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

admin

方法1：
1、创建一个临时表，选取需要的数据。
/ s* w; J0 y- n- f1 b2、清空原表。
3、临时表数据导入到原表。
8 s3 L. O6 z3 Z) b9 D4、删除临时表。
+ b. Y; h/ S, a7 o. R, u% r$ jmysql> select * from student;
+----+------+
" Q  c3 a4 A- Y- x8 P4 M; n# g| ID | NAME |
+----+------+
: z1 H% m7 S4 f! M0 C+ r| 11 | aa |
4 z6 I2 j) ^+ @5 M: m# v| 12 | aa |
| 13 | bb |
| 14 | bb |
| 15 | bb |
7 Y# `, w. Q, N& r* m| 16 | cc |
, X. p4 a: P! H6 ^+----+------+
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;
6 _5 u* l7 }, b% |% `2 LQuery OK, 0 rows affected

mysql> insert into student select * from temp;
Query OK, 3 rows affected
6 R1 G2 v1 m  s  tRecords: 3 Duplicates: 0 Warnings: 0

mysql> select * from student;
# K) I0 d* K4 X0 `+----+------+
  q1 d1 K: ^3 j( ]. e( c3 F| ID | NAME |
+----+------+
| 11 | aa |
' X2 M9 o( t& {  r1 [+ d| 13 | bb |
| 16 | cc |
% t' o- n& m* z6 g" V+ J+----+------+
6 `7 K+ Q$ n! \) @3 W3 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;
' B2 T6 r9 D3 @& N% |& d. MQuery 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 |
) [. j; M% h" I, \( C; b2 p+----+------+
| 11 | aa |
9 N; B! v1 H3 U3 v/ t4 }| 13 | bb |
( U( D( J8 v* v, n2 E6 j- N| 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
$ _! N/ t0 \, r原因是：更新数据时使用了查询，而查询的数据又做了更新的条件，mysql不支持这种方式。
" j9 h) z4 q) C9 N怎么规避这个问题？
$ Y$ d! k6 i6 i% G- l. B! o再加一层封装，如下：
- l0 {  u( F0 P$ X. O8 ~1 x, O. _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;
# P1 G2 g5 L2 b6 Q* H+----+------+
& ?2 V& G+ H& D% \  y| ID | NAME |
5 i3 C& B" ~- f. ^: v+----+------+
$ g$ U2 a' j5 _| 11 | aa |
| 13 | bb |
| 16 | cc |
$ e; z/ e3 K/ z+ D# j9 G. |% o+----+------+
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);
2 a2 N" }; @" H9 \7 w' N5 o! W$ b

& X# i' Y9 V$ r: T0 `