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

admin

方法1：
1、创建一个临时表，选取需要的数据。
2、清空原表。
3、临时表数据导入到原表。
* u( n6 a3 g7 S) C: `4 l/ z& }4、删除临时表。
mysql> select * from student;
8 I: w/ ]) J+ l3 @  M7 A+----+------+
% ?9 S4 L: N9 S/ ?8 T, B- {| ID | NAME |
3 ?5 q+ Z* c9 U" y% z2 l+----+------+
9 K9 f8 b3 I6 T| 11 | aa |
| 12 | aa |
3 t, d9 o  n/ y$ Y" ~5 {- o| 13 | bb |
| 14 | bb |
* y9 W3 _$ z* ^1 a) R* L6 E% ]| 15 | bb |
. X9 Y2 {, `9 \| 16 | cc |
+----+------+
/ P/ ?; S% q6 Z. _5 E0 r  |2 J6 rows in set

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

mysql> truncate table student;
( i) z" h! w% ?$ {! _6 o0 Q8 [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;
; f6 j3 t9 f; |$ }4 ], p, z- g+----+------+
| ID | NAME |
4 L& r/ D$ G' v! i( v+----+------+
& L7 u0 y, o9 x2 U+ w4 J| 11 | aa |
# |9 ?" Z# _- x7 W| 13 | bb |
3 T- q: C# W- P) G| 16 | cc |
4 t+ m% D: S5 G( q3 k& `- v+----+------+
3 rows in set

mysql> drop temporary table temp;
Query OK, 0 rows affected
( }7 P4 X9 t# n这个方法，显然存在效率问题。

---

方法2：按name分组，把最小的id保存到临时表，删除id不在最小id集合的记录，如下：
: A" x# Z* C) mmysql> 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);
2 x8 |% l: G' q3 F) kQuery OK, 3 rows affected

mysql> select * from student;
+----+------+
| ID | NAME |
+----+------+
| 11 | aa |
| 13 | bb |
| 16 | cc |
+----+------+
1 o% ?4 |: j$ Q& y! j6 F3 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
* \& D* x) ~* J原因是：更新数据时使用了查询，而查询的数据又做了更新的条件，mysql不支持这种方式。
怎么规避这个问题？
再加一层封装，如下：
) E' L4 @* y# V( z. Mmysql> delete from student where id not in (select minid from (select min(id) as minid from student group by name) b);
+ R. s1 m+ k* \4 s" j" F# r# hQuery OK, 3 rows affected

mysql> select * from student;
+----+------+
( J( |" n9 ^" D1 t; a3 C| ID | NAME |
9 n5 H: j* g: r6 r" H# V/ I+----+------+
| 11 | aa |
# Q. `* K; Q( J% A/ z| 13 | bb |
0 |4 }/ E) z0 Q& Y/ ]3 j4 y| 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);
: ~% ~. o5 E" P& P2 F