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

admin

方法1：
1 ~5 E! s; }( @% N1、创建一个临时表，选取需要的数据。
4 u7 Z" _7 _0 \0 a. b4 ~6 t2、清空原表。
3、临时表数据导入到原表。
4、删除临时表。
1 L  ~/ j) t( rmysql> select * from student;
: f) }2 Y+ s0 m% f- K+----+------+
5 ?; Q4 V& X3 _* D| ID | NAME |
+----+------+
; l( f7 T& H7 b/ d$ E| 11 | aa |
7 s; u  c6 _. |! B3 f4 h* }2 b| 12 | aa |
% p2 @1 F7 @; o  j0 q| 13 | bb |
| 14 | bb |
| 15 | bb |
# Q1 {  l' W! ~( N$ Z/ G' _7 I| 16 | cc |
+----+------+
6 rows in set

mysql> create temporary table temp as select min(id),name from student group by name;
$ x* w2 n: @2 t+ m$ I' TQuery 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;
9 x3 w3 U' F' ^2 D# rQuery OK, 3 rows affected
& a0 r0 i: w0 Y; a  PRecords: 3 Duplicates: 0 Warnings: 0

mysql> select * from student;
+----+------+
& H# K* ]/ ~1 Q; G7 N; f. k| ID | NAME |
; S0 W& i+ u4 v" V) y# b% N+----+------+
| 11 | aa |
| 13 | bb |
| 16 | cc |
+----+------+
4 C4 ?( H* M# S& y' M6 y5 q3 rows in set

mysql> drop temporary table temp;
3 ^" j' i+ w3 p  Z4 Z) gQuery 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;
Query OK, 3 rows affected
( m2 m& T" c/ C7 VRecords: 3 Duplicates: 0 Warnings: 0

mysql> delete from student where id not in (select minid from temp);
3 T4 J6 y) n; V9 m, }5 A3 ZQuery OK, 3 rows affected

mysql> select * from student;
+----+------+
7 o; Z5 E% r3 Y% X5 j' {, c' {| ID | NAME |
+----+------+
| 11 | aa |
9 T- V! f) e: S' V| 13 | bb |
| 16 | cc |
, d6 B3 Z+ `7 F: t( I+----+------+
3 rows in set

---

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

mysql> delete from student where id not in (select min(id) from student group by name);
$ X( _# h  \: x1 ^9 Y" @1 d执行报错：1093 - You can't specify target table 'student' for update in FROM clause
原因是：更新数据时使用了查询，而查询的数据又做了更新的条件，mysql不支持这种方式。
怎么规避这个问题？
再加一层封装，如下：
, T/ K# q+ m3 ]- tmysql> 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;
+----+------+
| ID | NAME |
+----+------+
( y8 V) n4 N* I2 q4 E2 f8 R. C+ a| 11 | aa |
| 13 | bb |
0 Q6 C$ k0 Y! P5 B- a| 16 | cc |
0 g. [6 a9 A0 _6 ?- ~+----+------+
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);
/ `. [7 a% A1 i