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

admin

方法1：
1、创建一个临时表，选取需要的数据。
% V, x( b/ k$ {2、清空原表。
3、临时表数据导入到原表。
# |9 {7 M9 c( J) z+ R8 T" y% Y4、删除临时表。
) V* P$ ]1 Y( k+ v. K. E6 `mysql> select * from student;
+----+------+
| ID | NAME |
+----+------+
1 K' }0 K/ C% S# _+ ?| 11 | aa |
| 12 | aa |
( e; H0 d! a2 x( r0 L# n3 l5 c4 L| 13 | bb |
' l0 z: v/ [4 @* L% Z| 14 | bb |
| 15 | bb |
| 16 | cc |
# g$ e& {& D4 e  k+----+------+
6 rows in set

mysql> create temporary table temp as select min(id),name from student group by name;
Query OK, 3 rows affected
( v# F$ S8 X3 A6 eRecords: 3 Duplicates: 0 Warnings: 0

mysql> truncate table student;
. R! \$ A: I) `( j4 Q: ]: ]% qQuery OK, 0 rows affected

mysql> insert into student select * from temp;
Query OK, 3 rows affected
: A& [" F2 |9 Q5 k7 mRecords: 3 Duplicates: 0 Warnings: 0

mysql> select * from student;
+----+------+
: h" v9 v) K1 Z/ \| ID | NAME |
2 L: I+ N7 {, ^7 f$ F1 n+----+------+
! z. x6 n* j5 n2 R+ h* N) W; G9 v| 11 | aa |
| 13 | bb |
| 16 | cc |
+----+------+
, g- t6 w( s1 e4 O( V/ A4 r, h3 rows in set

mysql> drop temporary table temp;
Query OK, 0 rows affected
这个方法，显然存在效率问题。

---

方法2：按name分组，把最小的id保存到临时表，删除id不在最小id集合的记录，如下：
mysql> 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);
Query OK, 3 rows affected

mysql> select * from student;
( I* l: W/ m7 @0 q. A- O% L+----+------+
1 Z. \& z2 z2 L0 N$ `/ |( |; R| ID | NAME |
$ e% }0 o8 S) L4 T4 d6 K+----+------+
- x& V3 \0 L/ ?  K| 11 | aa |
| 13 | bb |
% G1 p+ j* N! Z- ^5 }| 16 | cc |
. L& U* S( q  @$ f& x1 n+----+------+
( ], e) d" V- f4 r& |. h3 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不支持这种方式。
怎么规避这个问题？
再加一层封装，如下：
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;
+----+------+
| ID | NAME |
+----+------+
/ x/ G3 T: X  e- @) g| 11 | aa |
3 J% b# G# a" i| 13 | bb |
& a9 `% [% y3 ~- ~7 p7 ?: u| 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);
9 s& A3 S- }/ `/ q' `% N* r# W! N

, N. t: l/ }& ]7 y