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

admin

方法1：
1、创建一个临时表，选取需要的数据。
5 W* U" a1 C& _, B3 b2、清空原表。
* ^7 R- J+ p: u$ ]7 I3、临时表数据导入到原表。
0 o% E. j, b. G4 g9 J& C4、删除临时表。
$ T; J, U! H) o9 g* y: F* m6 jmysql> select * from student;
, _2 k8 E) q- f+ V7 D& X+----+------+
) y8 E( I$ g3 R$ M8 S. q& n8 C| ID | NAME |
+----+------+
2 U0 {  N( Z/ C4 D# Y* A| 11 | aa |
2 Y  p2 x7 m  s! t| 12 | aa |
| 13 | bb |
1 q6 c% K0 A4 N+ W6 w% t" C| 14 | bb |
| 15 | bb |
| 16 | cc |
+----+------+
6 rows in set

mysql> create temporary table temp as select min(id),name from student group by name;
# G. {. U7 Z2 I6 s5 [Query 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;
& S, i7 ^5 J; R5 d* N) HQuery OK, 3 rows affected
0 D6 i5 @/ X4 h* f& n3 Q2 |Records: 3 Duplicates: 0 Warnings: 0

mysql> select * from student;
+----+------+
2 ?, l9 [" o2 u| ID | NAME |
+----+------+
| 11 | aa |
5 A9 \+ W6 X9 _  P! ~1 J| 13 | bb |
) `/ M6 t3 o1 d% h' t' c5 Z| 16 | cc |
+----+------+
1 y. M4 Y3 H" }/ w% }2 v, B3 rows in set

mysql> drop temporary table temp;
Query OK, 0 rows affected
2 ^1 N8 S1 e! g, U# g5 i这个方法，显然存在效率问题。

---

方法2：按name分组，把最小的id保存到临时表，删除id不在最小id集合的记录，如下：
7 o# k( X- t8 b# j+ F  A% S: ^9 h! Q. w2 umysql> 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);
2 b) G4 j" a/ F3 e; ~+ tQuery OK, 3 rows affected

mysql> select * from student;
+----+------+
1 g: O2 r, ]. x- c, Y" V2 A% y- `- k| ID | NAME |
& l3 ^. m' N( y: X# [+----+------+
) n/ m9 ~3 u" `& g: |0 H! g| 11 | aa |
+ L6 J. W# {6 w7 B( I) ^| 13 | bb |
1 n+ e% I- _; [8 R| 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
; X. s9 u. P6 H+ f原因是：更新数据时使用了查询，而查询的数据又做了更新的条件，mysql不支持这种方式。
5 y' ~- A( I5 G0 Q( ]怎么规避这个问题？
* r0 H: ^8 |! O0 y& l再加一层封装，如下：
$ c2 U1 S2 s* i) |8 kmysql> 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 |
& u. V* Y: c) n! O. c+----+------+
| 11 | aa |
/ _! p) Y0 i! u| 13 | bb |
| 16 | cc |
5 U$ q. M- v+ x- U! P+----+------+
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);