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

admin

方法1：
1、创建一个临时表，选取需要的数据。
7 V, x) z3 W/ \; l/ ~4 w3 k2、清空原表。
3、临时表数据导入到原表。
4、删除临时表。
mysql> select * from student;
& C  C8 ?; @) ]3 E/ P+----+------+
& R4 F5 _; f+ u" Z5 ^% Z- |) t| ID | NAME |
+----+------+
| 11 | aa |
| 12 | aa |
| 13 | bb |
| 14 | bb |
% A1 d9 c5 F0 d7 j' ~* {- B| 15 | bb |
| 16 | cc |
+----+------+
. N7 X/ q, y' ^& f: C# n% s6 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;
Query 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;
+----+------+
, z0 b  n5 V4 s  R( I6 v| ID | NAME |
+----+------+
| 11 | aa |
# s( q6 [, x; _) T) k3 x: M. i| 13 | bb |
1 e# l& c4 I& X- x/ X| 16 | cc |
+----+------+
( }% S1 A) k/ r! b1 v1 ?+ W3 rows in set

mysql> drop temporary table temp;
Query OK, 0 rows affected
4 V4 s1 a0 F( z5 L  Y# V7 J这个方法，显然存在效率问题。

---

方法2：按name分组，把最小的id保存到临时表，删除id不在最小id集合的记录，如下：
mysql> create temporary table temp as select min(id) as MINID from student group by name;
8 s  y' f% e8 g8 F+ A7 `Query OK, 3 rows affected
7 l4 _7 {. g# g8 o# E- HRecords: 3 Duplicates: 0 Warnings: 0

mysql> delete from student where id not in (select minid from temp);
( `) V0 t4 w* a. Q8 G# QQuery OK, 3 rows affected

mysql> select * from student;
+----+------+
| ID | NAME |
* ~: L7 [: g0 m# W+----+------+
| 11 | aa |
| 13 | bb |
' F8 E9 E) S5 O$ F, \# B! e8 }" W| 16 | cc |
6 w* p* u( I, _* |$ }+----+------+
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
% u8 }( {3 Q  N$ T9 N$ w4 W& S原因是：更新数据时使用了查询，而查询的数据又做了更新的条件，mysql不支持这种方式。
怎么规避这个问题？
再加一层封装，如下：
mysql> delete from student where id not in (select minid from (select min(id) as minid from student group by name) b);
( ~: K, u$ r; S; G. Q6 _5 {+ kQuery OK, 3 rows affected

mysql> select * from student;
2 P1 i8 H- [. Y. u" o5 G* i+----+------+
| ID | NAME |
+----+------+
| 11 | aa |
3 ~, p4 T; R0 ~8 [: K; j| 13 | bb |
/ C$ _0 h4 o# F. K2 p9 }3 I| 16 | cc |
+ x' G' |) c0 g+----+------+
9 @% y9 J# Q8 c6 q5 R$ `! i3 rows in set

) j* B5 O3 ^) l9 J# i: t方法三例如：delete from bugdata where id not in (select minid from (select min(id) as minid from bugdata group by qxbh) b);

/ i1 e; d1 a) J5 p7 Y
* |) g% ^+ |* Q1 k9 O" N8 Z& X