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

admin

方法1：
6 `7 R7 s, X' v; a% g$ w2 t4 o% j1、创建一个临时表，选取需要的数据。
2、清空原表。
3、临时表数据导入到原表。
6 l/ i* B1 V: M1 V4 w- F4、删除临时表。
mysql> select * from student;
+----+------+
* u1 B; X3 r+ T* E1 ^2 O| ID | NAME |
& G# v) R: D" R4 ]3 h; H2 Q! @3 }8 o- x+----+------+
| 11 | aa |
| 12 | aa |
| 13 | bb |
2 \0 M' \+ Z( G( x* T" i| 14 | bb |
7 c% Z8 L& V  c3 x| 15 | bb |
7 y6 ?  O" P# L3 H) M| 16 | cc |
+----+------+
8 H, c9 v/ `) x* B0 r  _. E6 rows in set

mysql> create temporary table temp as select min(id),name from student group by name;
: H: Y) U7 s$ Q, ?# SQuery OK, 3 rows affected
Records: 3 Duplicates: 0 Warnings: 0

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

mysql> insert into student select * from temp;
' j# c+ G3 ^( f4 V6 }, qQuery OK, 3 rows affected
Records: 3 Duplicates: 0 Warnings: 0

mysql> select * from student;
3 w' N; q( h7 J1 p+----+------+
* n! v9 P2 c5 `| ID | NAME |
$ v0 G, ]$ K/ [+----+------+
| 11 | aa |
" P* t0 G$ o4 r" N2 X1 i| 13 | bb |
| 16 | cc |
+----+------+
( l! [# K# u: H% _" x2 {3 rows in set

mysql> drop temporary table temp;
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;
% Y6 x5 k6 u& c9 A* vQuery OK, 3 rows affected
# s! J9 F& ?) D) [* e/ XRecords: 3 Duplicates: 0 Warnings: 0

mysql> delete from student where id not in (select minid from temp);
1 Z- e8 C) G1 ?" oQuery OK, 3 rows affected

mysql> select * from student;
& Y* |4 P  @) f6 J( B' ^" n+----+------+
| ID | NAME |
, I3 |# k" w2 n& C( r9 s8 S3 X+ R7 Y+----+------+
% ?0 g, m% W5 [1 V| 11 | aa |
| 13 | bb |
4 t1 S- f% k  h| 16 | cc |
+----+------+
3 rows in set

---

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

mysql> delete from student where id not in (select min(id) from student group by name);
; Q2 J# C% D5 Q) r( S+ K/ `( \执行报错：1093 - You can't specify target table 'student' for update in FROM clause
2 x) b; U6 I$ ~' j原因是：更新数据时使用了查询，而查询的数据又做了更新的条件，mysql不支持这种方式。
7 Y$ F6 d; \( A6 [8 t怎么规避这个问题？
再加一层封装，如下：
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 |
) c( b2 I. i8 S  v# R- G+----+------+
, h6 z/ @0 E2 H| 11 | aa |
' p3 B  u8 d: P8 v0 N1 F! y| 13 | bb |
| 16 | cc |
+----+------+
' N, v2 u% {# R/ i' h0 J3 rows in set

方法三例如：delete from bugdata where id not in (select minid from (select min(id) as minid from bugdata group by qxbh) b);
! a4 G2 g7 E" x' {8 ^- l0 ]3 ^