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

admin

方法1：
1、创建一个临时表，选取需要的数据。
% Y+ I. S0 W6 M$ L9 t' q2、清空原表。
3、临时表数据导入到原表。
: C) {7 x: \# h3 b2 A4、删除临时表。
mysql> select * from student;
+----+------+
9 E* ~: y1 _- r8 K& T, ]2 @| ID | NAME |
+----+------+
( ^, `1 ]/ ?# U& ~& d| 11 | aa |
" w0 l+ M, R4 L- k) J| 12 | aa |
1 q( y4 d& c' k& o- ~" l, P5 F| 13 | bb |
# f! B' e; m# r& l* D' {| 14 | bb |
' a) O4 V9 h9 M2 ?| 15 | bb |
$ N6 C8 C, F+ T- P: o/ L: }| 16 | cc |
' a! x7 K# C& |) L5 H+----+------+
6 rows in set

mysql> create temporary table temp as select min(id),name from student group by name;
. N! R4 }" D" J- X: y. s) kQuery OK, 3 rows affected
1 G# o, z- s% i0 }9 GRecords: 3 Duplicates: 0 Warnings: 0

mysql> truncate table student;
8 B6 p& l4 q) U, I2 \4 Z* q: I0 }Query 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 |
% b6 F2 h; @: w+ `/ F+----+------+
| 11 | aa |
% ]) T6 X/ {0 D& A| 13 | bb |
| 16 | cc |
+----+------+
3 rows in set

mysql> drop temporary table temp;
# g" u3 t3 V2 @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;
% x  e* _) h- ~$ @& u( XQuery OK, 3 rows affected
Records: 3 Duplicates: 0 Warnings: 0

mysql> delete from student where id not in (select minid from temp);
& Y$ }4 o! M3 \6 k; J" G9 pQuery OK, 3 rows affected

mysql> select * from student;
% G" _$ M+ h- }. J( x  a% D+----+------+
| ID | NAME |
, j1 k4 A0 |' c+----+------+
. E$ U4 k9 M$ T% ]6 e! n3 ?" f" x% I| 11 | aa |
8 s7 x6 T9 q9 D' D: d| 13 | bb |
, y. ~9 t1 V: a| 16 | cc |
+----+------+
! P5 K& T+ z) |( m1 g7 C+ O3 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
/ R8 I/ `4 F& X5 i1 q) k原因是：更新数据时使用了查询，而查询的数据又做了更新的条件，mysql不支持这种方式。
& E! |9 j6 q0 _! C怎么规避这个问题？
再加一层封装，如下：
  ^" H% X& c3 W6 Imysql> delete from student where id not in (select minid from (select min(id) as minid from student group by name) b);
/ n) H# f6 T7 F" @, BQuery OK, 3 rows affected

mysql> select * from student;
+----+------+
| ID | NAME |
7 [% o3 j: y$ I! b# P4 S: R+----+------+
| 11 | aa |
| 13 | bb |
& `1 G+ m* C0 W- }  e, n| 16 | cc |
. z" ]: Z9 t0 X  ]; e+----+------+
$ F% M5 z; F3 p+ R+ j3 A& O7 f3 rows in set

3 H0 [* M3 e! P  b9 R$ F方法三例如：delete from bugdata where id not in (select minid from (select min(id) as minid from bugdata group by qxbh) b);

8 \+ i  i0 U3 w9 D
. u  y9 O, Z7 F5 W& r9 S8 k' F8 C