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

admin

方法1：
7 ~& c6 g8 [* T4 f: Q1、创建一个临时表，选取需要的数据。
2、清空原表。
9 [7 I( j  K8 g" e* M! X3、临时表数据导入到原表。
0 m: P, ]: p( s6 Z$ Q2 [3 V# N4、删除临时表。
mysql> select * from student;
+----+------+
| ID | NAME |
+----+------+
  s* Y; @4 M8 ]| 11 | aa |
| 12 | aa |
| 13 | bb |
. q) P5 i/ E1 p* p| 14 | bb |
: m; l% j8 A& s$ [4 q| 15 | bb |
| 16 | cc |
+----+------+
6 rows in set

mysql> create temporary table temp as select min(id),name from student group by name;
4 k4 F: h* G3 f' h* YQuery OK, 3 rows affected
! {4 P2 U  Y) O" c8 @Records: 3 Duplicates: 0 Warnings: 0

mysql> truncate table student;
Query OK, 0 rows affected

mysql> insert into student select * from temp;
2 g: |4 N+ M( f0 C1 a6 f3 p. Z5 m/ s2 PQuery OK, 3 rows affected
Records: 3 Duplicates: 0 Warnings: 0

mysql> select * from student;
+----+------+
| ID | NAME |
+----+------+
2 b2 J  W  r8 @| 11 | aa |
| 13 | bb |
" G* K+ y4 x& F| 16 | cc |
  k( j6 t+ j, _4 D' t+----+------+
4 f% X' U4 j) K# Q( |3 rows in set

mysql> drop temporary table temp;
Query OK, 0 rows affected
! y% F! @6 c- _9 K1 M6 ^6 t这个方法，显然存在效率问题。

---

方法2：按name分组，把最小的id保存到临时表，删除id不在最小id集合的记录，如下：
mysql> create temporary table temp as select min(id) as MINID from student group by name;
* y( K( Z# d& \; CQuery OK, 3 rows affected
" K( s" X4 L$ y. J7 ?1 RRecords: 3 Duplicates: 0 Warnings: 0

mysql> delete from student where id not in (select minid from temp);
1 v+ l$ c: }6 o$ l$ i( FQuery OK, 3 rows affected

mysql> select * from student;
4 B' y3 w8 K9 }2 a/ Z+----+------+
8 X. L, ]/ t4 v% G4 [1 s! i7 @| ID | NAME |
. r4 U/ X5 w) H- _+----+------+
| 11 | aa |
. `" T+ S: m" J2 t  B6 _| 13 | bb |
| 16 | cc |
+----+------+
; I) p4 r& d3 p0 r( W& f2 d4 P3 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
: t) x0 A, t8 }% x原因是：更新数据时使用了查询，而查询的数据又做了更新的条件，mysql不支持这种方式。
: G0 t* b0 ]7 H. i. ]: H怎么规避这个问题？
" V; t) w3 H  `  i- W/ K再加一层封装，如下：
  A9 y' v' j$ p, R  [4 ]7 l: amysql> 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;
& c# v' s( A/ J$ f) i/ h+ q, K! _+----+------+
9 _9 f  c' ~/ t% l3 j7 L| ID | NAME |
' i3 b) {% W5 X9 f8 b! E/ }2 W+----+------+
| 11 | aa |
| 13 | bb |
| 16 | cc |
+----+------+
- C& `' ^6 L/ S3 rows in set

! N1 D% M( A* y+ W/ M: k0 m方法三例如：delete from bugdata where id not in (select minid from (select min(id) as minid from bugdata group by qxbh) b);

0 {* e/ ^5 W& c8 `- y& n  D* j9 E