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

admin

方法1：
: e2 Q# C4 D/ q1、创建一个临时表，选取需要的数据。
: }  a9 d1 T3 h5 U  M2、清空原表。
3、临时表数据导入到原表。
4、删除临时表。
mysql> select * from student;
# n6 O8 |4 I$ a( Y: R+----+------+
| ID | NAME |
0 ~+ Q2 F" N; c" f. p+ J$ v+----+------+
| 11 | aa |
| 12 | aa |
| 13 | bb |
2 {/ X2 @8 h  h% m- e| 14 | bb |
' w/ h7 ?+ u  |' U/ s| 15 | bb |
- t; u0 J3 E9 J2 i| 16 | cc |
+----+------+
  K& `8 O9 r7 |: B( x6 rows in set

mysql> create temporary table temp as select min(id),name from student group by name;
6 |+ ?4 |6 H2 ]( N! rQuery OK, 3 rows affected
" @1 G; ~7 z* E9 Z0 ]Records: 3 Duplicates: 0 Warnings: 0

mysql> truncate table student;
, j+ M- O  J. I' W( zQuery OK, 0 rows affected

mysql> insert into student select * from temp;
Query OK, 3 rows affected
: ?* I$ U1 R: j/ C0 E9 gRecords: 3 Duplicates: 0 Warnings: 0

mysql> select * from student;
& b2 J# t7 s; T# t7 F# X4 F6 m: A+----+------+
. N2 {" M  _) k* y  X8 _: @) i8 F| ID | NAME |
& O6 e5 l/ \6 L( E% U+----+------+
2 a% f! S8 m# x- i| 11 | aa |
& C2 P3 |4 ~+ m9 b8 R- n| 13 | bb |
| 16 | cc |
7 I& v0 S+ o! {0 v' S+----+------+
1 ?" q) m! v' a5 r, z% b, E3 rows in set

mysql> drop temporary table temp;
Query OK, 0 rows affected
" N: R/ f9 b4 d# ^' `6 {* J  o, r/ O/ z这个方法，显然存在效率问题。

---

方法2：按name分组，把最小的id保存到临时表，删除id不在最小id集合的记录，如下：
3 B9 u( e) v0 U6 Tmysql> create temporary table temp as select min(id) as MINID from student group by name;
Query OK, 3 rows affected
2 P( k. F4 `- b! kRecords: 3 Duplicates: 0 Warnings: 0

mysql> delete from student where id not in (select minid from temp);
* J/ Y& N9 m1 m2 I0 QQuery OK, 3 rows affected

mysql> select * from student;
+ w9 c% W# k4 x* d8 o. D. P+----+------+
) c5 ^3 S1 k; J/ O9 v; Q$ Z| ID | NAME |
+----+------+
| 11 | aa |
| 13 | bb |
; u& l2 k6 y+ C. ^| 16 | cc |
1 ^: k8 l" ?4 a( c0 L$ F+ h" v* M: F+----+------+
2 e" s; C( p  L$ J% J6 T' E/ 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
原因是：更新数据时使用了查询，而查询的数据又做了更新的条件，mysql不支持这种方式。
8 b, Q8 _4 F5 n  ^( r怎么规避这个问题？
再加一层封装，如下：
mysql> 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;
+----+------+
7 S; O; M9 D* p) ]& i| ID | NAME |
' c7 O5 v, h( W0 b9 a+----+------+
| 11 | aa |
| 13 | bb |
: }) w+ w) F& H4 z# v: [| 16 | cc |
+----+------+
3 rows in set

7 V+ R3 K2 @3 `5 U& g9 H方法三例如：delete from bugdata where id not in (select minid from (select min(id) as minid from bugdata group by qxbh) b);
7 a  p( ?. \5 f
2 v+ j5 v8 t. w: R0 Q, t, d
) B- J' k9 H6 M# u5 I7 [