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

admin

方法1：
1、创建一个临时表，选取需要的数据。
2、清空原表。
: t7 L0 n: S( I5 U  p6 w5 H/ T3、临时表数据导入到原表。
3 A3 \' L* z2 I, M* ]. f7 ?. e% F4、删除临时表。
# b7 v+ i+ @# s8 z; W$ Dmysql> select * from student;
8 A1 r, \. w* Y2 b+ i( G+----+------+
| ID | NAME |
  _- P" Q" p9 F5 n/ @  i1 q" r0 z, Z+----+------+
* f9 \0 ?  t& e4 L6 K) @2 i0 O| 11 | aa |
; ^/ M1 V) b0 f7 u! ?| 12 | aa |
| 13 | bb |
3 R. g$ l) R7 o2 e5 Q: O7 d+ Q| 14 | bb |
3 Q( D: `/ u' A0 E! Y/ ]/ y| 15 | bb |
) h3 T8 @. S( `4 V7 L* v| 16 | cc |
+----+------+
5 b2 K3 J3 |7 R3 m5 M6 rows in set

mysql> create temporary table temp as select min(id),name from student group by name;
Query OK, 3 rows affected
7 v! s) @; a" z& ?Records: 3 Duplicates: 0 Warnings: 0

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

mysql> insert into student select * from temp;
Query OK, 3 rows affected
Records: 3 Duplicates: 0 Warnings: 0

mysql> select * from student;
+----+------+
. ~, H; M6 v# `0 J9 P8 t5 q3 R| ID | NAME |
+----+------+
| 11 | aa |
| 13 | bb |
7 D$ d$ R! f& S$ F& k/ q$ X| 16 | cc |
+----+------+
3 rows in set

mysql> drop temporary table temp;
! K* `9 }8 c$ g! \& Z& K( gQuery OK, 0 rows affected
6 V2 ^) q/ r6 R8 A这个方法，显然存在效率问题。

---

方法2：按name分组，把最小的id保存到临时表，删除id不在最小id集合的记录，如下：
  u$ X2 e0 D' |* a* ]4 Hmysql> create temporary table temp as select min(id) as MINID from student group by name;
7 y0 n: h1 c; [: O: [( oQuery OK, 3 rows affected
& j% x- u8 p9 s! T6 i- }- DRecords: 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 |
+----+------+
: ~" u/ a1 X4 O; S1 I. c- c| 11 | aa |
| 13 | bb |
| 16 | cc |
$ e& {8 Q( L* M/ z+----+------+
3 rows in set

---

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

mysql> delete from student where id not in (select min(id) from student group by name);
9 t5 b) J1 N1 O6 k执行报错：1093 - You can't specify target table 'student' for update in FROM clause
- c3 }% F) f0 Z; _& a原因是：更新数据时使用了查询，而查询的数据又做了更新的条件，mysql不支持这种方式。
怎么规避这个问题？
( o4 j0 t$ p- Q再加一层封装，如下：
0 b3 P/ K& u+ \mysql> delete from student where id not in (select minid from (select min(id) as minid from student group by name) b);
Query OK, 3 rows affected

mysql> select * from student;
+ l: Z& t; D2 U+----+------+
7 |( q# d9 Z- n8 @6 E7 p| ID | NAME |
+----+------+
| 11 | aa |
% I  j& ^8 s8 g| 13 | bb |
| 16 | cc |
' b! I8 z6 c" x$ n+----+------+
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);