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

admin

方法1：
1、创建一个临时表，选取需要的数据。
2、清空原表。
3、临时表数据导入到原表。
, {  _# I3 b) S0 ~4、删除临时表。
mysql> select * from student;
+----+------+
, o3 C6 F: T) J| ID | NAME |
* [7 F' P; t6 n+ T) I, I& @2 ~+----+------+
| 11 | aa |
; x6 [0 g# _9 K  V/ f; p2 H| 12 | aa |
- y8 N* N! L2 j1 M# }. f/ x| 13 | bb |
; S8 m; x% r$ l2 v0 j4 }| 14 | bb |
| 15 | bb |
| 16 | cc |
. J' }6 i$ f. K- M9 [. P" {- x+----+------+
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;
Query OK, 3 rows affected
Records: 3 Duplicates: 0 Warnings: 0

mysql> select * from student;
! F! x7 E3 k9 U+----+------+
| ID | NAME |
5 X9 e9 E4 H4 h1 t+----+------+
| 11 | aa |
| 13 | bb |
| 16 | cc |
! F4 U1 F( Y- g% g7 L+----+------+
3 rows in set

mysql> drop temporary table temp;
Query OK, 0 rows affected
这个方法，显然存在效率问题。

---

方法2：按name分组，把最小的id保存到临时表，删除id不在最小id集合的记录，如下：
1 e: z# ]4 \+ D9 Y3 ~* ]" C3 W+ D2 Bmysql> create temporary table temp as select min(id) as MINID from student group by name;
: X: @" Z6 }. C: aQuery OK, 3 rows affected
Records: 3 Duplicates: 0 Warnings: 0

mysql> delete from student where id not in (select minid from temp);
- j9 E2 r6 h5 GQuery OK, 3 rows affected

mysql> select * from student;
0 H* ^! A0 V7 k5 Z+----+------+
| ID | NAME |
5 }% W$ X! e. }6 Q4 Y. F) Y5 J+----+------+
| 11 | aa |
| 13 | bb |
| 16 | cc |
+----+------+
5 x- B1 M: J8 k7 C  c3 v6 Z3 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);
Query OK, 3 rows affected

mysql> select * from student;
+----+------+
| ID | NAME |
' D6 O: {, y! f3 l( m7 g( e8 a( o+----+------+
| 11 | aa |
| 13 | bb |
3 `" V. n* h6 R1 q| 16 | cc |
+----+------+
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);
) W: n* o$ q- L& }8 i: V
% h: G0 C: c+ a; V0 w& B# r, w
0 f$ Y7 ~% \8 H; z4 Q& ~: }. Z