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

admin

方法1：
4 W  |, X, J& @# _2 R1、创建一个临时表，选取需要的数据。
2、清空原表。
3、临时表数据导入到原表。
: v; n; W( i# Y# A. w: q. \4、删除临时表。
# i$ |! Q8 L) u. J! `mysql> select * from student;
( k: H) Y) ?3 R2 ]3 h+ P+----+------+
| ID | NAME |
+----+------+
  J9 |5 s' w7 G5 ?0 u| 11 | aa |
| 12 | aa |
| 13 | bb |
| 14 | bb |
| 15 | bb |
' b5 m+ G; r* d0 x6 U# d| 16 | cc |
% i3 e' [, ]0 A+ X5 c8 c- D% r. s+----+------+
- V  j$ Y# t4 }1 ^% l% \* C6 rows in set

mysql> create temporary table temp as select min(id),name from student group by name;
Query OK, 3 rows affected
6 |, q4 G1 Z6 H5 j+ o- q9 Y' YRecords: 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
& N  p. o6 A$ i* VRecords: 3 Duplicates: 0 Warnings: 0

mysql> select * from student;
+----+------+
| ID | NAME |
( |$ y' D3 O$ Y$ ?$ ?- i+----+------+
1 \/ Y  W7 ^- f0 g# O  A: _| 11 | aa |
$ c# U! b+ B2 k8 ]5 V8 S| 13 | bb |
| 16 | cc |
+----+------+
3 rows in set

mysql> drop temporary table temp;
Query OK, 0 rows affected
这个方法，显然存在效率问题。

---

方法2：按name分组，把最小的id保存到临时表，删除id不在最小id集合的记录，如下：
. s6 V& }! Q! gmysql> create temporary table temp as select min(id) as MINID from student group by name;
Query OK, 3 rows affected
' W1 I8 o- Z' }9 B% w3 ~Records: 3 Duplicates: 0 Warnings: 0

mysql> delete from student where id not in (select minid from temp);
/ m6 c3 f: G8 ^4 BQuery OK, 3 rows affected

mysql> select * from student;
+----+------+
| ID | NAME |
+----+------+
! v7 r6 H) p% F| 11 | aa |
| 13 | bb |
| 16 | cc |
  e! Q2 _; Y+ V; j, ^7 w- p+----+------+
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不支持这种方式。
# T- o/ l) m' W4 G1 d2 g. @& U- L怎么规避这个问题？
再加一层封装，如下：
mysql> delete from student where id not in (select minid from (select min(id) as minid from student group by name) b);
' z4 m( u6 ?6 jQuery OK, 3 rows affected

mysql> select * from student;
- D  H  k5 s5 N/ E: U. K% S+----+------+
& T* L9 o- ^8 l6 x* v| ID | NAME |
9 h2 X0 L3 i6 X+----+------+
| 11 | aa |
| 13 | bb |
7 f: `7 X) t3 q7 F; \| 16 | cc |
2 d; X# U6 r) K1 w, S4 J# \+ o+----+------+
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);
9 p8 }: P0 }) T) y0 S2 Z3 h
; o1 w% D6 ~( n  D9 _+ D