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

admin

方法1：
; L& f) z( M1 d. d7 p' ^0 v: s2 p1、创建一个临时表，选取需要的数据。
2、清空原表。
3、临时表数据导入到原表。
4、删除临时表。
mysql> select * from student;
# K/ r! c) ~* r1 O3 Y& P! \* t+----+------+
| ID | NAME |
+----+------+
| 11 | aa |
& i5 d; t& r( O- n7 R6 S| 12 | aa |
5 t2 `$ _2 |, l! C' X| 13 | bb |
4 k& C  Q% @$ B, k0 P! u| 14 | bb |
| 15 | bb |
: y2 U* }5 V  `, B& R| 16 | cc |
+----+------+
6 rows in set

mysql> create temporary table temp as select min(id),name from student group by name;
/ c' w; D) |" E; n6 l1 Y% f4 aQuery OK, 3 rows affected
3 U$ d0 E9 N  j* V4 K- y5 K, tRecords: 3 Duplicates: 0 Warnings: 0

mysql> truncate table student;
% W3 K1 v0 J4 N6 T8 \2 TQuery OK, 0 rows affected

mysql> insert into student select * from temp;
Query OK, 3 rows affected
Records: 3 Duplicates: 0 Warnings: 0

mysql> select * from student;
+----+------+
| ID | NAME |
+ u8 I' d" V& r( N4 w! x) b+----+------+
| 11 | aa |
| 13 | bb |
/ l) k) [' Y8 i* X" `3 @! }| 16 | cc |
+----+------+
8 |: c1 T, f* f  v; k3 rows in set

mysql> drop temporary table temp;
Query OK, 0 rows affected
5 ]6 M+ n+ w7 l# |5 ^8 Q" S/ G: b9 L这个方法，显然存在效率问题。

---

方法2：按name分组，把最小的id保存到临时表，删除id不在最小id集合的记录，如下：
6 O0 X2 ~9 {$ C# o- p" lmysql> create temporary table temp as select min(id) as MINID from student group by name;
( u& D# Q1 x; X* q4 ?Query OK, 3 rows affected
- [( N' |- i  ]0 e7 e  WRecords: 3 Duplicates: 0 Warnings: 0

mysql> delete from student where id not in (select minid from temp);
: O" t7 T5 I. D$ R$ qQuery OK, 3 rows affected

mysql> select * from student;
- C6 M' N2 V5 F2 C$ V9 d0 N+----+------+
| ID | NAME |
6 p8 N( U9 x: C- ], i; f+----+------+
| 11 | aa |
| 13 | bb |
, u; X; k$ j! k' \+ F4 e| 16 | cc |
( m) h: [. ?7 A* }$ W, w+----+------+
5 Z, e5 y* r0 e* W/ x" a3 rows in set

---

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

mysql> delete from student where id not in (select min(id) from student group by name);
" ?' E# j5 v0 J* b( F执行报错：1093 - You can't specify target table 'student' for update in FROM clause
原因是：更新数据时使用了查询，而查询的数据又做了更新的条件，mysql不支持这种方式。
4 G* v) Q& ?, w; }怎么规避这个问题？
0 `; {. l5 G# z9 P; a, ?4 v再加一层封装，如下：
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;
1 F) C$ d( A2 X& {2 r6 Y+----+------+
8 O7 p' j, h' G' h9 {) V2 d. o; b- {| ID | NAME |
+----+------+
1 D1 y8 ]0 c& B+ m/ r9 D0 m| 11 | aa |
| 13 | bb |
| 16 | cc |
+----+------+
3 rows in set

: \7 \5 d% n+ F- B0 n+ z7 M  ]2 U方法三例如：delete from bugdata where id not in (select minid from (select min(id) as minid from bugdata group by qxbh) b);
, S8 M% V: @( i6 l
) `! K" j( z! l3 A# ^5 K/ z- M% S