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

admin

方法1：
0 o* S6 H2 g8 b9 r0 n1、创建一个临时表，选取需要的数据。
2、清空原表。
3、临时表数据导入到原表。
2 X: J4 l( D* q. z4、删除临时表。
+ r: l' Z6 ?6 X0 u! x% E- s6 Amysql> select * from student;
# `0 J4 u! ~! @" G+----+------+
! O/ q6 K0 o- f* C( j" G2 X$ k| ID | NAME |
+----+------+
| 11 | aa |
3 M( v5 |% V- k| 12 | aa |
| 13 | bb |
| 14 | bb |
2 ?4 @/ Z; m- E, M9 M/ P| 15 | bb |
| 16 | cc |
5 B9 v& j. a- o- N5 c: H+----+------+
+ d# U- E4 n- @$ U0 M3 A  n6 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;
$ S5 H3 L+ u$ [0 hQuery OK, 0 rows affected

mysql> insert into student select * from temp;
$ i6 }. F4 P+ Q# ^  e7 z7 M& PQuery OK, 3 rows affected
Records: 3 Duplicates: 0 Warnings: 0

mysql> select * from student;
2 \. h: F- f+ \% ~9 u+----+------+
7 \7 h! P% l. F+ y| ID | NAME |
$ ^: ~1 P7 ~% }/ p: n/ Q9 y1 b+----+------+
: T" Y( j8 V5 K4 |6 K| 11 | aa |
| 13 | bb |
6 ~4 K' O) K. h( N+ p3 N4 v| 16 | cc |
1 M  O( o( N  f2 X1 h/ k+----+------+
& u& x2 E7 Q9 B/ G3 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;
* L3 E1 ?3 I0 Z# `* kQuery OK, 3 rows affected
Records: 3 Duplicates: 0 Warnings: 0

mysql> delete from student where id not in (select minid from temp);
+ g0 p( P! I) L) s) q. @3 YQuery OK, 3 rows affected

mysql> select * from student;
3 \% D+ {% T5 m3 H$ Z+----+------+
/ X$ E: ~/ t  @$ R) y7 d, @; d" G% i2 W3 n| ID | NAME |
+----+------+
+ i$ V' f# X4 L$ D| 11 | aa |
| 13 | bb |
| 16 | cc |
+----+------+
3 rows in set

---

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

mysql> delete from student where id not in (select min(id) from student group by name);
) i+ |# ]. A' _9 Y8 q执行报错：1093 - You can't specify target table 'student' for update in FROM clause
原因是：更新数据时使用了查询，而查询的数据又做了更新的条件，mysql不支持这种方式。
2 A! r. O, C; I6 }+ I9 }# p# \怎么规避这个问题？
再加一层封装，如下：
; g. F! |0 M8 ^1 L; J' amysql> delete from student where id not in (select minid from (select min(id) as minid from student group by name) b);
' I; a8 N* A2 aQuery OK, 3 rows affected

mysql> select * from student;
* a7 L  ^" P" b% u' b+ k+----+------+
- F) R. m, s! M3 I| ID | NAME |
/ n/ G1 M# f" v+----+------+
2 c) D" c9 t  ?0 v+ V2 p| 11 | aa |
| 13 | bb |
! e: m5 `' d5 F' k/ f1 m| 16 | cc |
+----+------+
; u% q' J4 G0 m& s0 x0 A3 rows in set

& N/ E1 `6 K0 w3 Y0 L, O4 T! d方法三例如：delete from bugdata where id not in (select minid from (select min(id) as minid from bugdata group by qxbh) b);
, @+ T% c7 Z9 @* Y7 m