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

admin

方法1：
1、创建一个临时表，选取需要的数据。
& w2 _( j. \- A8 L2、清空原表。
3、临时表数据导入到原表。
+ k( Z& t4 x7 X4 q' T) F; b2 N4 Q4、删除临时表。
mysql> select * from student;
+----+------+
8 L4 G  [, J+ [$ a6 q# B| ID | NAME |
: b" T+ m% ~& E+----+------+
8 _4 f& \- b8 a1 O* r5 B* M| 11 | aa |
| 12 | aa |
$ X/ p7 S7 t7 N6 o! m' s+ L| 13 | bb |
6 s1 y1 D" a& B9 ^+ y5 i! W( V| 14 | bb |
| 15 | bb |
% K2 X3 F$ z5 z- }| 16 | cc |
# B' B6 o8 V5 b' H* l+----+------+
8 _8 F9 b* v/ r# V6 rows in set

mysql> create temporary table temp as select min(id),name from student group by name;
Query OK, 3 rows affected
5 ~) A0 Z/ B, `7 x% [Records: 3 Duplicates: 0 Warnings: 0

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

mysql> insert into student select * from temp;
& j' K* i: R/ B) X" }( B; c  NQuery OK, 3 rows affected
Records: 3 Duplicates: 0 Warnings: 0

mysql> select * from student;
+----+------+
; c/ U7 z2 u( o0 I  i| ID | NAME |
+----+------+
( a1 F9 s& h6 p" I  b4 L& `| 11 | aa |
- `6 F/ F; t3 g( e  S( a| 13 | bb |
| 16 | cc |
. {( K. k* {. |5 w+----+------+
3 rows in set

mysql> drop temporary table temp;
* W. x* z0 x  \; {Query OK, 0 rows affected
这个方法，显然存在效率问题。

---

方法2：按name分组，把最小的id保存到临时表，删除id不在最小id集合的记录，如下：
2 B$ ~- w0 |- R9 V; Hmysql> create temporary table temp as select min(id) as MINID from student group by name;
: W/ m) i6 `4 CQuery 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 |
+----+------+
| 11 | aa |
  e3 ^) x5 U4 E| 13 | bb |
| 16 | cc |
# ~3 {& n/ Q( u. g! I4 i+----+------+
/ |( R& f" E( y+ S3 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, J/ k& A# b9 ]6 b( B原因是：更新数据时使用了查询，而查询的数据又做了更新的条件，mysql不支持这种方式。
8 c* k! J  L- A: [怎么规避这个问题？
( t# S- e- v. T% N" v+ K再加一层封装，如下：
mysql> delete from student where id not in (select minid from (select min(id) as minid from student group by name) b);
3 I9 y; q- \* _& K$ C! bQuery OK, 3 rows affected

mysql> select * from student;
+----+------+
- R% n5 ]: H5 b0 g" k, M| ID | NAME |
+----+------+
% p6 [3 G) E' V7 B8 O+ r" i| 11 | aa |
| 13 | bb |
  M+ |( e* }" ~+ u9 _| 16 | cc |
+----+------+
$ Z7 x; d2 j6 K' Q3 rows in set

( A7 y" o/ _- X+ i8 k$ [! H' r方法三例如：delete from bugdata where id not in (select minid from (select min(id) as minid from bugdata group by qxbh) b);
' d0 ^2 t" U+ B7 V
+ z1 m& F( h$ q  S! c/ w0 H