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

admin

方法1：
1、创建一个临时表，选取需要的数据。
  U1 w  M3 M$ ~2、清空原表。
% p1 k8 X+ w, j2 [3、临时表数据导入到原表。
3 t2 p& M% c& e  H0 p5 q4、删除临时表。
mysql> select * from student;
' O% X' X! d8 `+----+------+
4 }# `& }! ?, b6 \# B  [| ID | NAME |
+----+------+
# y7 x/ Z- d- W* N* h" X! ^| 11 | aa |
0 v  ]% S& g) ^+ d4 c4 I| 12 | aa |
| 13 | bb |
: ^) k! W+ n/ e! ~: l6 W| 14 | bb |
| 15 | bb |
( u5 o% E; a+ J' c| 16 | cc |
) D- r7 [9 r$ c3 d; @  Q( O+----+------+
6 rows in set

mysql> create temporary table temp as select min(id),name from student group by name;
3 O; g5 V$ x- r% M7 tQuery 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;
0 M7 X+ o8 R7 `9 a$ H: v+ b9 X! w6 xQuery OK, 3 rows affected
! m# F% k7 |3 {9 zRecords: 3 Duplicates: 0 Warnings: 0

mysql> select * from student;
! ^3 }" D; r/ }* A. i, t+----+------+
1 F( i% T7 s6 Z0 W# C| ID | NAME |
0 i2 S* j# }4 l  Y: P0 }+----+------+
| 11 | aa |
; E1 `8 D# n% }' o/ \5 A% E6 _| 13 | bb |
| 16 | cc |
, H& I' a3 ~( K( ?# P+----+------+
3 rows in set

mysql> drop temporary table temp;
7 ^' g1 H1 w+ J' B7 r) s5 |+ mQuery 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;
9 ]! `/ Z/ c5 @* V$ ^0 P2 \! g3 EQuery OK, 3 rows affected
' f, X' K4 X1 k: ZRecords: 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;
" Q, |8 ?, A: O; C/ }2 O0 k% c* Q+----+------+
: P. C) S9 C) g9 ~| ID | NAME |
5 r  M% h3 z- R) V' C1 ]0 S+----+------+
) t$ @- G3 }& u& c1 U| 11 | aa |
6 U' V/ |% R5 G# V| 13 | bb |
& M9 W" b5 f3 G4 t! b) D5 ]| 16 | cc |
# w/ N6 C4 x8 [. V# K, \6 H+----+------+
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不支持这种方式。
怎么规避这个问题？
再加一层封装，如下：
: C, t0 E/ D  i% f1 L, Xmysql> delete from student where id not in (select minid from (select min(id) as minid from student group by name) b);
2 o& K/ N& O4 S' q6 qQuery OK, 3 rows affected

mysql> select * from student;
! e4 b+ S+ Y, y' b+----+------+
| ID | NAME |
( ~7 @  ]  ?7 n/ l& M" A+----+------+
| 11 | aa |
3 l* h/ U1 z; P. R| 13 | bb |
+ b. q) z$ g* d8 d0 }1 J4 G7 ]) C| 16 | cc |
; [8 P( _( V- Y' k8 o/ L+ N7 z+----+------+
3 rows in set

. U5 D& B" v% P方法三例如：delete from bugdata where id not in (select minid from (select min(id) as minid from bugdata group by qxbh) b);

+ n: V& s' d6 q- Q
) y& N" k7 `9 D8 V) m( M6 _2 y: j