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

admin

方法1：
1、创建一个临时表，选取需要的数据。
1 L0 Z3 Y9 R( O" y0 s3 R2、清空原表。
3、临时表数据导入到原表。
/ a. M" w; @% g4、删除临时表。
- q$ \/ `3 N1 S0 gmysql> select * from student;
+----+------+
& u2 _  W2 i% M4 M4 ]/ s4 R" `| ID | NAME |
6 A7 H# L, \* f4 v- N. f+ m# _+----+------+
| 11 | aa |
6 Z0 E! S# l; _/ {+ B: Z| 12 | aa |
* A. S9 q) ]! E$ l: f; t| 13 | bb |
| 14 | bb |
# E( }& c6 D) {. f| 15 | bb |
; `6 Z2 s/ g5 B8 M| 16 | cc |
+----+------+
6 rows in set

mysql> create temporary table temp as select min(id),name from student group by name;
: n" e  R' V* uQuery OK, 3 rows affected
3 D3 H  K4 y7 u& k5 FRecords: 3 Duplicates: 0 Warnings: 0

mysql> truncate table student;
5 f  b7 _! L% B* T! t# a; xQuery 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;
% |4 Y/ D% ]# M; K- Y+----+------+
+ d; A8 g  n" g" ?6 L. Z| ID | NAME |
+----+------+
1 j% p% K: x% J| 11 | aa |
+ S  C. M  Y, ~$ X| 13 | bb |
2 t; j% h4 W+ u& ~| 16 | cc |
: ^2 U$ F& J' L% [1 A$ K+----+------+
3 rows in set

mysql> drop temporary table temp;
Query OK, 0 rows affected
( r/ @$ }9 y$ H7 x8 G1 U5 E这个方法，显然存在效率问题。

---

方法2：按name分组，把最小的id保存到临时表，删除id不在最小id集合的记录，如下：
. n/ _! I+ ^3 lmysql> create temporary table temp as select min(id) as MINID from student group by name;
0 B; s) U3 f9 a: }* MQuery OK, 3 rows affected
Records: 3 Duplicates: 0 Warnings: 0

mysql> delete from student where id not in (select minid from temp);
! j; @1 ?+ i1 M! O9 b* eQuery OK, 3 rows affected

mysql> select * from student;
% b& K! r" v6 _) j9 H" d, }' P+----+------+
| ID | NAME |
6 {8 ~" t9 [2 _0 j* l+----+------+
) N# o) |, w! j" k, {. `7 ?$ ?$ i| 11 | aa |
| 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);
执行报错：1093 - You can't specify target table 'student' for update in FROM clause
原因是：更新数据时使用了查询，而查询的数据又做了更新的条件，mysql不支持这种方式。
% Z2 R/ s1 v2 {( n( s怎么规避这个问题？
再加一层封装，如下：
8 o; b% W0 O* {/ t. j8 K% e% rmysql> delete from student where id not in (select minid from (select min(id) as minid from student group by name) b);
+ T' j6 ?' T9 g3 cQuery OK, 3 rows affected

mysql> select * from student;
: q& ?# j( m$ \& |* ]: X9 ~+----+------+
| ID | NAME |
+----+------+
+ v& k% h: Z$ b7 p2 I| 11 | aa |
| 13 | bb |
( h9 @; z. t3 c5 n4 ?9 C| 16 | cc |
* O3 g& S  k& K) t, {# i1 [% @8 k+ X: u+----+------+
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);
5 {+ R3 ?- S! q$ F8 N
+ H& L' X2 [+ S, ^$ {: [7 c8 A
8 u, C( m. _, K: Q& ?5 k4 ?7 g