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

admin

方法1：
9 L- m. N) Q  r5 {7 @1、创建一个临时表，选取需要的数据。
. ~* C: s; u4 }7 b- ?3 Z/ U4 `4 H2、清空原表。
8 e. ?% v  F2 k5 ]9 H3、临时表数据导入到原表。
4、删除临时表。
  b* s* k2 o# G7 j3 V9 s, O. D# A; K  K% Jmysql> select * from student;
+----+------+
| ID | NAME |
7 b( M2 L9 E, e, R+ ^2 W+----+------+
| 11 | aa |
9 b7 V& j! A( x4 _% v( _# [5 V9 z| 12 | aa |
# o0 g  Q" T  U| 13 | bb |
# J8 U! g' ^  M$ ^| 14 | bb |
| 15 | bb |
| 16 | cc |
5 q. |, ^; T; D4 D% d8 H$ K+----+------+
5 i, c2 [9 y1 \# Z  i6 rows in set

mysql> create temporary table temp as select min(id),name from student group by name;
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
Records: 3 Duplicates: 0 Warnings: 0

mysql> select * from student;
+----+------+
| ID | NAME |
+----+------+
| 11 | aa |
# S% [/ Q1 _) V0 b2 G$ l% k| 13 | bb |
  a, p% n7 {9 t, i& I, }( z, U| 16 | cc |
, z3 x# A: Y1 K3 R7 _+----+------+
3 rows in set

mysql> drop temporary table temp;
Query OK, 0 rows affected
1 o' N5 q1 g" m& Z这个方法，显然存在效率问题。

---

方法2：按name分组，把最小的id保存到临时表，删除id不在最小id集合的记录，如下：
& ]! J7 r6 s, p+ v7 Dmysql> 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);
Query OK, 3 rows affected

mysql> select * from student;
+----+------+
| ID | NAME |
+----+------+
; a* P& D# `  y7 ^  F( }5 V+ n; @| 11 | aa |
| 13 | bb |
+ p( G, N; R" R- p0 |; A6 y| 16 | cc |
+----+------+
3 rows in set

---

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

mysql> delete from student where id not in (select min(id) from student group by name);
" F" \" U( b. {1 W执行报错：1093 - You can't specify target table 'student' for update in FROM clause
原因是：更新数据时使用了查询，而查询的数据又做了更新的条件，mysql不支持这种方式。
0 b% y  B  n0 k5 b怎么规避这个问题？
* Y+ [7 ?) x- f6 G再加一层封装，如下：
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;
: q3 p8 q/ r% s: g) C' _( a+----+------+
. [. I" U8 U5 [( Y, K| ID | NAME |
+----+------+
| 11 | aa |
| 13 | bb |
$ w; u, q/ S' F" c  E; e| 16 | cc |
+----+------+
; l$ M1 [% W4 B( A+ 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);
8 @0 G/ z$ o) ^: E% z

. C/ q* r$ s' E; \9 q1 s- I4 o% a