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

admin

方法1：
& V' A& ]- g/ {1、创建一个临时表，选取需要的数据。
, C, G5 Z. L$ @' T7 C$ {2、清空原表。
3、临时表数据导入到原表。
0 a: r5 c! z/ v9 y" A- t4、删除临时表。
4 X+ b8 |  y7 T$ C/ v1 S* kmysql> select * from student;
- z6 r. M1 ]0 |8 i8 j8 O+----+------+
| ID | NAME |
+----+------+
| 11 | aa |
2 H( R: a- ~/ V1 s  L| 12 | aa |
0 T6 C/ t1 S& u3 x  m7 z0 I| 13 | bb |
% l7 Y* S' V0 w  N1 G# _2 I| 14 | bb |
| 15 | bb |
, \8 t& {: n8 u| 16 | cc |
0 d& Y$ Z4 t1 [6 C- U+----+------+
6 rows in set

mysql> create temporary table temp as select min(id),name from student group by name;
# u7 H3 u: K. ~Query OK, 3 rows affected
( y4 _! q: B; K8 MRecords: 3 Duplicates: 0 Warnings: 0

mysql> truncate table student;
8 _5 l. Y+ A' h% a* G1 u0 w8 TQuery OK, 0 rows affected

mysql> insert into student select * from temp;
3 S6 C+ c# ^" x- ^$ [+ i$ X/ l4 E7 d% [; AQuery OK, 3 rows affected
Records: 3 Duplicates: 0 Warnings: 0

mysql> select * from student;
/ G  E) w$ ~, J; |5 q9 l+----+------+
| ID | NAME |
" `2 j0 t) z+ i/ a7 X1 i+----+------+
( q; P' }( V3 G+ \| 11 | aa |
" n' l( }3 A3 z8 q8 z. G% Y4 K* Y| 13 | bb |
| 16 | cc |
+----+------+
6 W& K6 l/ w$ c* J( h3 o' P( f. R3 rows in set

mysql> drop temporary table temp;
1 [* _# F8 l' F0 t- C$ UQuery OK, 0 rows affected
$ j4 L( B+ x  v0 s: ~这个方法，显然存在效率问题。

---

方法2：按name分组，把最小的id保存到临时表，删除id不在最小id集合的记录，如下：
3 Z9 h2 W3 {2 |9 Zmysql> 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);
6 }( Z' ?; n$ J, i+ M' jQuery OK, 3 rows affected

mysql> select * from student;
. C+ p  \& k: l" C+----+------+
# S! E3 T6 ]3 D, }7 C8 x, P| ID | NAME |
+----+------+
| 11 | aa |
| 13 | bb |
/ a. l( h! N4 Q, i4 r| 16 | cc |
3 t; C2 L; p, W1 N$ E6 R/ b5 d+----+------+
! v9 I  c. z8 p& a3 z7 v3 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不支持这种方式。
怎么规避这个问题？
再加一层封装，如下：
9 l! Q5 Q% M, K" Q% S: Q& |2 j/ wmysql> 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;
1 Y# f4 B$ u6 ~+----+------+
| ID | NAME |
+----+------+
! W0 N1 m4 O7 Y+ r7 U& y| 11 | aa |
: \) {% `! C9 e| 13 | bb |
| 16 | cc |
+----+------+
/ p$ b% q  f. D  S' b3 rows in set

$ _2 U. J2 n& _2 {; J$ A) c方法三例如：delete from bugdata where id not in (select minid from (select min(id) as minid from bugdata group by qxbh) b);
4 S& M5 F) I6 f" @2 ^  v
$ t5 I; E+ x9 y3 ?* ]5 B( R; Y( O
/ _. @: x& p* n