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

admin

方法1：
6 e& `' p8 |+ l% r7 ]1、创建一个临时表，选取需要的数据。
- c5 i/ v$ S4 F- I+ S; S: g- D5 X2、清空原表。
7 ?3 o5 f% X3 E; O! A3、临时表数据导入到原表。
4、删除临时表。
$ _* p" y" h# B2 Qmysql> select * from student;
+----+------+
| ID | NAME |
+----+------+
6 M0 Z! c  x* P: B| 11 | aa |
| 12 | aa |
| 13 | bb |
| 14 | bb |
7 K' R9 Q$ {7 i# O! K6 o& }: o% f| 15 | bb |
| 16 | cc |
+----+------+
6 m+ O8 r3 V: K; ]6 rows in set

mysql> create temporary table temp as select min(id),name from student group by name;
! I& H" [+ g7 z' r" ^6 e9 AQuery OK, 3 rows affected
/ Z  `" x* w' k- ]Records: 3 Duplicates: 0 Warnings: 0

mysql> truncate table student;
: p2 y; w, T2 H2 }Query OK, 0 rows affected

mysql> insert into student select * from temp;
' k/ s* r& @  N4 @" j4 o. cQuery OK, 3 rows affected
Records: 3 Duplicates: 0 Warnings: 0

mysql> select * from student;
, ^1 Q, J+ i; y! t$ o3 w+----+------+
! e& ]8 I7 {7 V| ID | NAME |
+----+------+
7 Z" L) A0 K" \3 Z( H* ~| 11 | aa |
| 13 | bb |
| 16 | cc |
, `3 f- [" b8 Y- ?2 ]) }# B7 G+----+------+
- F  f  T* N! [. H2 k4 h3 rows in set

mysql> drop temporary table temp;
Query OK, 0 rows affected
& R- c; v& o7 C8 O这个方法，显然存在效率问题。

---

方法2：按name分组，把最小的id保存到临时表，删除id不在最小id集合的记录，如下：
2 s2 T3 j% K& O0 v, }: \- T6 r9 Zmysql> create temporary table temp as select min(id) as MINID from student group by name;
Query OK, 3 rows affected
Records: 3 Duplicates: 0 Warnings: 0

mysql> delete from student where id not in (select minid from temp);
' l& w" z7 u4 T+ gQuery OK, 3 rows affected

mysql> select * from student;
+----+------+
| ID | NAME |
# F4 c7 H1 v& t1 g) t+----+------+
0 v7 l! a6 s: o$ C2 F$ w' _* ~; Z| 11 | aa |
| 13 | bb |
6 @6 a& B5 T% J0 q1 k1 P3 {| 16 | cc |
9 M& Z2 V; G( v9 ?# a+----+------+
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
4 m" I% p8 v% c) I0 S! R( x原因是：更新数据时使用了查询，而查询的数据又做了更新的条件，mysql不支持这种方式。
# |9 t; g# [+ M怎么规避这个问题？
再加一层封装，如下：
  |* t6 a! I: b1 w/ n) y& emysql> delete from student where id not in (select minid from (select min(id) as minid from student group by name) b);
  w1 t9 S5 g" _8 \8 zQuery OK, 3 rows affected

mysql> select * from student;
3 c( R+ i) r/ m1 L( ~1 }4 F) h6 e* a+----+------+
! q/ p0 j/ p/ q6 q0 D8 ^  a0 h; D| ID | NAME |
5 a) R* M2 R7 f: {. V3 g+----+------+
2 ~" y# K6 Q+ X| 11 | aa |
7 b2 [: h# v# Q& Z| 13 | bb |
/ Y* G, U; `, D  t# k" `* D, ~1 o| 16 | cc |
+----+------+
3 rows in set

0 C8 C5 z5 q5 g( X" A方法三例如：delete from bugdata where id not in (select minid from (select min(id) as minid from bugdata group by qxbh) b);

7 @# v% f: k$ W& G( T
5 e! P3 w% i0 o* n; [8 p