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

admin

方法1：
5 E  T' a7 L! o8 S6 r1 }1、创建一个临时表，选取需要的数据。
' W' ~$ X% ?! c; ~2、清空原表。
3、临时表数据导入到原表。
0 @7 p1 n6 ~. C9 M# J$ b2 Y. V% B4、删除临时表。
; o* h' A$ ^/ I/ x- I9 Pmysql> select * from student;
. `2 n/ h- n5 A& }  j& X6 a+----+------+
| ID | NAME |
+----+------+
| 11 | aa |
: T( G/ f' r* |  k: r6 ]| 12 | aa |
| 13 | bb |
+ r2 V. J) l. F| 14 | bb |
0 `' C% P; a- g" f; k: ?| 15 | bb |
  c# f8 a( ]( l( |1 e$ || 16 | cc |
' B% a" z; ]5 W' o7 m/ b+----+------+
$ A' a' a7 t2 [+ ]# j1 t% C6 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
Records: 3 Duplicates: 0 Warnings: 0

mysql> select * from student;
% e6 B- T' x; M6 _- f+----+------+
4 \' v9 F0 y9 c| ID | NAME |
( e/ {* D- C" ^+----+------+
5 a8 `/ l) v+ q5 o* Z* F; t/ n| 11 | aa |
| 13 | bb |
" E- c$ w) ^6 |- ?/ g! U2 m! T| 16 | cc |
5 s/ M/ W' ?# B+----+------+
9 I( b+ L9 A; k8 @3 rows in set

mysql> drop temporary table temp;
2 e/ G/ D( }+ o8 \4 ]# v9 ^1 uQuery 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;
) M( `: U, j7 r" w: gQuery OK, 3 rows affected
Records: 3 Duplicates: 0 Warnings: 0

mysql> delete from student where id not in (select minid from temp);
% P! y9 q8 f. gQuery OK, 3 rows affected

mysql> select * from student;
) |% P. b9 u+ U9 ]9 P0 }+----+------+
1 A: _- q6 C0 @4 U" C) L" j| ID | NAME |
* n. Z0 D& f7 C- a# ~+----+------+
| 11 | aa |
8 p. w8 o/ P3 B. K; M| 13 | bb |
6 t% a; d* M' f| 16 | cc |
8 I, A" \0 Q+ e% F5 k* \+----+------+
, j& E/ w/ _/ R% B: c7 |; b: T3 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
+ Y! o- ~8 `3 v  t2 t  m) a$ M原因是：更新数据时使用了查询，而查询的数据又做了更新的条件，mysql不支持这种方式。
怎么规避这个问题？
8 S6 c' X* @5 a再加一层封装，如下：
8 ?9 h/ _# t$ [% D1 [& S% kmysql> delete from student where id not in (select minid from (select min(id) as minid from student group by name) b);
% g8 g( J2 E# X  e4 i% zQuery OK, 3 rows affected

mysql> select * from student;
+----+------+
1 ?' ~7 b* m8 N| ID | NAME |
+----+------+
| 11 | aa |
| 13 | bb |
: I  p1 D1 ?+ A4 p, k  Z! E| 16 | cc |
8 H' {, I) D+ K) w# w5 ?% Q+----+------+
3 rows in set

8 Z/ S8 B8 c3 E( M4 r5 w3 ?方法三例如：delete from bugdata where id not in (select minid from (select min(id) as minid from bugdata group by qxbh) b);

1 C5 X, T8 _# q; `
( I- P0 I3 S; \  K6 N