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

admin

方法1：
$ s" o7 g) d1 b% s3 c7 @1、创建一个临时表，选取需要的数据。
( `5 @4 b5 V. I6 `" v2、清空原表。
* f$ V( F) S* Z- Z  m6 k+ F3 P9 ]7 e3、临时表数据导入到原表。
( T) i* {0 {- D. B3 S9 z  f4、删除临时表。
& Q3 y$ D/ D* e# y+ A1 ^mysql> select * from student;
+----+------+
0 z# D; g* q6 _0 Z| ID | NAME |
+----+------+
6 V$ }0 [9 P1 ^  q5 G/ F| 11 | aa |
$ G# G3 O* G& }2 u9 V| 12 | aa |
| 13 | bb |
| 14 | bb |
0 ?: L! v0 c; x' @! G" D2 d| 15 | bb |
. @+ x( Z" f) p! ^* q3 U* F" s% N% K| 16 | cc |
+----+------+
6 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;
7 r: I" a) L  CQuery OK, 3 rows affected
2 r5 @# K* ]6 t! LRecords: 3 Duplicates: 0 Warnings: 0

mysql> select * from student;
+----+------+
% e  {- v* g: c, Q| ID | NAME |
4 ^9 s- h' G9 l. F/ S* S( G( P+----+------+
| 11 | aa |
5 `( V$ i3 n, c' p9 Q: C  r( L  k| 13 | bb |
| 16 | cc |
" e6 I9 e1 ~$ f/ U' b% n0 o, b+----+------+
3 rows in set

mysql> drop temporary table temp;
7 f5 y" H) H7 a+ g/ m9 @Query OK, 0 rows affected
$ P2 N- c% F, H/ `这个方法，显然存在效率问题。

---

方法2：按name分组，把最小的id保存到临时表，删除id不在最小id集合的记录，如下：
* E" R" _  j' d# U% a9 omysql> 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 |
* `( C  P1 n2 `" ^4 V0 g+----+------+
  ]) b% m' X6 v# D! x  a| 11 | aa |
| 13 | bb |
1 A5 U3 V7 @; M; O  C| 16 | cc |
1 y# X+ i. ~% T+ n5 m7 Y+----+------+
* w3 D! ?  u( `% T$ F3 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
. c8 \4 _$ @/ \, ?2 n6 S原因是：更新数据时使用了查询，而查询的数据又做了更新的条件，mysql不支持这种方式。
! x; c2 X: |0 H. h怎么规避这个问题？
! I3 y# L* A) ?) L再加一层封装，如下：
/ z: R  R/ w+ y9 z& m4 y$ Gmysql> 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 |
, |& H6 H) {9 x" I7 G  r/ k# h+----+------+
| 11 | aa |
, K% |7 J2 L8 [8 }/ F. n- R| 13 | bb |
- G3 D- D' W5 b$ W& ~5 n| 16 | cc |
+----+------+
# A7 X; T1 r1 z* }% m3 rows in set

方法三例如：delete from bugdata where id not in (select minid from (select min(id) as minid from bugdata group by qxbh) b);

: C* X# @' a* A3 g7 c3 l