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

admin

方法1：
  `( ^$ d9 L; v- G: a- k1、创建一个临时表，选取需要的数据。
2、清空原表。
! w% e$ y! @" \3、临时表数据导入到原表。
4 Q9 v9 u% }$ q% s4 n, \4、删除临时表。
6 S/ ]$ ~5 ?- b7 a+ cmysql> select * from student;
+----+------+
! _+ K0 X7 r8 G) B0 r+ y| ID | NAME |
+----+------+
| 11 | aa |
| 12 | aa |
| 13 | bb |
6 I6 z8 w8 Y& d9 k& q$ r| 14 | bb |
| 15 | bb |
| 16 | cc |
; }0 \; ?9 O1 O' K' @- {+----+------+
6 rows in set

mysql> create temporary table temp as select min(id),name from student group by name;
8 m  l% `1 x! G) YQuery OK, 3 rows affected
Records: 3 Duplicates: 0 Warnings: 0

mysql> truncate table student;
2 X/ l9 B: ?7 Y0 gQuery 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;
+----+------+
8 h7 `) U  d5 S3 \+ N; W| ID | NAME |
: {2 o# S' s& Q" L# F+----+------+
| 11 | aa |
: B( H- x  l# d- s( P9 }| 13 | bb |
| 16 | cc |
1 R& V/ _% }; i9 M0 i) ?+ |+----+------+
3 rows in set

mysql> drop temporary table temp;
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;
" z% e" t/ T: ?" a! ^Query OK, 3 rows affected
Records: 3 Duplicates: 0 Warnings: 0

mysql> delete from student where id not in (select minid from temp);
2 E" V+ n! d: \; X0 Q8 [Query OK, 3 rows affected

mysql> select * from student;
+----+------+
; M7 o  K7 `! b+ Q, M/ ~, r' S  M' T; P| ID | NAME |
+----+------+
" h" |& m& ^7 S, \" \| 11 | aa |
& Z& s" Y- Y7 {) K6 g# L| 13 | bb |
| 16 | cc |
7 C5 N' Y! x+ ]; H+ j7 ?: c+----+------+
( s! O( S- x) z& U+ j% w3 rows in set

---

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

mysql> delete from student where id not in (select min(id) from student group by name);
% c3 n% u9 j' R' U' ^8 \' |执行报错：1093 - You can't specify target table 'student' for update in FROM clause
& v! h& K9 p3 l4 Q$ d) f1 \原因是：更新数据时使用了查询，而查询的数据又做了更新的条件，mysql不支持这种方式。
怎么规避这个问题？
4 Y' U  F0 `% V, e9 r, G再加一层封装，如下：
# L* T* N; M% E4 v; n) Cmysql> 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;
+----+------+
9 `3 p/ Q% y+ d5 B) T7 X* t1 t7 c| ID | NAME |
+----+------+
, l. o4 W" K% `1 ~| 11 | aa |
| 13 | bb |
; ?+ K* C' A1 E0 T5 N| 16 | cc |
+----+------+
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);