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

admin

方法1：
1、创建一个临时表，选取需要的数据。
2、清空原表。
3、临时表数据导入到原表。
2 C+ h, X1 Y- `0 \4、删除临时表。
mysql> select * from student;
+----+------+
1 @  n3 `1 ]% v. i" t| ID | NAME |
+----+------+
| 11 | aa |
, h6 U: G' n3 F8 b0 \5 T( u9 || 12 | aa |
0 j. |+ u$ R1 u% j0 D0 N: K| 13 | bb |
| 14 | bb |
4 H2 x+ n" u$ F5 \/ t| 15 | bb |
0 b: N  J2 I% q7 q% w" W$ y| 16 | cc |
: D3 v) H9 h9 h: X+----+------+
4 c+ V# y7 b4 f- ?" m& b; e/ C0 ~6 rows in set

mysql> create temporary table temp as select min(id),name from student group by name;
* [; U% c1 U9 H7 z( ~& BQuery OK, 3 rows affected
- l3 Z. u3 y9 C9 {5 ?5 JRecords: 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
9 ~4 ~/ F; x8 h  q, S; {4 I( E9 qRecords: 3 Duplicates: 0 Warnings: 0

mysql> select * from student;
+----+------+
" U7 P+ D1 N/ J1 {+ k' f% C| ID | NAME |
+----+------+
+ e" K5 o: j: C2 E3 P% v| 11 | aa |
7 @1 N8 b* j/ q% }5 h8 j# p| 13 | bb |
, ]$ K$ ~+ m" z0 Z| 16 | cc |
+----+------+
/ d7 j5 |  g/ s/ L/ x0 Q6 `( q7 V3 rows in set

mysql> drop temporary table temp;
# P$ {  b) y% r  a3 SQuery OK, 0 rows affected
( H# X5 H+ a; T; `* r# d- L这个方法，显然存在效率问题。

---

方法2：按name分组，把最小的id保存到临时表，删除id不在最小id集合的记录，如下：
mysql> create temporary table temp as select min(id) as MINID from student group by name;
1 F* Q2 n$ B2 @Query OK, 3 rows affected
7 f, A- |6 P- M0 K' TRecords: 3 Duplicates: 0 Warnings: 0

mysql> delete from student where id not in (select minid from temp);
2 @4 P9 T0 n& W1 zQuery OK, 3 rows affected

mysql> select * from student;
+----+------+
( b4 [& @$ y1 v" l( [| ID | NAME |
+----+------+
| 11 | aa |
| 13 | bb |
$ C! s7 Q; o' \| 16 | cc |
5 q/ h8 l7 V* S3 ]0 l* d% q+----+------+
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
" N8 v$ e3 R$ r1 l( r& @8 Y原因是：更新数据时使用了查询，而查询的数据又做了更新的条件，mysql不支持这种方式。
怎么规避这个问题？
再加一层封装，如下：
% e5 Q8 e) B4 C; h$ E2 `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 |
+----+------+
| 11 | aa |
5 F3 ~: ^& N& \+ R6 C| 13 | bb |
' [; N7 W' q3 X5 M6 v$ h! z$ l4 w0 F| 16 | cc |
+----+------+
3 rows in set

' [* W* l; h+ ?: L4 H$ ~- V方法三例如：delete from bugdata where id not in (select minid from (select min(id) as minid from bugdata group by qxbh) b);