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

admin

方法1：
1、创建一个临时表，选取需要的数据。
2、清空原表。
# R% l+ f  j3 B# F: m3、临时表数据导入到原表。
6 F6 `, l" T% E% e3 C4、删除临时表。
mysql> select * from student;
+----+------+
| ID | NAME |
4 F( }- I' Q% N+----+------+
, P) t: G' N2 ^6 h! O8 H9 c| 11 | aa |
0 c6 |& D! E/ B% ?+ W' D| 12 | aa |
| 13 | bb |
| 14 | bb |
| 15 | bb |
| 16 | cc |
4 Z& ?" A( l& b* K, R+----+------+
6 rows in set

mysql> create temporary table temp as select min(id),name from student group by name;
Query OK, 3 rows affected
- }" t- s5 L1 `0 a# }" [, aRecords: 3 Duplicates: 0 Warnings: 0

mysql> truncate table student;
& O6 `: M$ @8 [' Q/ W1 b3 p. y7 WQuery 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;
+ `/ u+ y2 x( ]& b+ a" m' }+----+------+
+ V" D5 i3 [# v; J| ID | NAME |
+----+------+
- M' l6 ^& T# `. J9 E  f& O# d| 11 | aa |
| 13 | bb |
  }* U4 w8 T5 c# }/ ^& t, C. f| 16 | cc |
+----+------+
, D, T* n/ x! g. a3 p* u( M3 rows in set

mysql> drop temporary table temp;
6 k" f- v3 o! F! ^$ DQuery 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;
  o% n# k: U  _% K$ w0 rQuery OK, 3 rows affected
  c5 C7 G+ l4 I% Z1 yRecords: 3 Duplicates: 0 Warnings: 0

mysql> delete from student where id not in (select minid from temp);
7 e2 i) J1 ^- T! @1 ZQuery OK, 3 rows affected

mysql> select * from student;
9 N0 ?* s# A0 o0 n7 U/ _) \% r+----+------+
5 s. U, h* M) A- |1 }/ c4 L% C# {+ B( C| ID | NAME |
+----+------+
| 11 | aa |
9 W4 M3 Y, W' p! k| 13 | bb |
| 16 | cc |
" Z0 G  @4 D+ v  B+----+------+
& \# d  W+ ]3 y1 {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
原因是：更新数据时使用了查询，而查询的数据又做了更新的条件，mysql不支持这种方式。
怎么规避这个问题？
5 M  S7 j$ e; J& |% ]再加一层封装，如下：
mysql> delete from student where id not in (select minid from (select min(id) as minid from student group by name) b);
: d8 N2 Q3 R# |, A' zQuery OK, 3 rows affected

mysql> select * from student;
* ?) R% G9 [8 C* z- X+----+------+
| ID | NAME |
* n6 n0 ~$ I+ G1 Y% }# |+----+------+
| 11 | aa |
7 |  D4 G7 t/ ]. X5 Z| 13 | bb |
| 16 | cc |
; h( L+ c) R# ^; I. C+----+------+
3 rows in set

# K1 `4 x1 `+ M" W7 q方法三例如：delete from bugdata where id not in (select minid from (select min(id) as minid from bugdata group by qxbh) b);