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

admin

方法1：
1、创建一个临时表，选取需要的数据。
  O$ `8 w: p; W/ v$ A* F! l2、清空原表。
3、临时表数据导入到原表。
8 _) }9 g2 R% @( B7 a4、删除临时表。
" j- B- d/ B/ lmysql> select * from student;
( Z  U+ e6 j- F. B# C9 o% P$ n# ]+----+------+
- m7 G. R" u0 U/ b, Y# g/ n. X| ID | NAME |
+----+------+
  k2 L0 W! ~1 R! `$ v; T/ g| 11 | aa |
8 q  c0 _: M/ p+ c$ R: S| 12 | aa |
| 13 | bb |
8 a; _( r& j) l# f1 J2 r8 w| 14 | bb |
| 15 | bb |
3 o' Q. P" c& |' f5 }' v| 16 | cc |
1 g! g* K4 [! p3 |% ^9 p) ~2 v+----+------+
; I5 R% _! |' S7 y6 rows in set

mysql> create temporary table temp as select min(id),name from student group by name;
Query OK, 3 rows affected
) E" F1 M0 }+ GRecords: 3 Duplicates: 0 Warnings: 0

mysql> truncate table student;
) ^  H5 Q0 e3 T( AQuery OK, 0 rows affected

mysql> insert into student select * from temp;
. ?% v' j% p: C; ~) aQuery OK, 3 rows affected
/ u' x1 b  ?* r7 J' e1 ]! GRecords: 3 Duplicates: 0 Warnings: 0

mysql> select * from student;
2 K* {4 S7 U2 G+ K  R2 y# S+----+------+
| ID | NAME |
+----+------+
| 11 | aa |
| 13 | bb |
| 16 | cc |
+----+------+
7 E' v2 f+ P" M" u- u& r3 rows in set

mysql> drop temporary table temp;
! u  m2 g# w3 C. R" ]Query OK, 0 rows affected
: y7 c/ `4 c" }( n这个方法，显然存在效率问题。

---

方法2：按name分组，把最小的id保存到临时表，删除id不在最小id集合的记录，如下：
7 |- t" g5 K% e/ n* r8 w, Nmysql> create temporary table temp as select min(id) as MINID from student group by name;
Query OK, 3 rows affected
Records: 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;
+----+------+
9 Y) m0 J8 D. r1 {| ID | NAME |
; x! d& m& o3 s( k: Z  W; e/ @$ ?+----+------+
| 11 | aa |
| 13 | bb |
$ F7 T9 n0 q+ v) k9 w| 16 | cc |
+----+------+
3 rows in set

---

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

mysql> delete from student where id not in (select min(id) from student group by name);
1 ^8 J/ J( e4 A' p- ^执行报错：1093 - You can't specify target table 'student' for update in FROM clause
原因是：更新数据时使用了查询，而查询的数据又做了更新的条件，mysql不支持这种方式。
怎么规避这个问题？
再加一层封装，如下：
, w' d) @+ `9 F# M. o) dmysql> 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;
3 w" E. z# ]) [& R: p3 b8 r+----+------+
| ID | NAME |
+----+------+
| 11 | aa |
| 13 | bb |
| 16 | cc |
3 r7 ?7 b/ k2 h; [$ k- F* L8 ]- E+----+------+
3 rows in set

方法三例如：delete from bugdata where id not in (select minid from (select min(id) as minid from bugdata group by qxbh) b);
/ f6 y$ |2 v0 Z
6 a3 ?$ K( ?4 K$ l