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

admin

方法1：
1、创建一个临时表，选取需要的数据。
: v5 o' l$ J5 P; ]- y! i: F3 v2、清空原表。
* ]* ?) \; M$ w1 M6 c3 [. l3、临时表数据导入到原表。
4、删除临时表。
mysql> select * from student;
+----+------+
| ID | NAME |
+----+------+
| 11 | aa |
| 12 | aa |
3 Y. O7 v# O( P| 13 | bb |
| 14 | bb |
6 ]! g- j- i$ }4 K8 P| 15 | bb |
; ]9 Y% ?( r; E. X6 u9 g| 16 | cc |
+----+------+
6 rows in set

mysql> create temporary table temp as select min(id),name from student group by name;
  {0 N. p4 F! z  D; q. u! N( {Query OK, 3 rows affected
Records: 3 Duplicates: 0 Warnings: 0

mysql> truncate table student;
! \, K  G- F. k  @3 Q; Y$ DQuery 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;
$ }$ R& s8 X2 u+ {1 R5 z1 R+----+------+
0 a3 V# D) M  T! Q% u| ID | NAME |
+----+------+
. e6 k  s* s% c5 d5 s, W$ I8 w| 11 | aa |
| 13 | bb |
; w7 E# K3 n1 C$ `! t" }# _; A, B| 16 | cc |
+----+------+
3 rows in set

mysql> drop temporary table temp;
Query OK, 0 rows affected
这个方法，显然存在效率问题。

---

方法2：按name分组，把最小的id保存到临时表，删除id不在最小id集合的记录，如下：
. e4 `$ A8 b9 |" B( Imysql> create temporary table temp as select min(id) as MINID from student group by name;
Query OK, 3 rows affected
9 F5 o9 j, s' ^, T: F" ^7 d9 }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;
, z/ H4 O9 C' z7 q% A1 l+----+------+
7 u! u8 m/ W3 F$ V, q8 v( \| ID | NAME |
$ j. a% @) z; r5 n4 o+----+------+
| 11 | aa |
| 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);
( N$ E& n; W5 M7 n& X1 v/ C5 f执行报错：1093 - You can't specify target table 'student' for update in FROM clause
& e2 B3 q* y% a. o* v原因是：更新数据时使用了查询，而查询的数据又做了更新的条件，mysql不支持这种方式。
怎么规避这个问题？
( u/ G! Q2 D6 p/ s4 _" p3 i再加一层封装，如下：
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;
+----+------+
| ID | NAME |
+----+------+
| 11 | aa |
| 13 | bb |
1 O0 L5 R4 p9 m' B( @4 Z. z| 16 | cc |
' }8 v4 B- L; N% @% O- F+----+------+
7 P2 F" E) C; A7 N5 K9 S3 rows in set

, K1 n* P4 W8 X& l3 J方法三例如：delete from bugdata where id not in (select minid from (select min(id) as minid from bugdata group by qxbh) b);
, `4 d6 Q7 q, Y+ S& q; Q

( d5 ?$ ~) k- v8 ~6 H, r) o: p