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

admin

方法1：
% m% o* {. L! m- g' {9 o1、创建一个临时表，选取需要的数据。
  G. w0 x/ R8 J9 X6 L) J2 z6 T* o0 L2、清空原表。
% f! F/ g- S, I# G1 M* E3、临时表数据导入到原表。
4、删除临时表。
mysql> select * from student;
+----+------+
| ID | NAME |
+----+------+
| 11 | aa |
| 12 | aa |
| 13 | bb |
| 14 | bb |
| 15 | bb |
: P2 o! z, X$ R5 u, U+ D| 16 | cc |
4 j" ]: R1 D  c7 O1 j+----+------+
+ ^9 C; E2 m, [* j& V' M, F6 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;
' e" G$ T3 h; H# O  N5 lQuery OK, 0 rows affected

mysql> insert into student select * from temp;
, Y% Y- u4 Y4 a7 S' \/ f, q2 lQuery OK, 3 rows affected
5 U! B7 {" e+ m  N& e; _- C. iRecords: 3 Duplicates: 0 Warnings: 0

mysql> select * from student;
+----+------+
, N) N4 l! B" U2 n| ID | NAME |
0 f0 {* t  P3 c: H# h! _& d+----+------+
| 11 | aa |
$ t5 ?1 x- O9 g2 Z8 C1 h| 13 | bb |
7 J" ?7 t2 @, b! r| 16 | cc |
+----+------+
3 rows in set

mysql> drop temporary table temp;
- `, ]7 i0 q% r5 w- N/ [Query OK, 0 rows affected
7 @$ j- [# g* W' L" r# \. ?这个方法，显然存在效率问题。

---

方法2：按name分组，把最小的id保存到临时表，删除id不在最小id集合的记录，如下：
6 O% s6 B' x- `6 Lmysql> create temporary table temp as select min(id) as MINID from student group by name;
Query OK, 3 rows affected
' N, Y/ \$ s" \& NRecords: 3 Duplicates: 0 Warnings: 0

mysql> delete from student where id not in (select minid from temp);
% o9 q/ j% d( Z* I; m+ r9 b6 z8 _Query OK, 3 rows affected

mysql> select * from student;
+----+------+
| ID | NAME |
+----+------+
2 A" |- ^7 k" a' I6 ?# Z! b" z| 11 | aa |
1 S( [8 O5 E1 D2 @/ ^! O1 ]# t| 13 | bb |
| 16 | cc |
7 E( d* \* J- P# i0 t+----+------+
$ X3 s3 y3 I. {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
4 {/ ?1 g4 @: J. e! ^( ]原因是：更新数据时使用了查询，而查询的数据又做了更新的条件，mysql不支持这种方式。
" R0 }# j& _9 [, ], h; v怎么规避这个问题？
再加一层封装，如下：
mysql> delete from student where id not in (select minid from (select min(id) as minid from student group by name) b);
! c8 Q. G9 c* b/ X# `7 Q& lQuery OK, 3 rows affected

mysql> select * from student;
+----+------+
2 `! Y9 }, X8 r' b| ID | NAME |
" Q/ Q, _+ g# e& T$ q% f  L+ k+----+------+
$ @% W( o" k- ?6 D; a! R$ r| 11 | aa |
| 13 | bb |
| 16 | cc |
+----+------+
7 e) A8 l( A+ i  u, B% j; J3 rows in set

4 A# l$ H1 k  `) w$ V4 E方法三例如：delete from bugdata where id not in (select minid from (select min(id) as minid from bugdata group by qxbh) b);