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

admin

方法1：
, Y0 _. O4 s5 l' \, E1、创建一个临时表，选取需要的数据。
2、清空原表。
; o" F$ @+ f1 s$ b# b, N3、临时表数据导入到原表。
4、删除临时表。
$ O1 c3 E/ S- Y( j/ h" Imysql> select * from student;
+----+------+
- i: W! e- b6 K| ID | NAME |
+----+------+
| 11 | aa |
5 \* u, ^. d  U+ b9 D9 T  h4 u( G1 Y| 12 | aa |
$ l3 L$ b/ _6 F# d7 X  k: ~3 k- u| 13 | bb |
/ R- n( ~7 f$ p" u7 o0 b+ O| 14 | bb |
| 15 | bb |
$ F' I, H. q7 z) a" H3 j6 O% S| 16 | cc |
" `) A4 l$ d! s* _+----+------+
6 rows in set

mysql> create temporary table temp as select min(id),name from student group by name;
2 ^+ |' d) v9 [  Z7 TQuery OK, 3 rows affected
Records: 3 Duplicates: 0 Warnings: 0

mysql> truncate table student;
1 U6 C. L# S0 H( ZQuery OK, 0 rows affected

mysql> insert into student select * from temp;
Query OK, 3 rows affected
* [% o9 K" a. X; A# ]( c  E+ R) jRecords: 3 Duplicates: 0 Warnings: 0

mysql> select * from student;
2 \6 U$ D9 K( f+----+------+
' [( P1 ?, l% {" I) c9 \| ID | NAME |
% {% o0 ]. _7 W/ E* Y% N. @+----+------+
| 11 | aa |
| 13 | bb |
- j7 c$ w- \! L+ `  p4 o+ C0 P* O| 16 | cc |
6 E5 J- u: k2 c  p# z5 p+ v& u2 o, b+----+------+
3 rows in set

mysql> drop temporary table temp;
Query OK, 0 rows affected
  e* D" B% M' |. ~3 C4 W6 U- l7 c这个方法，显然存在效率问题。

---

方法2：按name分组，把最小的id保存到临时表，删除id不在最小id集合的记录，如下：
mysql> create temporary table temp as select min(id) as MINID from student group by name;
5 b" ~. L5 d2 Q9 K# e" qQuery OK, 3 rows affected
Records: 3 Duplicates: 0 Warnings: 0

mysql> delete from student where id not in (select minid from temp);
# e* y8 e8 G8 P5 {Query OK, 3 rows affected

mysql> select * from student;
- m9 p- W3 [* |! t8 H: @; V* b+----+------+
7 \+ r2 O$ g3 || ID | NAME |
% [1 x' C- d: w* w' w8 H+----+------+
; z, Y1 a- t% @| 11 | aa |
| 13 | bb |
| 16 | cc |
+----+------+
* P* u1 V, }: b) _5 i- c3 rows in set

---

方法3：直接在原表上操作，容易想到的sql语句如下：

mysql> delete from student where id not in (select min(id) from student group by name);
3 c9 l7 b( [; m/ Q) s8 d/ o执行报错：1093 - You can't specify target table 'student' for update in FROM clause
原因是：更新数据时使用了查询，而查询的数据又做了更新的条件，mysql不支持这种方式。
怎么规避这个问题？
, c. j8 y! w8 m5 o再加一层封装，如下：
2 H7 Q; N6 f" P6 m( pmysql> delete from student where id not in (select minid from (select min(id) as minid from student group by name) b);
" U/ @- A; o2 y* Q8 q) EQuery OK, 3 rows affected

mysql> select * from student;
+----+------+
| ID | NAME |
+----+------+
| 11 | aa |
' ~- G/ g+ c0 ?! M& ^8 |0 l/ d| 13 | bb |
| 16 | cc |
+----+------+
/ k  R( g. `2 p  V3 rows in set

方法三例如：delete from bugdata where id not in (select minid from (select min(id) as minid from bugdata group by qxbh) b);
# `9 z. K/ K4 c! X0 ?& b- H

+ f# h( A2 K5 ^: w# L