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

admin

方法1：
1、创建一个临时表，选取需要的数据。
& n% m" Q; Q. ~; c2、清空原表。
3、临时表数据导入到原表。
4、删除临时表。
; t2 X& |8 k  Zmysql> select * from student;
0 e) ^% q% K: @+----+------+
3 R- }7 i+ t& U) v| ID | NAME |
  }- c$ E+ N7 ?7 p/ z7 j( B" q/ k+----+------+
8 x0 C, Y6 u9 W| 11 | aa |
, B# V& r& a+ f) ?8 _6 r% }- t/ P1 f| 12 | aa |
6 |4 Z: m2 m% p4 R5 ?| 13 | bb |
: n9 @2 v" h! n" I3 h! X7 f| 14 | bb |
. ]0 |, _9 a) r  Z- Q5 O8 }* v| 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
' I. C/ P* B: `0 G3 Z5 v! `Records: 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
Records: 3 Duplicates: 0 Warnings: 0

mysql> select * from student;
+----+------+
| ID | NAME |
& V. O9 v% f& u) M& G+----+------+
: M4 b, D- H2 c+ w9 h% g| 11 | aa |
| 13 | bb |
& Q2 M+ H: v+ `2 w: O' Z. f# p0 e| 16 | cc |
  T/ n+ J  V; Z. M1 |3 p: o) W+----+------+
2 a6 L$ R$ l. H5 `3 rows in set

mysql> drop temporary table temp;
  m+ t5 v) @9 M6 P* |( D- R( rQuery OK, 0 rows affected
& W- `6 C0 X- K% t. h* Q) c0 ~5 S这个方法，显然存在效率问题。

---

方法2：按name分组，把最小的id保存到临时表，删除id不在最小id集合的记录，如下：
mysql> create temporary table temp as select min(id) as MINID from student group by name;
, I0 M9 [7 K, Z6 m% O$ wQuery OK, 3 rows affected
Records: 3 Duplicates: 0 Warnings: 0

mysql> delete from student where id not in (select minid from temp);
% @9 T6 u: R7 a8 YQuery OK, 3 rows affected

mysql> select * from student;
+----+------+
5 r$ y  I- G' |9 L. ^  [| ID | NAME |
7 M1 ^9 a$ {/ G) Q+----+------+
: h4 P. z" u3 |- r" w+ v| 11 | aa |
$ `2 r  ~+ c+ y| 13 | bb |
| 16 | cc |
# G& O2 m- s7 C* H/ n  d+----+------+
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
+ b2 A/ n; |9 b原因是：更新数据时使用了查询，而查询的数据又做了更新的条件，mysql不支持这种方式。
怎么规避这个问题？
4 `5 e4 ^, l  N2 M! E' O4 A再加一层封装，如下：
' {0 V" P4 _# ymysql> 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;
/ e# }& b, w: C/ B+----+------+
| ID | NAME |
+----+------+
| 11 | aa |
9 x6 z3 t; c7 L$ Y| 13 | bb |
| 16 | cc |
; y( |1 K3 S, _$ B+----+------+
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);
0 i: X! S0 b  f0 ~: j% ^* f1 _, G

3 z# ]: a4 m" P7 }* `6 ~6 H4 w6 z