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

admin

方法1：
7 F8 G" J3 f4 t- A# j1、创建一个临时表，选取需要的数据。
( x7 [4 {; @2 N' S2 z2、清空原表。
3、临时表数据导入到原表。
" B& A) y8 G/ X. A3 s* v4、删除临时表。
mysql> select * from student;
+----+------+
% g6 C+ Z$ Y0 l6 A| ID | NAME |
4 V# Z0 Y4 b. ]+ F4 }7 Y9 V+----+------+
5 @9 w1 n: w/ q. q! q" i$ R6 Q8 \9 m3 U| 11 | aa |
7 w( `0 `7 ]$ g' c3 A' v| 12 | aa |
| 13 | bb |
% {0 X5 M. N8 T4 D| 14 | bb |
1 B& t3 F" Q( }4 |5 i' F/ Q| 15 | bb |
| 16 | cc |
$ n+ _8 w) u: M* B7 k+ {8 S+----+------+
6 rows in set

mysql> create temporary table temp as select min(id),name from student group by name;
Query OK, 3 rows affected
3 l" z3 E; I4 n3 f/ n, T% FRecords: 3 Duplicates: 0 Warnings: 0

mysql> truncate table student;
/ ^$ K6 O) O: v- |% D! ?% gQuery OK, 0 rows affected

mysql> insert into student select * from temp;
  \, G) b& @, O7 I% J" ]Query OK, 3 rows affected
Records: 3 Duplicates: 0 Warnings: 0

mysql> select * from student;
- l5 X+ R/ _1 o) E+ t% l3 e+----+------+
6 r; G9 v- @& t& ?| ID | NAME |
+----+------+
& z) {. T1 x) x- z| 11 | aa |
( M+ I* ?: t- O5 Y" @- }! }! l| 13 | bb |
- m2 V/ C* e9 ^$ ?3 q7 {| 16 | cc |
" y, F/ `& |  `7 D+----+------+
' k- J+ A0 ~# h3 rows in set

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

---

方法2：按name分组，把最小的id保存到临时表，删除id不在最小id集合的记录，如下：
7 b3 [3 w' T! n- W. q1 A! c& S- U7 qmysql> create temporary table temp as select min(id) as MINID from student group by name;
( R1 n, M- h5 {# _- `; ~+ v% k1 dQuery OK, 3 rows affected
  \+ Z& a3 [6 o, l1 @Records: 3 Duplicates: 0 Warnings: 0

mysql> delete from student where id not in (select minid from temp);
Query OK, 3 rows affected

mysql> select * from student;
4 n+ s  w' \, `* B- t5 U+----+------+
9 y/ J2 H# I- P- \0 t3 M| ID | NAME |
+----+------+
| 11 | aa |
| 13 | bb |
. ?, r# I- }5 o1 E  \| 16 | cc |
' [- I5 {4 q; V8 X+----+------+
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
$ |0 O' P# |- N6 O原因是：更新数据时使用了查询，而查询的数据又做了更新的条件，mysql不支持这种方式。
怎么规避这个问题？
再加一层封装，如下：
/ _* c8 x( M5 Z" F) A1 t3 n, @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 |
5 w( Z2 B$ ?+ n3 d, ]0 W) `" C* c2 I& M! |+----+------+
2 L4 @# B7 E8 ]; ^0 I1 H! z| 11 | aa |
4 n! ]! ?9 {$ \) H8 l- W| 13 | bb |
& w' m* j- e0 y' l( w) U5 C| 16 | cc |
+----+------+
4 O3 U  W5 Y* J' Z3 rows in set

3 i2 c5 G3 N7 o# D" N方法三例如：delete from bugdata where id not in (select minid from (select min(id) as minid from bugdata group by qxbh) b);
" {" o8 B% a& u: ^
- u. @1 ?- E0 |$ X
: A2 s2 W+ U$ s1 Z+ P