/*
MySQL 消除重复行的一些方法
*/
create
table
test_1(id
int
,value
int
);
insert
test_1
select
1,2
union
all
select
1,2
union
all
select
2,3;
create
table
tmp
like
test_1;
insert
tmp
select
distinct
*
from
test_1;
drop
table
test_1;
rename
table
tmp
to
test_1;
mysql>
select
*
from
test_1;
+
| id | value |
+
| 1 | 2 |
| 2 | 3 |
+
create
table
test_1(id
int
,value
int
) engine=MyISAM;
insert
test_1
select
1,2
union
all
select
1,2
union
all
select
2,3;
alter
table
test_1
add
id2
int
not
null
auto_increment,
add
primary
key
(id,value,id2);
select
*
from
test_1;
+
| id | value | id2 |
+
| 1 | 2 | 1 |
| 1 | 2 | 2 |
| 2 | 3 | 1 |
+
delete
from
test_1
where
id2<>1;
alter
table
test_1
drop
id2;
select
*
from
test_1;
+
| id | value |
+
| 1 | 2 |
| 2 | 3 |
+
create
table
test_2(id
int
,value
int
);
insert
test_2
select
1,2
union
all
select
1,3
union
all
select
2,3;
Alter
IGNORE
table
test_2
add
primary
key
(id);
select
*
from
test_2;
+
| id | value |
+
| 1 | 2 |
| 2 | 3 |
+
我们可以看到 1 3 这条记录消失了
我们这里也可以使用
Unique
约束 因为有可能列中有
NULL
值,但是这里
NULL
就可以多个了..
create
table
test_2(id
int
,value
int
);
insert
test_2
select
1,2
union
all
select
1,3
union
all
select
2,3;
delete
A
from
test_2 a
join
(
select
MAX
(value)
as
v ,ID
from
test_2
group
by
id) b
on
a.id=b.id
and
a.value<>b.v;
select
*
from
test_2;
+
| id | value |
+
| 1 | 3 |
| 2 | 3 |
+
create
table
test_2(id
int
,value
int
);
insert
test_2
select
1,2
union
all
select
1,3
union
all
select
2,3;
delete
a
from
test_2 a
where
exists(
select
*
from
test_2
where
a.id=id
and
a.value<value);
/*ERROR 1093 (HY000): You can
't specify target table '
a'
for
update
in
FROM
clause*/
目前,您不能从一个表中删除,同时又在子查询中从同一个表中选择。
create
table
test_3(id
int
,value
int
);
insert
test_3
select
1,2
union
all
select
1,3
union
all
select
1,4
union
all
select
2,3;
delete
from
test_3
where
id=1
order
by
value
desc
limit 2;
select
*
from
test_3;
+
| id | value |
+
| 1 | 2 |
| 2 | 3 |
+
如果你只想删除任意的记录 保留一条 就可以去掉
order
by