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

admin

方法1：
1、创建一个临时表，选取需要的数据。
2、清空原表。
+ f5 ~! b7 @' P9 I3、临时表数据导入到原表。
+ l& w7 ?& i% J& ?4、删除临时表。
mysql> select * from student;
+----+------+
| ID | NAME |
+----+------+
+ S* ~" T  W& c2 v| 11 | aa |
- l- @, o' j! J- b, Z1 c| 12 | aa |
0 o3 P# r: `4 v& G& L| 13 | bb |
; j- i# V- l. a! t| 14 | bb |
1 ^/ O! G8 R; _, {* T# x| 15 | bb |
( F4 X& i7 u. ]1 @( W| 16 | cc |
, ]( }/ V/ X# ?/ C* C, v1 k+----+------+
6 rows in set

mysql> create temporary table temp as select min(id),name from student group by name;
' e# M4 _. m2 M* t0 e0 vQuery 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;
7 [% K. o. J! ~5 v6 TQuery OK, 3 rows affected
% l+ ], V0 ?& P# v) C8 r& ^+ nRecords: 3 Duplicates: 0 Warnings: 0

mysql> select * from student;
9 {% u9 q! L" W' {+ v+----+------+
| ID | NAME |
' h; Z% y, b0 u( w: Y/ U# H+----+------+
; h! h6 @( T! Y* i- e| 11 | aa |
6 _# Y+ R! `% u$ N" }3 U' w| 13 | bb |
| 16 | cc |
+----+------+
3 rows in set

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

---

方法2：按name分组，把最小的id保存到临时表，删除id不在最小id集合的记录，如下：
0 W" G1 g3 x. f$ B( rmysql> create temporary table temp as select min(id) as MINID from student group by name;
1 |! G, d! f& ?Query 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 ~: _% D1 f: Q( K; P$ V| ID | NAME |
) j8 b) k, m4 r  |+----+------+
| 11 | aa |
| 13 | bb |
/ a7 f% D; v: z3 F4 || 16 | cc |
+----+------+
3 rows in set

---

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

mysql> delete from student where id not in (select min(id) from student group by name);
* \. y* z7 m  {% z执行报错：1093 - You can't specify target table 'student' for update in FROM clause
原因是：更新数据时使用了查询，而查询的数据又做了更新的条件，mysql不支持这种方式。
8 p4 ^( @$ x$ X5 c! y1 g怎么规避这个问题？
再加一层封装，如下：
9 q: [1 f) p( B, A7 fmysql> 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 |
+----+------+
# B- d) s* F1 J7 ^9 D0 e( C5 l| 11 | aa |
| 13 | bb |
( U. T+ {& j  P9 w| 16 | cc |
+----+------+
# e+ y& H" |+ n# J+ M! B" w3 rows in set

方法三例如：delete from bugdata where id not in (select minid from (select min(id) as minid from bugdata group by qxbh) b);
! S6 `8 ~9 O, x% d
" k. ]7 A1 H/ ^/ D4 i  o