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

admin

方法1：
1、创建一个临时表，选取需要的数据。
: J) Y+ S6 F! V! g; v$ \0 F2、清空原表。
- D( v# d# X# O3 _* E3、临时表数据导入到原表。
4 Z9 H1 S; V* ]8 q0 u  r/ g4、删除临时表。
+ i3 A) e3 a1 _- T8 ^mysql> select * from student;
+----+------+
" k# Y4 ~3 \5 C| ID | NAME |
+----+------+
| 11 | aa |
* z. j/ j3 S7 M0 f: P& C| 12 | aa |
/ d: H% G' Z2 c( B0 m: M( l7 r  U| 13 | bb |
| 14 | bb |
& U- \( z1 j! j% y  W: U/ G/ M| 15 | bb |
| 16 | cc |
" b" n- C$ [+ _) ^6 q  t6 s+----+------+
1 J/ g, t4 Q! w8 j6 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;
Query OK, 0 rows affected

mysql> insert into student select * from temp;
0 R  o/ V: j% V# J8 G# T$ aQuery OK, 3 rows affected
Records: 3 Duplicates: 0 Warnings: 0

mysql> select * from student;
+----+------+
| ID | NAME |
+ V* ~8 z3 S; W( \% F+----+------+
# R9 A- e: |6 |7 x/ z8 _| 11 | aa |
1 H+ V/ k! }0 u& _* Q! @6 _+ ]| 13 | bb |
3 r9 K4 R, R* u0 O' X7 s, w  T| 16 | cc |
4 d2 Z/ W$ u  S3 q: f1 D% O+----+------+
& Q  R3 p6 r9 }+ H- X, B3 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;
5 u9 t4 Z4 W' D" c$ f4 Z7 l$ o1 @Query OK, 3 rows affected
Records: 3 Duplicates: 0 Warnings: 0

mysql> delete from student where id not in (select minid from temp);
, z& j- v" w% G7 f3 SQuery OK, 3 rows affected

mysql> select * from student;
/ D* @- o7 u/ Z" ~1 {" |, d- y+----+------+
" b/ o0 r% l$ W* C' \: u+ H| ID | NAME |
1 z  ^# T% D% G! x  L2 B3 |3 `+----+------+
# v8 m; j8 h* Q% P' E3 h* X| 11 | aa |
5 ]+ v" w+ ]4 a) Z0 H+ i1 r4 t| 13 | bb |
| 16 | cc |
+----+------+
3 rows in set

---

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

mysql> delete from student where id not in (select min(id) from student group by name);
: o2 R4 t0 {& f0 V3 y执行报错：1093 - You can't specify target table 'student' for update in FROM clause
* Z: }# ?5 l  p- H* E( j原因是：更新数据时使用了查询，而查询的数据又做了更新的条件，mysql不支持这种方式。
怎么规避这个问题？
再加一层封装，如下：
! H+ a$ ?( P" I7 z  N4 r7 vmysql> delete from student where id not in (select minid from (select min(id) as minid from student group by name) b);
# |# a6 I3 g& `! R7 ~1 TQuery OK, 3 rows affected

mysql> select * from student;
5 \! ?! I( ~7 A2 R; V2 q+----+------+
| ID | NAME |
. |) \, D' \2 @* K: V1 i# A+----+------+
| 11 | aa |
# S1 b( b  R+ r  E# \| 13 | bb |
| 16 | cc |
( K7 T% }8 a- D8 x4 ?, C# j+----+------+
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);
4 U" b7 g* v; ]' T. i& k0 S
8 y/ j$ H* U: U' ]. f