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

admin

方法1：
1、创建一个临时表，选取需要的数据。
' }; Z; a3 L; u% _$ y- ]/ G$ _2、清空原表。
3、临时表数据导入到原表。
4、删除临时表。
mysql> select * from student;
! R$ [" i4 E: z: i% M+----+------+
| ID | NAME |
6 g  A$ }# o4 U9 D+----+------+
| 11 | aa |
3 j, K3 v& w' O$ u# i| 12 | aa |
4 L% P1 w3 G' P+ R| 13 | bb |
| 14 | bb |
| 15 | bb |
. F8 o( P3 k/ {" D! s" i| 16 | cc |
7 k! p0 f2 v# U. k. a+----+------+
6 rows in set

mysql> create temporary table temp as select min(id),name from student group by name;
% X1 s9 A4 i# v4 y" }; ?- b; BQuery 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;
8 `$ j! p& N. M* M' B* E& @Query OK, 3 rows affected
Records: 3 Duplicates: 0 Warnings: 0

mysql> select * from student;
+----+------+
| ID | NAME |
! M. ~/ T8 u- f' J4 R2 z( E+----+------+
! ?: h- C+ z2 Z| 11 | aa |
- y9 h+ g. s( D| 13 | bb |
| 16 | cc |
6 D2 S6 Z/ U: `: }% D+----+------+
3 rows in set

mysql> drop temporary table temp;
Query OK, 0 rows affected
2 ^* c5 N- w; |+ @( V$ M. y这个方法，显然存在效率问题。

---

方法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
Records: 3 Duplicates: 0 Warnings: 0

mysql> delete from student where id not in (select minid from temp);
Query OK, 3 rows affected

mysql> select * from student;
; ~/ _* o5 e& \& B+----+------+
| ID | NAME |
' ]  b: o" z+ C' W+----+------+
1 B( j3 X  d' l$ C| 11 | aa |
3 L/ w3 M: _7 c. s4 u| 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
& N0 x1 Q, b: f1 E& N原因是：更新数据时使用了查询，而查询的数据又做了更新的条件，mysql不支持这种方式。
怎么规避这个问题？
8 L1 D$ I1 o# [5 N/ O- j. h8 ~0 ~5 _再加一层封装，如下：
2 j4 T) u; I5 n& b* mmysql> 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;
+----+------+
) c1 N; g' @# t" r. v& o( s0 F, ^- i) ^8 b| ID | NAME |
+----+------+
6 f: ?7 K- Q9 P3 m3 U9 M| 11 | aa |
( n- [9 j7 }. b( P0 N| 13 | bb |
% b4 Y4 u' l% F| 16 | cc |
+ i- ~$ Q/ I9 |7 P7 p" b" \# `+----+------+
3 rows in set

  m0 s! C* e/ N1 n2 ?方法三例如：delete from bugdata where id not in (select minid from (select min(id) as minid from bugdata group by qxbh) b);
+ ^/ \' x  {6 {4 {- |2 Y% _

8 }9 s3 W0 ^3 ?! ?6 L7 `" H3 p. S% ]