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

admin

方法1：
1、创建一个临时表，选取需要的数据。
2、清空原表。
3、临时表数据导入到原表。
4、删除临时表。
mysql> select * from student;
+----+------+
| ID | NAME |
+ P3 N- v0 m( Y  z. I6 i2 s$ g/ n2 S+----+------+
| 11 | aa |
| 12 | aa |
| 13 | bb |
| 14 | bb |
, D) n5 k* U, P! ]5 h; Q; }7 v| 15 | bb |
| 16 | cc |
+ u% r2 _1 h  p. q+----+------+
$ `& i( p4 b2 O9 W  _6 rows in set

mysql> create temporary table temp as select min(id),name from student group by name;
Query OK, 3 rows affected
- T! p4 O; p) l) e) ~Records: 3 Duplicates: 0 Warnings: 0

mysql> truncate table student;
" n: B- g" R+ K+ B' r$ u: ]Query OK, 0 rows affected

mysql> insert into student select * from temp;
) {  }1 W" h$ k: Y2 T4 u. p: UQuery OK, 3 rows affected
. K, z  t( k6 T4 L7 ERecords: 3 Duplicates: 0 Warnings: 0

mysql> select * from student;
+----+------+
( ~7 y6 g7 k$ f: d3 d| ID | NAME |
+----+------+
| 11 | aa |
: z; Y$ P* _) {! _0 H) n; [| 13 | bb |
| 16 | cc |
+----+------+
/ T4 S1 r  B  y* }( a" [1 X3 rows in set

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

---

方法2：按name分组，把最小的id保存到临时表，删除id不在最小id集合的记录，如下：
3 |% V# X, Q+ s0 G" g" T  Emysql> create temporary table temp as select min(id) as MINID from student group by name;
7 v* ], T$ O) [; A8 N  FQuery OK, 3 rows affected
/ T/ E% c, C. A( m  ]1 L( P" ]6 u5 F6 ^Records: 3 Duplicates: 0 Warnings: 0

mysql> delete from student where id not in (select minid from temp);
- l, G5 o7 U) U* j5 gQuery OK, 3 rows affected

mysql> select * from student;
5 {  A! L' w% o. N' a4 p+----+------+
| ID | NAME |
: B/ l# r& J4 P. Y! h; \5 o- W$ y+----+------+
: z3 q1 {/ f# {* P# e- n| 11 | aa |
) Z" q+ p0 |) t& z# `% y; p( W| 13 | bb |
| 16 | cc |
+----+------+
( Q6 ?. Y3 v9 J5 |3 rows in set

---

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

mysql> delete from student where id not in (select min(id) from student group by name);
3 p. l3 k8 W- A/ t1 X; B执行报错：1093 - You can't specify target table 'student' for update in FROM clause
/ ]5 }" v# @2 G+ {  \- D1 U) V; B原因是：更新数据时使用了查询，而查询的数据又做了更新的条件，mysql不支持这种方式。
# ?$ q$ S" a1 W怎么规避这个问题？
再加一层封装，如下：
mysql> delete from student where id not in (select minid from (select min(id) as minid from student group by name) b);
) n) f( f  S1 d6 |( ?/ ~5 Q, pQuery OK, 3 rows affected

mysql> select * from student;
5 X. p& r0 g5 h& t  r# O. w+----+------+
| ID | NAME |
+----+------+
+ H0 c' p* M6 J# O9 M| 11 | aa |
| 13 | bb |
% t; I5 e. E! b! z" R/ }( U| 16 | cc |
! q( j1 o2 B8 c5 e/ D) e2 Z, h9 ^+----+------+
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);
; L5 z8 T1 u! m4 p9 `6 x5 I