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

admin

方法1：
1、创建一个临时表，选取需要的数据。
2、清空原表。
/ y) {' U8 b. M+ ?6 f3 J& }3、临时表数据导入到原表。
4、删除临时表。
mysql> select * from student;
' u, v. z! n# O+ S+----+------+
$ E) j5 N" l- w! t| ID | NAME |
6 M- }6 d  l0 n4 J7 H$ C+----+------+
' W7 V5 d6 k. O0 {9 {& I+ \| 11 | aa |
| 12 | aa |
| 13 | bb |
| 14 | bb |
| 15 | bb |
/ F  {# b( [7 Z' _2 f: t3 F0 v) |) I| 16 | cc |
+----+------+
* ^+ J1 l/ H: h6 {6 rows in set

mysql> create temporary table temp as select min(id),name from student group by name;
0 V; z# n7 k5 Y7 |, n% L0 a8 wQuery OK, 3 rows affected
Records: 3 Duplicates: 0 Warnings: 0

mysql> truncate table student;
# }! v& P+ R8 V/ b8 ^1 sQuery OK, 0 rows affected

mysql> insert into student select * from temp;
Query OK, 3 rows affected
5 X7 b  w  k. O# Y! N( LRecords: 3 Duplicates: 0 Warnings: 0

mysql> select * from student;
) R8 V. T% u' [$ [+----+------+
| ID | NAME |
+----+------+
3 P( g$ L2 \/ W8 Z| 11 | aa |
| 13 | bb |
| 16 | cc |
( O" f" U2 s' j4 Z6 C' @. q+----+------+
! h& L. Q& r. w3 rows in set

mysql> drop temporary table temp;
Query OK, 0 rows affected
* ], P) p! b, y8 ]5 j7 @, @这个方法，显然存在效率问题。

---

方法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);
' H0 ]6 T& r5 C3 p0 ]7 tQuery OK, 3 rows affected

mysql> select * from student;
+----+------+
| ID | NAME |
+----+------+
5 W6 l4 q" R9 y+ G1 |  Y6 ]| 11 | aa |
1 V$ F9 L8 h) n4 P3 ~" g! O| 13 | bb |
| 16 | cc |
, k( l2 {5 A1 g2 Q' t* y2 x+----+------+
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
原因是：更新数据时使用了查询，而查询的数据又做了更新的条件，mysql不支持这种方式。
怎么规避这个问题？
/ H! Y6 ?8 U0 ]+ b$ p8 F9 r再加一层封装，如下：
mysql> delete from student where id not in (select minid from (select min(id) as minid from student group by name) b);
; j* d- ]" Y% Q, I9 E/ C6 KQuery OK, 3 rows affected

mysql> select * from student;
9 B' [5 @2 D0 U; b' v: I- V+----+------+
- j$ i/ E0 z: l, L# `( P| ID | NAME |
" e) O5 O+ U  |' T2 ?0 f9 M8 ?+----+------+
| 11 | aa |
8 |- {2 y1 L) {  `1 j" M% h& p9 i2 L. d| 13 | bb |
2 A" W, ~! L7 L7 D| 16 | cc |
# L) g2 h" ]! T/ u+----+------+
$ q# W- I. v( V- Z$ J: @* ?' `# B6 O3 rows in set

3 [/ Q4 v+ @# w方法三例如：delete from bugdata where id not in (select minid from (select min(id) as minid from bugdata group by qxbh) b);
* j$ v7 f' N/ @, s! _; }5 m

. x3 a- @3 P) Q5 t0 t# A8 m1 F