mysql删除重复记录，保存Id最小的一条

admin

方法1：
1、创建一个临时表，选取需要的数据。
2、清空原表。
3、临时表数据导入到原表。
4、删除临时表。
mysql> select * from student;
% A& |; X! \' g5 e5 U- c+----+------+
3 b7 q: F" O$ q9 J  A/ z* o| ID | NAME |
+----+------+
. X! J& w: m) X. M( F| 11 | aa |
| 12 | aa |
+ w1 N, |- o# K" r( w( y% e| 13 | bb |
; [8 |( K/ t2 E9 r| 14 | bb |
+ B1 ^4 t7 }) v| 15 | bb |
| 16 | cc |
+----+------+
6 rows in set

mysql> create temporary table temp as select min(id),name from student group by name;
Query OK, 3 rows affected
( A  k) d+ Z: t* cRecords: 3 Duplicates: 0 Warnings: 0

mysql> truncate table student;
Query OK, 0 rows affected

mysql> insert into student select * from temp;
& V$ `% t; T  @Query OK, 3 rows affected
2 Y4 c, n1 ^/ c/ m$ a- c/ JRecords: 3 Duplicates: 0 Warnings: 0

mysql> select * from student;
$ K4 d; x& W* h3 ?' ^9 E+----+------+
  e: S8 Y; W/ @% Y! S| ID | NAME |
# a3 M# J; P) X: W! u+----+------+
3 \7 B/ f- J; L  J/ {% e" `$ ]3 `| 11 | aa |
/ P# X9 G: k2 R/ t| 13 | bb |
| 16 | cc |
+----+------+
. [4 q, y' D! w7 w# n/ |9 I3 rows in set

mysql> drop temporary table temp;
) V) q# v- {5 d6 M6 JQuery OK, 0 rows affected
这个方法，显然存在效率问题。

---

方法2：按name分组，把最小的id保存到临时表，删除id不在最小id集合的记录，如下：
( b& D9 d8 q) q# v; l4 Hmysql> create temporary table temp as select min(id) as MINID from student group by name;
Query OK, 3 rows affected
5 P$ x! }" g5 h( l8 FRecords: 3 Duplicates: 0 Warnings: 0

mysql> delete from student where id not in (select minid from temp);
Query OK, 3 rows affected

mysql> select * from student;
+----+------+
# G, }7 V: U0 s0 w6 `| ID | NAME |
1 ]* E5 ?8 S( }- X% }+ t+----+------+
! E/ @) c% w9 T7 R& z. v| 11 | aa |
| 13 | bb |
| 16 | cc |
+----+------+
3 rows in set

---

方法3：直接在原表上操作，容易想到的sql语句如下：

mysql> delete from student where id not in (select min(id) from student group by name);
执行报错：1093 - You can't specify target table 'student' for update in FROM clause
# [; [: P  ~9 T/ \/ z. o( t9 P& |原因是：更新数据时使用了查询，而查询的数据又做了更新的条件，mysql不支持这种方式。
8 @. ]& f2 g9 w怎么规避这个问题？
. D7 o) I# [8 J# |% _2 r再加一层封装，如下：
mysql> delete from student where id not in (select minid from (select min(id) as minid from student group by name) b);
Query OK, 3 rows affected

mysql> select * from student;
+----+------+
| ID | NAME |
+----+------+
| 11 | aa |
  i6 d& [& ]* x4 o0 W3 ~5 G# K5 m& @| 13 | bb |
/ _9 l, L; R# h2 s/ j8 q| 16 | cc |
+----+------+
3 rows in set

2 j3 i9 X4 D7 j( {( ]) n) r4 a方法三例如：delete from bugdata where id not in (select minid from (select min(id) as minid from bugdata group by qxbh) b);

( H5 ]" ]) G: R  e