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

admin

方法1：
1、创建一个临时表，选取需要的数据。
) j: D, S: }  X3 x- a# r$ H2 c* n$ Z2、清空原表。
3、临时表数据导入到原表。
4、删除临时表。
8 Y' F% F) I+ i( ~mysql> select * from student;
  R' ^  N3 ^0 ^+----+------+
: D; N0 }- t0 j7 c/ q. K| ID | NAME |
+----+------+
: K0 }, ~: p3 F: \+ k" v| 11 | aa |
| 12 | aa |
/ s  A$ K0 V% c4 T& ?: s| 13 | bb |
| 14 | bb |
| 15 | bb |
% b, |% g1 M  |1 J: C7 ~% R" i; M| 16 | cc |
7 ^3 J5 I6 M9 B2 J; o7 P+----+------+
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;
; j$ h% V' j( f# wQuery OK, 0 rows affected

mysql> insert into student select * from temp;
Query OK, 3 rows affected
$ V1 N6 e3 k8 u8 ^8 u9 a' w- WRecords: 3 Duplicates: 0 Warnings: 0

mysql> select * from student;
+----+------+
| ID | NAME |
+----+------+
0 ~: L9 |3 R. S8 L  S0 i: v| 11 | aa |
/ w: _) Y% o9 q6 ]8 f& |/ G7 {, v| 13 | bb |
3 h( f5 I9 N8 X( r' L| 16 | cc |
; X) J3 \2 j% @! n0 ?8 n* B+----+------+
# d! L+ e# q: u* Y2 [+ E3 rows in set

mysql> drop temporary table temp;
9 f. Y- O0 _; TQuery 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;
, f1 m) n8 @0 L- f! O% Q9 p6 xQuery OK, 3 rows affected
$ r( `) A; R! ^- x' ^1 uRecords: 3 Duplicates: 0 Warnings: 0

mysql> delete from student where id not in (select minid from temp);
7 J3 c- m: d! X8 ]/ B( S. J: d: d0 kQuery OK, 3 rows affected

mysql> select * from student;
# U2 D" Z4 K" ?. V$ r2 j" e6 P+----+------+
| ID | NAME |
+----+------+
| 11 | aa |
( |& W, d) A0 L: b& ^| 13 | bb |
7 o3 p& E5 [: Y  J# k| 16 | cc |
+----+------+
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不支持这种方式。
怎么规避这个问题？
: D* g/ b1 I$ n9 i$ x! ~6 V' l再加一层封装，如下：
mysql> 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;
6 K" R1 Z0 w- E# F4 q+ }+----+------+
| ID | NAME |
+----+------+
| 11 | aa |
7 G1 x8 {% I  ]: |% e2 r| 13 | bb |
| 16 | cc |
+----+------+
# ]2 w+ X, u, B  E7 n/ [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);

/ X) [& y: s- D9 s