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

admin

方法1：
" x+ x& G2 w5 H% W4 f/ \$ E7 g1、创建一个临时表，选取需要的数据。
2、清空原表。
2 M. Q" u1 D  n& n/ V: ]3、临时表数据导入到原表。
/ @; b" m. {1 D! l6 f% F3 `8 U4、删除临时表。
2 [4 ?1 E! o1 {mysql> select * from student;
+----+------+
5 v+ ~& `2 @& g| ID | NAME |
# J, f% K8 K- X+----+------+
| 11 | aa |
| 12 | aa |
6 y  f! R4 o8 p+ x% u& t# ?| 13 | bb |
9 `" `/ n' H- Y- E) u( e: v# A' B| 14 | bb |
| 15 | bb |
! Y$ ?8 }4 K9 N+ g) Z, a" I5 g* r: H| 16 | cc |
+----+------+
% N9 n! W, d: G9 ~6 rows in set

mysql> create temporary table temp as select min(id),name from student group by name;
Query OK, 3 rows affected
, q3 |0 w( ?# G1 c- f8 nRecords: 3 Duplicates: 0 Warnings: 0

mysql> truncate table student;
" T2 D/ T$ a/ `9 p0 lQuery OK, 0 rows affected

mysql> insert into student select * from temp;
Query OK, 3 rows affected
* U; a, A- j. O: s% |# MRecords: 3 Duplicates: 0 Warnings: 0

mysql> select * from student;
  j7 _3 y" c7 i+ I3 H) e; t+----+------+
2 L4 v6 c# w- _; w$ x2 {| ID | NAME |
+----+------+
# V2 F8 e# b; y0 w6 o0 }- I- r) D| 11 | aa |
( L7 ]: h' s1 d9 Y) o/ l| 13 | bb |
/ k+ ?, V) A1 z# t/ b3 N| 16 | cc |
- b4 r7 W) ~6 ?. W; V' `+----+------+
0 L4 H: g& M8 w- x7 O2 ]4 D9 ^3 rows in set

mysql> drop temporary table temp;
: |  \% k& x* @1 y& L7 B9 H) TQuery OK, 0 rows affected
, K8 y, {& s$ K. W这个方法，显然存在效率问题。

---

方法2：按name分组，把最小的id保存到临时表，删除id不在最小id集合的记录，如下：
mysql> create temporary table temp as select min(id) as MINID from student group by name;
8 h: M0 P+ c- HQuery OK, 3 rows affected
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;
+----+------+
6 K! l1 F  E; A: V+ O) Z: p| ID | NAME |
+----+------+
% k$ B4 O4 x" Z) Y1 Y: ]  U6 l. o| 11 | aa |
- `: s6 Q$ L1 C& V+ d| 13 | bb |
| 16 | cc |
. o0 R2 y9 Q! u+----+------+
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
: E+ H1 P4 r5 t/ F2 c4 b原因是：更新数据时使用了查询，而查询的数据又做了更新的条件，mysql不支持这种方式。
怎么规避这个问题？
再加一层封装，如下：
. H8 d) D7 Z6 Mmysql> 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;
+----+------+
, H/ }0 M/ H+ X2 {8 E| ID | NAME |
+----+------+
| 11 | aa |
! K( W8 g0 q1 {% D  c: X* u' c* ~| 13 | bb |
| 16 | cc |
  B, L2 s$ P. }2 w; V+----+------+
) b: {* [4 i% M& `3 rows in set

方法三例如：delete from bugdata where id not in (select minid from (select min(id) as minid from bugdata group by qxbh) b);
6 c/ ^$ _% ]1 j2 C) f  @5 V

) r1 F2 F$ c) t. g2 X) W% B5 o