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

admin

方法1：
1、创建一个临时表，选取需要的数据。
2、清空原表。
3、临时表数据导入到原表。
4、删除临时表。
& h# ]# [& U& Z2 U2 e1 J0 g+ e, l7 [mysql> select * from student;
0 L# X" L4 B2 ~8 U4 f# y$ z+----+------+
| ID | NAME |
9 ]! Q) N) S3 ]2 O/ d  J2 m+----+------+
| 11 | aa |
7 j$ x5 T+ A- _" h8 g9 I| 12 | aa |
4 i9 [7 s5 A# k$ K8 I5 b+ i| 13 | bb |
| 14 | bb |
| 15 | bb |
9 W3 l1 J% w6 g" l. B- Y0 j| 16 | cc |
+----+------+
3 s0 d! U6 U8 E# c" ]6 P6 rows in set

mysql> create temporary table temp as select min(id),name from student group by name;
Query OK, 3 rows affected
8 @! _+ ]  F  B7 V' C; b- }/ D$ xRecords: 3 Duplicates: 0 Warnings: 0

mysql> truncate table student;
( |, v* d! ?( p; pQuery OK, 0 rows affected

mysql> insert into student select * from temp;
- d' L0 V1 r  F4 yQuery OK, 3 rows affected
+ h9 r' Q5 S6 ]) N9 W* ~  @  T  f7 d6 iRecords: 3 Duplicates: 0 Warnings: 0

mysql> select * from student;
+----+------+
9 {5 ^0 Y" B1 {| ID | NAME |
- k+ p" {/ h7 w, t% d: w% U+----+------+
; I4 P7 r# n7 s, ~' r7 z4 J| 11 | aa |
* S* J, K+ d0 b  B/ O. \| 13 | bb |
| 16 | cc |
7 }* z. H: I" R+----+------+
7 X- l. h& ?- L0 y  X2 q3 rows in set

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

---

方法2：按name分组，把最小的id保存到临时表，删除id不在最小id集合的记录，如下：
9 I5 k! X! q6 y: F  h/ `8 O" @; Umysql> create temporary table temp as select min(id) as MINID from student group by name;
" S' c4 q5 e" k/ y2 A1 m+ MQuery OK, 3 rows affected
. N+ N2 u: m$ P, X( S; {3 jRecords: 3 Duplicates: 0 Warnings: 0

mysql> delete from student where id not in (select minid from temp);
) X" r/ s) I% V& J; p0 qQuery OK, 3 rows affected

mysql> select * from student;
+----+------+
| ID | NAME |
; m" T3 w4 R' D  U+----+------+
0 k5 p/ ?2 M. P| 11 | aa |
0 q2 I1 ?7 d# r- i| 13 | bb |
2 P3 A) I2 @& j1 ^: a| 16 | cc |
" l& e3 F/ z0 G; B7 ^/ v. t  q+----+------+
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
原因是：更新数据时使用了查询，而查询的数据又做了更新的条件，mysql不支持这种方式。
怎么规避这个问题？
再加一层封装，如下：
5 D0 j- F1 D- Y5 h* P1 c% Kmysql> 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;
* }. n( f. t0 V* q+----+------+
| ID | NAME |
+----+------+
| 11 | aa |
| 13 | bb |
4 F$ E  {1 L: I# H( G| 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);
9 c+ {0 X( ]  }% Q2 f8 Q