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

admin

方法1：
1、创建一个临时表，选取需要的数据。
2、清空原表。
3、临时表数据导入到原表。
4、删除临时表。
( f' u* P, G- hmysql> select * from student;
: F# [" r, F& p' ?+----+------+
| ID | NAME |
7 s! O- y! K4 H0 f+----+------+
# Y$ A7 M6 _# |+ D) y2 v| 11 | aa |
! }4 O3 N  b' G, |+ ^$ l9 X| 12 | aa |
| 13 | bb |
0 s* F2 ^+ M- a0 A! Z- R3 O| 14 | bb |
7 s4 t2 Q6 W5 n/ V% H/ u| 15 | bb |
| 16 | cc |
0 H* \0 k  e3 J* n8 ?+----+------+
+ u  O5 s# ~, c4 v( ?  H6 rows in set

mysql> create temporary table temp as select min(id),name from student group by name;
Query OK, 3 rows affected
" B9 d8 U3 ]  a& h, E: xRecords: 3 Duplicates: 0 Warnings: 0

mysql> truncate table student;
Query OK, 0 rows affected

mysql> insert into student select * from temp;
7 z" i) `0 I4 B, u$ T/ KQuery OK, 3 rows affected
Records: 3 Duplicates: 0 Warnings: 0

mysql> select * from student;
0 y7 H+ d$ w  V) P) A+----+------+
| ID | NAME |
. W2 Q7 r6 q( A5 t8 s1 i, C) D' u$ x+----+------+
  l1 W6 z/ i2 g$ k; E| 11 | aa |
$ @) ?  [) U3 n7 Q  ]* j$ Y7 d# W| 13 | bb |
$ b0 e- T+ `/ i, V| 16 | cc |
+----+------+
! c; ~' t, f* Y  b: r4 {3 rows in set

mysql> drop temporary table temp;
7 c( s, Y7 \" C% SQuery 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;
5 k0 B! N% r6 |7 E6 l7 n  KQuery OK, 3 rows affected
Records: 3 Duplicates: 0 Warnings: 0

mysql> delete from student where id not in (select minid from temp);
1 a: y$ f5 \3 P8 j3 O7 h+ i9 ]Query OK, 3 rows affected

mysql> select * from student;
+----+------+
| ID | NAME |
+----+------+
  n: \* N# D* {5 s, C) o' K& D| 11 | aa |
* F4 g0 c: k7 K( `% Z0 p| 13 | bb |
| 16 | cc |
5 q2 T$ u0 {$ \# E+----+------+
  R" x& Q2 Y7 v! U. U  C1 j: n$ N3 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不支持这种方式。
怎么规避这个问题？
再加一层封装，如下：
mysql> delete from student where id not in (select minid from (select min(id) as minid from student group by name) b);
: S- F) p: [. W8 fQuery OK, 3 rows affected

mysql> select * from student;
0 z- g- |. @1 F0 M6 D+----+------+
3 H! G) F2 z; V! ~$ L- G7 A0 @| ID | NAME |
! y4 u( Y0 r# C' p- E+----+------+
( x5 K" P! U. v) p* ~| 11 | aa |
| 13 | bb |
' D6 Q* K8 ~6 n+ X2 J- z  i. M9 y| 16 | cc |
+----+------+
/ _. ^5 x! j1 v3 j5 l" i3 rows in set

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

  R* @7 C4 e: }7 i
: E0 o0 |# w3 b! j* b# l  a