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

admin

方法1：
1、创建一个临时表，选取需要的数据。
2、清空原表。
3、临时表数据导入到原表。
4、删除临时表。
mysql> select * from student;
+----+------+
| ID | NAME |
+----+------+
3 i9 y6 B3 c) V4 m# {| 11 | aa |
, f- q( {- u, B1 p8 {| 12 | aa |
2 u" M2 _3 x7 W3 q. n/ P' d| 13 | bb |
9 `2 T" N2 n3 u, r| 14 | bb |
' ^; g9 ]: j  O1 d% ~| 15 | bb |
: L+ r8 j" ^; G6 X| 16 | cc |
+----+------+
9 S) L; o( Z5 [' f6 rows in set

mysql> create temporary table temp as select min(id),name from student group by name;
* _4 j4 H$ v% l7 V) L: I/ u5 ~Query OK, 3 rows affected
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
8 U# i8 a! [7 m1 a2 w: ?9 Z/ hRecords: 3 Duplicates: 0 Warnings: 0

mysql> select * from student;
" y* B9 w5 J6 w( D" y+----+------+
$ g( d0 w& \1 x  l| ID | NAME |
+----+------+
6 O: e: E! c' J. B2 _7 }( L| 11 | aa |
* D. b, u- ]$ {% P; v) s7 I9 [. w1 q| 13 | bb |
1 }# e: T- @. ^0 ]# p| 16 | cc |
1 |6 e! i/ W& x' {* N) t+----+------+
$ P+ a( @, T" _: g" f* E3 rows in set

mysql> drop temporary table temp;
2 i) B' W& ]  J: H2 N- v+ i; \Query OK, 0 rows affected
这个方法，显然存在效率问题。

---

方法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
Records: 3 Duplicates: 0 Warnings: 0

mysql> delete from student where id not in (select minid from temp);
. [- D; ?) W# _0 W$ N% W/ O) JQuery OK, 3 rows affected

mysql> select * from student;
+----+------+
: \9 g" X3 A& I9 H| ID | NAME |
+----+------+
% y$ q" ?7 b( P# o/ j* b$ t6 L| 11 | aa |
1 b& X" Y! J# D| 13 | bb |
| 16 | cc |
+----+------+
8 Q1 g+ h) S, u. S7 r3 rows in set

---

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

mysql> delete from student where id not in (select min(id) from student group by name);
4 ^1 n, U* O+ |; q2 l& Y+ d: Z5 W0 D执行报错：1093 - You can't specify target table 'student' for update in FROM clause
- q. Y- h1 T7 p) ]' }  b原因是：更新数据时使用了查询，而查询的数据又做了更新的条件，mysql不支持这种方式。
; c" {& L0 a. y/ O5 ^8 Z3 W怎么规避这个问题？
再加一层封装，如下：
  J: d3 O: n' a: _- t, D8 u- }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;
+----+------+
; S3 _1 A9 i/ Q) G| ID | NAME |
: t' E  n* d3 i6 d( ~4 C+----+------+
( S! E6 Y$ ]& {$ H| 11 | aa |
1 O- L: p6 I; B' M& i0 z| 13 | bb |
| 16 | cc |
' }5 K0 Q0 C9 Z+----+------+
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);
* a3 a( a  C8 J( o