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

admin

方法1：
1、创建一个临时表，选取需要的数据。
2、清空原表。
0 P2 U  n; a3 K; _- T3、临时表数据导入到原表。
7 k; Z5 ^1 J4 c. z' J$ p4、删除临时表。
9 |; X/ ^% N/ d6 M5 y( v1 Fmysql> select * from student;
( f$ [/ F: p) f; e3 o2 Z3 f$ U+----+------+
| ID | NAME |
+----+------+
8 s) v+ [2 p+ H; t6 {/ e| 11 | aa |
* r+ L# `& P) E& X! }| 12 | aa |
| 13 | bb |
| 14 | bb |
| 15 | bb |
| 16 | cc |
+----+------+
6 rows in set

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

mysql> truncate table student;
* E7 n8 ~2 {! u" |( G2 pQuery 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 |
+----+------+
| 11 | aa |
( s& X3 z1 p# p$ S6 `| 13 | bb |
- j8 q$ l9 E/ w" ^| 16 | cc |
& [: x! r" _8 `$ f: D# M' L$ G- B+----+------+
# H$ d! l0 O) u# E9 \- k1 A! V3 rows in set

mysql> drop temporary table temp;
Query OK, 0 rows affected
8 a6 Y  j3 ]% L7 i- f# K这个方法，显然存在效率问题。

---

方法2：按name分组，把最小的id保存到临时表，删除id不在最小id集合的记录，如下：
mysql> create temporary table temp as select min(id) as MINID from student group by name;
Query OK, 3 rows affected
# [& k3 N3 I7 t1 }+ `Records: 3 Duplicates: 0 Warnings: 0

mysql> delete from student where id not in (select minid from temp);
* u+ q- f( N# A! f5 n6 FQuery OK, 3 rows affected

mysql> select * from student;
" [* x9 }. B, R9 N5 X% P2 e% s6 G+----+------+
; D, x5 |) d' a: r* c5 Z| ID | NAME |
, I2 d) Z' H/ }4 A$ W7 g& _+----+------+
* Q: f3 y5 _6 x$ A| 11 | aa |
| 13 | bb |
| 16 | cc |
+----+------+
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
& `. J5 u  R. D# d" B' [- o原因是：更新数据时使用了查询，而查询的数据又做了更新的条件，mysql不支持这种方式。
怎么规避这个问题？
/ O$ J1 b2 G' K1 F& ~  u# U* l# X再加一层封装，如下：
% B+ R5 q6 [0 C+ {6 x/ `- Smysql> 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;
% F% X) f2 A3 t: S+----+------+
% w* c. v; ]0 J| ID | NAME |
' ]( ?" i! J4 ?: `+----+------+
8 @: C5 g0 h9 P6 Z& w, q| 11 | aa |
| 13 | bb |
| 16 | cc |
9 ?" k. |9 R( g- ?+----+------+
3 H6 J  R- K/ z& b0 _3 rows in set

) |2 J3 B9 T& D$ C* }3 N) r9 Z6 I方法三例如：delete from bugdata where id not in (select minid from (select min(id) as minid from bugdata group by qxbh) b);
) ~" V/ J2 z/ b- W! j; L% V
& B: g1 u8 H. y  q) z
4 C% Z- h( S5 ?1 X% S: Z