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

admin

方法1：
1、创建一个临时表，选取需要的数据。
2、清空原表。
3、临时表数据导入到原表。
; w/ Q3 l' ~& |9 j# v$ S4、删除临时表。
mysql> select * from student;
9 _$ n  j. ~% N) K) h+----+------+
| ID | NAME |
0 l* E* ^! L% ~  I/ D+----+------+
| 11 | aa |
| 12 | aa |
| 13 | bb |
# D6 `/ _: w- o5 b; [' T| 14 | bb |
' A- j* B2 e; \) i, a4 B| 15 | bb |
| 16 | cc |
+ Z9 z, L5 @2 ~8 L0 j( [% F+----+------+
6 rows in set

mysql> create temporary table temp as select min(id),name from student group by name;
6 E5 }& B3 i* v% RQuery OK, 3 rows affected
) J! n8 ]; V: z* NRecords: 3 Duplicates: 0 Warnings: 0

mysql> truncate table student;
  n" }. X9 A+ ^7 e1 W. AQuery OK, 0 rows affected

mysql> insert into student select * from temp;
7 d; b" {, }* m. y7 pQuery OK, 3 rows affected
/ W  z* I' r5 k2 m' YRecords: 3 Duplicates: 0 Warnings: 0

mysql> select * from student;
+----+------+
8 L4 `1 z9 d# x| ID | NAME |
+----+------+
" a! g: k  V+ a6 Z0 l9 q| 11 | aa |
, ?, Y. j& e; D5 o2 ]# ^0 m+ Y' t| 13 | bb |
/ j; K7 ~5 Y* C0 w& P/ `- V# ?0 [, M| 16 | cc |
+----+------+
3 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;
5 @. @7 T& O" f2 EQuery OK, 3 rows affected
Records: 3 Duplicates: 0 Warnings: 0

mysql> delete from student where id not in (select minid from temp);
4 M7 |+ Y4 h' ~" a8 fQuery OK, 3 rows affected

mysql> select * from student;
4 n4 G0 I. K: [% n$ I! y+----+------+
| ID | NAME |
+----+------+
| 11 | aa |
8 v/ P- F( ^+ P3 j+ N" }| 13 | bb |
| 16 | cc |
+----+------+
1 O1 {# H! r. D9 o3 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不支持这种方式。
/ p& j1 H7 G- L2 c怎么规避这个问题？
- ]5 O! x4 ^3 }7 ]1 M) J再加一层封装，如下：
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 |
+----+------+
" ~. n( @' u. J$ C6 l" Z) d| 11 | aa |
| 13 | bb |
| 16 | cc |
+----+------+
3 rows in set

8 z. S/ J) j; V' |0 `- v8 A方法三例如：delete from bugdata where id not in (select minid from (select min(id) as minid from bugdata group by qxbh) b);