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

admin

方法1：
/ o# v, g) e% [+ |0 s1 G1、创建一个临时表，选取需要的数据。
/ @" {5 W/ E6 c5 g& R2、清空原表。
5 Q) H  y. a: d1 ]3、临时表数据导入到原表。
4、删除临时表。
mysql> select * from student;
8 z' p' ^( f9 C1 ]9 S+----+------+
+ f4 P. Z9 L* i2 j0 ?* S# ]3 Y4 C| ID | NAME |
+----+------+
  q( q2 K8 v5 h7 u, Y& v! p  F) ^! V| 11 | aa |
| 12 | aa |
( b2 t* \) _8 W; `5 ~; @9 B) X& b# G| 13 | bb |
| 14 | bb |
| 15 | bb |
| 16 | cc |
! }3 w8 A. q, F3 ~+----+------+
" x' ]- W: I! C$ L& x6 rows in set

mysql> create temporary table temp as select min(id),name from student group by name;
8 p9 V6 c) Z  q0 F( D2 ?Query OK, 3 rows affected
, M2 e: h* F  p% G4 x  _Records: 3 Duplicates: 0 Warnings: 0

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

mysql> insert into student select * from temp;
* [, \2 a6 [9 H& ]; Y; m2 }Query OK, 3 rows affected
9 z% O6 p( S! MRecords: 3 Duplicates: 0 Warnings: 0

mysql> select * from student;
* d8 G' m: n  p0 A+----+------+
3 \: T# z; d! T* F7 F9 c1 l| ID | NAME |
+----+------+
; y' i4 J% V. D: H' `, V# A% ?| 11 | aa |
# z; }( L* P0 X: R  I5 u+ Y| 13 | bb |
: e6 o4 R; V% S| 16 | cc |
+----+------+
3 rows in set

mysql> drop temporary table temp;
1 U2 C8 v0 H% q& h1 ?  z* qQuery OK, 0 rows affected
这个方法，显然存在效率问题。

---

方法2：按name分组，把最小的id保存到临时表，删除id不在最小id集合的记录，如下：
  h% e8 i, h. F( |mysql> create temporary table temp as select min(id) as MINID from student group by name;
0 N. @2 e, u" C) o. n: HQuery 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;
$ U6 J& F0 A& Y  o8 n" ?+ d+----+------+
4 z, ^4 N# K, ~8 Q! a8 f/ z2 J- c6 @| ID | NAME |
+ J* d3 p( ^6 [# `+----+------+
4 f1 P+ V. D( I" b, G* G5 i( ?| 11 | aa |
| 13 | bb |
8 \% g- R% d, ~. C( F( b2 Z5 ]| 16 | cc |
+----+------+
3 rows in set

---

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

mysql> delete from student where id not in (select min(id) from student group by name);
5 \- z' L: w$ [$ V- |: `, v( Y执行报错：1093 - You can't specify target table 'student' for update in FROM clause
原因是：更新数据时使用了查询，而查询的数据又做了更新的条件，mysql不支持这种方式。
6 n# o7 b: C  K0 c' 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;
+----+------+
3 I) L# t: S! T* \| ID | NAME |
( W; @% y2 d! y/ E+----+------+
| 11 | aa |
& y2 w8 v! |) `! e# B: F% B| 13 | bb |
8 e% Z# Z7 l6 z9 d5 N8 V' U| 16 | cc |
+----+------+
( K# W& y# W3 F3 rows in set

方法三例如：delete from bugdata where id not in (select minid from (select min(id) as minid from bugdata group by qxbh) b);
! b: G8 j6 {, u9 U) k+ Q7 f; {5 V. B0 I
6 H. J- c7 V/ p  p; n0 Z
, m1 g' Y* t* p