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

admin

方法1：
/ G' M/ a4 C8 N4 \1、创建一个临时表，选取需要的数据。
" m0 V; ~0 \0 X; |* F- W) Q/ y2、清空原表。
3、临时表数据导入到原表。
9 s9 |  C/ q& R& V4、删除临时表。
! b; Y9 n# ]) e: lmysql> select * from student;
  X( J* s: k5 t: n+----+------+
| ID | NAME |
) t! D4 W3 C) F8 T- o' \+----+------+
1 w/ B- B  U4 r8 s8 R: |7 R2 Q  L| 11 | aa |
| 12 | aa |
| 13 | bb |
| 14 | bb |
| 15 | bb |
) O7 N+ S) s. y6 }0 r5 X| 16 | cc |
; b  v* F' V/ J$ q+----+------+
3 |3 D' \4 r* D# N" F6 rows in set

mysql> create temporary table temp as select min(id),name from student group by name;
Query OK, 3 rows affected
Records: 3 Duplicates: 0 Warnings: 0

mysql> truncate table student;
8 [9 `2 f5 p( l; [6 hQuery OK, 0 rows affected

mysql> insert into student select * from temp;
Query OK, 3 rows affected
Records: 3 Duplicates: 0 Warnings: 0

mysql> select * from student;
+----+------+
: S% e( b+ O6 v5 H3 G7 M| ID | NAME |
+----+------+
+ J5 l0 c! ?# v+ N| 11 | aa |
| 13 | bb |
| 16 | cc |
$ U) C8 n# X; d9 O$ L+----+------+
3 rows in set

mysql> drop temporary table temp;
Query OK, 0 rows affected
' {& [, q/ G& \3 `这个方法，显然存在效率问题。

---

方法2：按name分组，把最小的id保存到临时表，删除id不在最小id集合的记录，如下：
5 Q6 W( |4 K! b5 O8 v; Z% ]8 [. Imysql> create temporary table temp as select min(id) as MINID from student group by name;
4 \  K3 M6 N) R! i' X" ~2 L- DQuery OK, 3 rows affected
Records: 3 Duplicates: 0 Warnings: 0

mysql> delete from student where id not in (select minid from temp);
Query OK, 3 rows affected

mysql> select * from student;
9 J% q2 `2 E& u+----+------+
| ID | NAME |
, \$ _9 G1 X) e% a% Q- I. T+----+------+
- o0 G# f9 r2 {6 H" p4 U2 }+ z) L4 X| 11 | aa |
| 13 | bb |
| 16 | cc |
3 B, v- U1 Y! V$ S- j+ S, o+----+------+
& K: Z+ g$ W4 Z$ m: x9 c3 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
% `4 y  v! v3 \/ L7 W/ Q7 S) @, C" _原因是：更新数据时使用了查询，而查询的数据又做了更新的条件，mysql不支持这种方式。
怎么规避这个问题？
再加一层封装，如下：
8 t) O; k7 G! C: R- xmysql> delete from student where id not in (select minid from (select min(id) as minid from student group by name) b);
9 |1 p" _& m  e( v. _1 gQuery OK, 3 rows affected

mysql> select * from student;
  q9 R' k0 t. E, U% v% F$ U, b+----+------+
| ID | NAME |
, ^& s- _. s. a+ F  W+----+------+
4 x: L+ u5 l" j& I& ^. c, i) a  a, \| 11 | aa |
| 13 | bb |
8 }4 W& {4 J7 w, p| 16 | cc |
+----+------+
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);
' T1 o( h" {# D% o" R' }# `2 [, S
9 m9 B, @. v. B! W+ s5 Q5 A
( Z% y: \  g: t6 ]/ R