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

admin

方法1：
; u! U" b' W8 Y" `) N. |1、创建一个临时表，选取需要的数据。
/ x5 `; _" n3 a4 A. P& j2、清空原表。
3 G( Q/ a1 |+ }9 u- L: Y3、临时表数据导入到原表。
+ X7 B( v/ j7 f4、删除临时表。
mysql> select * from student;
+ T" _$ d5 b, y9 X- F' H- e7 q+----+------+
| ID | NAME |
3 `% ~. [- ?: y* l9 g+----+------+
| 11 | aa |
: ?+ |: u6 ^& q/ `; s| 12 | aa |
3 R# R. b) `) @. ~* f! j% _; @| 13 | bb |
5 N0 o/ J, O0 M4 ]4 a! @+ y6 _( Z| 14 | bb |
3 J! X- |9 G( d+ c| 15 | bb |
  i' g0 |# a/ Z9 a* E| 16 | cc |
+----+------+
5 `0 Z& T( v! L9 T7 L  |6 rows in set

mysql> create temporary table temp as select min(id),name from student group by name;
2 {  I; U; o, A. N3 ]0 [Query OK, 3 rows affected
* X5 @7 _0 D4 ?8 oRecords: 3 Duplicates: 0 Warnings: 0

mysql> truncate table student;
% N  u! E; _$ N2 GQuery OK, 0 rows affected

mysql> insert into student select * from temp;
Query OK, 3 rows affected
0 A, M1 d4 b1 n5 `( S8 FRecords: 3 Duplicates: 0 Warnings: 0

mysql> select * from student;
+----+------+
! `$ b* L: x5 n4 ^; M" d| ID | NAME |
2 B0 w, G0 d; f6 ?+----+------+
+ {. K# c: t2 z7 t; B, N2 Y| 11 | aa |
! N6 @1 {( ]& ]' k' s| 13 | bb |
! ^  |% b$ c0 w| 16 | cc |
1 j3 n. D' v; m& |# Y+----+------+
2 `! G- V0 Y1 u3 k  X- B- P3 rows in set

mysql> drop temporary table temp;
6 x  `5 N# ?# x- a! V( AQuery OK, 0 rows affected
% W# N. B; R0 k2 z, B这个方法，显然存在效率问题。

---

方法2：按name分组，把最小的id保存到临时表，删除id不在最小id集合的记录，如下：
: H& p& S& [0 Q' Bmysql> create temporary table temp as select min(id) as MINID from student group by name;
- h6 H" g- c5 ?0 s: p5 [8 r2 ?. K1 f2 PQuery OK, 3 rows affected
$ N+ W$ T- [8 Y# Q  q# FRecords: 3 Duplicates: 0 Warnings: 0

mysql> delete from student where id not in (select minid from temp);
* U) a6 e$ Z$ s3 W, _! wQuery OK, 3 rows affected

mysql> select * from student;
+----+------+
# b$ ~5 b1 n5 c. P2 d/ P( J) D| ID | NAME |
+----+------+
# k9 R% M  C) n. n/ u' e# X( t| 11 | aa |
| 13 | bb |
| 16 | cc |
  J' }5 k9 h& ~4 D9 K+----+------+
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
1 D: Y7 R' ^1 V) ^3 Z原因是：更新数据时使用了查询，而查询的数据又做了更新的条件，mysql不支持这种方式。
怎么规避这个问题？
再加一层封装，如下：
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 |
( ?( k" ^! L3 ?3 _9 k9 H& ~, b  z! @+----+------+
$ a! d, {* i4 l! T| 11 | aa |
| 13 | bb |
| 16 | cc |
+----+------+
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);
3 V. b+ L: [8 x7 J0 e
1 ~6 t) r' J6 o$ g9 C. y