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

admin

方法1：
1、创建一个临时表，选取需要的数据。
: S8 E$ J% a2 {! u  W2、清空原表。
" ?. i7 R& U. B" l$ e3、临时表数据导入到原表。
4、删除临时表。
3 k+ |, G5 g6 |( V5 {& O$ amysql> select * from student;
/ j% U( V! c9 T8 m1 K# c+----+------+
| ID | NAME |
+----+------+
| 11 | aa |
7 c3 }2 Z  C! \7 h& F& ~+ q( ?| 12 | aa |
| 13 | bb |
7 R0 P7 V; |) s8 t* J| 14 | bb |
$ `9 k3 P8 L4 A1 W) H| 15 | bb |
0 u" V! m/ r& |- E| 16 | cc |
/ n' C5 \; i4 G+----+------+
6 rows in set

mysql> create temporary table temp as select min(id),name from student group by name;
1 z& `2 s& |8 o, V1 eQuery OK, 3 rows affected
4 q& [+ f  U6 y) }# }# HRecords: 3 Duplicates: 0 Warnings: 0

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

mysql> insert into student select * from temp;
Query OK, 3 rows affected
: m) ^5 ?5 D+ K7 B% ?! sRecords: 3 Duplicates: 0 Warnings: 0

mysql> select * from student;
/ ~/ m. j3 _; }+ j) C9 {: n" |+----+------+
| ID | NAME |
5 T9 W& m6 Z2 `8 _' |5 g6 {+----+------+
| 11 | aa |
| 13 | bb |
/ z' {5 j5 I! @+ Y) W| 16 | cc |
4 D1 x% J. B4 o# h+----+------+
3 rows in set

mysql> drop temporary table temp;
Query OK, 0 rows affected
这个方法，显然存在效率问题。

---

方法2：按name分组，把最小的id保存到临时表，删除id不在最小id集合的记录，如下：
mysql> 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;
+----+------+
& B( X; [1 Q' C& E2 @/ l9 B2 h| ID | NAME |
" d# w( F2 N' s7 b. p) v) X+----+------+
$ {6 T) s  t2 c1 N1 T; h| 11 | aa |
| 13 | bb |
% e" y7 a/ b6 G% v  Z| 16 | cc |
- d( i$ {% ?7 V7 H% Y' b2 G9 \+----+------+
3 rows in set

---

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

mysql> delete from student where id not in (select min(id) from student group by name);
- j7 s' R7 ?% m! K9 H& H. D执行报错：1093 - You can't specify target table 'student' for update in FROM clause
原因是：更新数据时使用了查询，而查询的数据又做了更新的条件，mysql不支持这种方式。
& |( F' s/ N& a怎么规避这个问题？
3 {* T8 D0 z8 I3 r4 z' ~再加一层封装，如下：
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 |
+----+------+
! s3 {$ b  Q: P! ~4 H: R8 ~| 11 | aa |
* `! v# ~2 R2 ?6 I+ q& R- F| 13 | bb |
| 16 | cc |
0 d9 R  M+ G' @6 |. t+----+------+
! W8 y2 v6 ?$ y1 @! G# j6 ]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);
1 P' n. |0 u" {+ g% g# v
( b: J3 H: a& Y