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

admin

方法1：
1、创建一个临时表，选取需要的数据。
( i6 m* q" h7 E9 q0 _2、清空原表。
3、临时表数据导入到原表。
4 h* V3 q7 N, ?. Q# v! A8 [* Y+ {4、删除临时表。
) |& w% l( ]- X# _+ imysql> select * from student;
0 ~+ y3 }# W! k4 g5 A+----+------+
| ID | NAME |
+ x2 K- k" Y) W+ I% f- q9 C9 q# d+----+------+
4 I+ T* P: w' || 11 | aa |
| 12 | aa |
| 13 | bb |
| 14 | bb |
| 15 | bb |
6 h$ w. d7 \5 o| 16 | cc |
+----+------+
3 x( Y2 Q: m5 A! |& S" A' s/ G6 rows in set

mysql> create temporary table temp as select min(id),name from student group by name;
Query OK, 3 rows affected
1 r( c0 \. [) P# HRecords: 3 Duplicates: 0 Warnings: 0

mysql> truncate table student;
  i) l( B( W( C' ]1 `1 SQuery OK, 0 rows affected

mysql> insert into student select * from temp;
0 }: M4 q! d6 ]6 q* tQuery OK, 3 rows affected
Records: 3 Duplicates: 0 Warnings: 0

mysql> select * from student;
+----+------+
| ID | NAME |
+----+------+
| 11 | aa |
| 13 | bb |
| 16 | cc |
1 h7 C4 E5 y6 T+----+------+
6 G& ?) N: q, u3 rows in set

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

---

方法2：按name分组，把最小的id保存到临时表，删除id不在最小id集合的记录，如下：
* [) ^1 @' ?* |) Pmysql> create temporary table temp as select min(id) as MINID from student group by name;
Query OK, 3 rows affected
* ?# Z. I. }1 jRecords: 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;
; m& }6 w' R% B+----+------+
" {! @9 C1 q, U' b| ID | NAME |
7 X5 n8 E4 o6 ^; C8 M+----+------+
$ H! ^: ^) e. D2 V' ]1 i' K  ^2 h| 11 | aa |
: A7 i: G# F. l$ C| 13 | bb |
* `% m: Z$ d! {# d' y) S) t7 y2 y( _| 16 | cc |
+----+------+
4 z' u( f/ r. O! w7 X: J) n3 rows in set

---

方法3：直接在原表上操作，容易想到的sql语句如下：

mysql> delete from student where id not in (select min(id) from student group by name);
1 V! d/ J; o, M4 U' g- T2 J执行报错：1093 - You can't specify target table 'student' for update in FROM clause
: b" J- a! U- {0 P+ h3 [原因是：更新数据时使用了查询，而查询的数据又做了更新的条件，mysql不支持这种方式。
" j3 F8 i) {# k# T8 u2 f0 R怎么规避这个问题？
再加一层封装，如下：
mysql> delete from student where id not in (select minid from (select min(id) as minid from student group by name) b);
) M9 y0 m" y+ T6 {" C+ |+ ^6 m* O* e+ A& MQuery OK, 3 rows affected

mysql> select * from student;
/ b; b1 y5 I* c) v# n3 }+----+------+
| ID | NAME |
5 ]0 X) t' v1 K/ i" E+----+------+
, Q7 e7 p: O/ P( z7 q$ |  ]$ ]; e| 11 | aa |
! ~- e  P1 Z7 ~4 P- l# Z* R1 B| 13 | bb |
| 16 | cc |
+----+------+
% q4 X- ]  r, s) n3 rows in set

方法三例如：delete from bugdata where id not in (select minid from (select min(id) as minid from bugdata group by qxbh) b);
& j, [+ T" u2 q0 y, l: \- G
/ k  S* W% h1 u; E; G! I* c. D