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

admin

方法1：
) |: {) t5 t$ x; P9 y  q1、创建一个临时表，选取需要的数据。
& f; |. C/ {. Q9 c) V: t8 ?# a& j2、清空原表。
( b. R% ~! K8 n) f* R: P3、临时表数据导入到原表。
4、删除临时表。
1 N, G7 ?% B: S7 K" u$ I& S3 |mysql> select * from student;
6 O9 g9 B% V& j) p9 N+----+------+
, A* ], F8 |9 Y| ID | NAME |
+----+------+
| 11 | aa |
8 }! f$ a  j6 E% F- L' e4 R1 R| 12 | aa |
' w% G8 }  B# E1 i. V' u" O/ T| 13 | bb |
3 u/ Y& o$ s* Z8 u4 f  u: v7 q| 14 | bb |
| 15 | bb |
/ E, n( ?  x) T" Y" x7 ?| 16 | cc |
7 S( I& Y0 |( @2 z6 n, y$ D" i+----+------+
6 rows in set

mysql> create temporary table temp as select min(id),name from student group by name;
5 c. A, G/ H4 v/ _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;
+----+------+
| ID | NAME |
+----+------+
' M( B0 N7 q) ?5 ~# n# |" ^1 n3 P, H+ A, T| 11 | aa |
| 13 | bb |
) i1 Y* y7 h. i% n/ h| 16 | cc |
6 u/ ~2 ?5 c& A. D' r" W5 z" c+----+------+
3 rows in set

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

---

方法2：按name分组，把最小的id保存到临时表，删除id不在最小id集合的记录，如下：
' _; Q, r6 z# |0 Jmysql> create temporary table temp as select min(id) as MINID from student group by name;
- Q$ Y  N. ]$ T- n' `Query OK, 3 rows affected
Records: 3 Duplicates: 0 Warnings: 0

mysql> delete from student where id not in (select minid from temp);
4 T! H8 \( q1 m. F% [' rQuery OK, 3 rows affected

mysql> select * from student;
+----+------+
$ V3 r& S& z0 Z( r7 ], s& }& [| ID | NAME |
8 [) i! y) w7 S; a/ q6 m$ i+----+------+
| 11 | aa |
# S, J$ b* h; Z  X| 13 | bb |
| 16 | cc |
+ i3 R/ Q3 r1 X; B* I0 B9 E7 [+ s) [+----+------+
3 rows in set

---

方法3：直接在原表上操作，容易想到的sql语句如下：

mysql> delete from student where id not in (select min(id) from student group by name);
- H8 }7 N0 N2 \# |& A1 `执行报错：1093 - You can't specify target table 'student' for update in FROM clause
3 n* C0 l. ?8 _2 T1 j# g原因是：更新数据时使用了查询，而查询的数据又做了更新的条件，mysql不支持这种方式。
怎么规避这个问题？
再加一层封装，如下：
, U- e/ p' D9 _$ z" G. J4 m& lmysql> 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;
! B! O8 Z: D" W7 @+----+------+
| ID | NAME |
+----+------+
3 V& Y, Y& O0 r2 ~$ y3 s0 M/ v& q| 11 | aa |
| 13 | bb |
| 16 | cc |
+----+------+
; h1 A/ Q# |  q% _! L3 rows in set

方法三例如：delete from bugdata where id not in (select minid from (select min(id) as minid from bugdata group by qxbh) b);

  B( {7 ^7 H: D  z# h% e$ S
1 S8 _; D9 x' |$ g' k1 f. V* \) w/ Y