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

admin

方法1：
' R. s' a4 V( ]1、创建一个临时表，选取需要的数据。
& s' h% h) ~& b2 @3 d2、清空原表。
3、临时表数据导入到原表。
" ~1 G$ V  o% V) @* {4、删除临时表。
- x; `; h/ N6 G: Y) d$ Jmysql> select * from student;
+----+------+
9 W4 v! Y( c' t: [5 ~8 B8 R| ID | NAME |
) u! g+ j  l: L2 k6 `  D' c+----+------+
| 11 | aa |
5 M1 `9 }2 c, t& _' S| 12 | aa |
| 13 | bb |
| 14 | bb |
! k9 b1 w+ Y, f9 W  u2 [, {| 15 | bb |
* b3 F# N9 _, M$ Q/ C| 16 | cc |
* S4 d& z* _) m3 O- }+ m+----+------+
: e6 H4 Q  [, v! F6 rows in set

mysql> create temporary table temp as select min(id),name from student group by name;
Query OK, 3 rows affected
  d" ]# q% o) e  K# @Records: 3 Duplicates: 0 Warnings: 0

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

mysql> insert into student select * from temp;
1 T; Y2 ]6 Z6 z3 Y: ?$ R+ ~& wQuery OK, 3 rows affected
Records: 3 Duplicates: 0 Warnings: 0

mysql> select * from student;
7 h/ P$ \7 W: i* s+----+------+
+ W% D# s2 Q; m! ?6 k5 `6 c2 S/ Y| ID | NAME |
+----+------+
| 11 | aa |
| 13 | bb |
7 Q+ o8 B! ~6 X$ ~| 16 | cc |
9 N/ j* O8 I3 u+ j1 p+----+------+
+ `/ W' A$ |7 s& r3 rows in set

mysql> drop temporary table temp;
Query OK, 0 rows affected
4 M$ \5 }$ w5 w2 U这个方法，显然存在效率问题。

---

方法2：按name分组，把最小的id保存到临时表，删除id不在最小id集合的记录，如下：
mysql> create temporary table temp as select min(id) as MINID from student group by name;
# o" ~) c+ l4 X2 t1 w4 B; BQuery 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;
+----+------+
0 d+ W+ K# a- d5 g% T, M| ID | NAME |
) s9 z' `$ s% ^$ e' N+----+------+
( E- D0 b3 `% t/ L7 E| 11 | aa |
* r/ X& Q7 P/ s| 13 | bb |
  r$ A9 y9 X, m: R6 d3 y& E| 16 | cc |
+----+------+
+ d9 }+ D. C% l/ t3 rows in set

---

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

mysql> delete from student where id not in (select min(id) from student group by name);
: P) w% P4 J. O7 z) ?执行报错：1093 - You can't specify target table 'student' for update in FROM clause
5 R% F2 g) B& H0 |2 `/ j原因是：更新数据时使用了查询，而查询的数据又做了更新的条件，mysql不支持这种方式。
4 f' n( d; d9 Z" Y  O1 @& z怎么规避这个问题？
, {3 r, U& l* }4 l% m) w: Z再加一层封装，如下：
mysql> delete from student where id not in (select minid from (select min(id) as minid from student group by name) b);
; S9 S0 p6 O4 `" P' N; L- P/ vQuery OK, 3 rows affected

mysql> select * from student;
+----+------+
| ID | NAME |
+----+------+
) X# J- V0 c+ B& Z' ~| 11 | aa |
| 13 | bb |
0 v, z# T" t( M+ c2 p3 v7 y# I| 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);