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

admin

方法1：
1、创建一个临时表，选取需要的数据。
5 O+ A3 Q# u8 d0 E2、清空原表。
7 y2 X& L# c* j. P) g8 K, B' Q3、临时表数据导入到原表。
4、删除临时表。
* b% q: U3 x7 d/ }, P! s5 amysql> select * from student;
+----+------+
+ r5 e0 F' q$ h$ Q| ID | NAME |
) ~1 f' X+ M5 ?. w2 }+----+------+
| 11 | aa |
! u2 i. E0 R. c, K- U| 12 | aa |
| 13 | bb |
| 14 | bb |
| 15 | bb |
. x1 F+ I' r* f8 G/ A; B4 O/ \| 16 | cc |
! G1 V- V- G! \! |+----+------+
6 rows in set

mysql> create temporary table temp as select min(id),name from student group by name;
Query 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;
Query OK, 3 rows affected
- Q+ t2 w' o. O- tRecords: 3 Duplicates: 0 Warnings: 0

mysql> select * from student;
+----+------+
' Y- t. R/ Q8 m/ }$ [- U* ]- K( ]| ID | NAME |
9 I: Z. {: p1 W3 F+----+------+
| 11 | aa |
| 13 | bb |
| 16 | cc |
; Q/ g% r$ N. f$ m2 e+----+------+
3 rows in set

mysql> drop temporary table temp;
Query OK, 0 rows affected
& H5 E9 |, l/ U4 k9 S这个方法，显然存在效率问题。

---

方法2：按name分组，把最小的id保存到临时表，删除id不在最小id集合的记录，如下：
mysql> create temporary table temp as select min(id) as MINID from student group by name;
Query OK, 3 rows affected
/ d7 D- \% y  K( }- E$ i7 FRecords: 3 Duplicates: 0 Warnings: 0

mysql> delete from student where id not in (select minid from temp);
8 O# A# s) C" I& z$ s3 G" A# SQuery OK, 3 rows affected

mysql> select * from student;
+----+------+
| ID | NAME |
2 s; t$ N" N; _; F# R& ?5 \+----+------+
| 11 | aa |
/ N& k$ j8 ~( C| 13 | bb |
| 16 | cc |
+----+------+
3 rows in set

---

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

mysql> delete from student where id not in (select min(id) from student group by name);
4 R/ d8 A) J! Z) K6 u执行报错：1093 - You can't specify target table 'student' for update in FROM clause
原因是：更新数据时使用了查询，而查询的数据又做了更新的条件，mysql不支持这种方式。
怎么规避这个问题？
再加一层封装，如下：
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;
2 [" [! n+ I) V/ Y$ C! n9 J) Y+----+------+
| ID | NAME |
+----+------+
4 B: v& Q, h: \! G& a- p% ^5 W# _- a| 11 | aa |
" C" T$ P2 L" J2 |/ j8 _. _| 13 | bb |
( l% G% O! e* l) J8 D) u( B* G| 16 | cc |
) ^9 }' @! y8 u0 h, j* |4 G+----+------+
/ u/ |2 `$ I1 V8 O3 rows in set

方法三例如：delete from bugdata where id not in (select minid from (select min(id) as minid from bugdata group by qxbh) b);