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

admin

方法1：
1、创建一个临时表，选取需要的数据。
2、清空原表。
' q. Y2 k4 o( @' I. [3、临时表数据导入到原表。
$ {2 x; i/ W: u8 ]4 V4、删除临时表。
5 H. Z7 o1 H% t# Kmysql> select * from student;
+----+------+
+ O3 z( l$ @0 Q8 u' `9 N( [* {| ID | NAME |
& E+ i) V! ]7 ^' f& q1 `+ n+----+------+
# D$ {& k6 c' h- N| 11 | aa |
| 12 | aa |
| 13 | bb |
$ L6 _( D' b$ f! S, q; N| 14 | bb |
| 15 | bb |
0 T- s- \  d+ U" r% U, E| 16 | cc |
+----+------+
) Z! \& K; [+ u. M/ F7 w* q6 rows in set

mysql> create temporary table temp as select min(id),name from student group by name;
. d8 o1 X# n; u2 K4 i2 T3 b. [Query OK, 3 rows affected
0 E& ~4 r. R- `/ |: u  s" o! T( {Records: 3 Duplicates: 0 Warnings: 0

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

mysql> insert into student select * from temp;
4 a2 S& b1 S! ]+ Y% [) kQuery OK, 3 rows affected
Records: 3 Duplicates: 0 Warnings: 0

mysql> select * from student;
+----+------+
| ID | NAME |
* j5 E7 q; X# H  F; w* Y9 k! x, V, }+----+------+
| 11 | aa |
| 13 | bb |
0 D. m$ J. {0 b7 q6 f8 l3 q| 16 | cc |
+----+------+
2 R' c! r0 r& T. |, ?5 S3 rows in set

mysql> drop temporary table temp;
% }8 @4 ?3 D! zQuery 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;
Query OK, 3 rows affected
' G7 |: K( N& E2 S. ARecords: 3 Duplicates: 0 Warnings: 0

mysql> delete from student where id not in (select minid from temp);
/ p: Z8 m" h- M, D, r% MQuery OK, 3 rows affected

mysql> select * from student;
/ ~; B! D- F& q: V* M6 V6 }4 E3 _8 r+----+------+
* s& L  Y* Q% S3 ?' C1 S' l, s| ID | NAME |
! D7 k% X$ E. S8 u8 J+----+------+
* r0 r3 N7 ~$ M| 11 | aa |
9 H  D5 e5 D; s| 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);
执行报错：1093 - You can't specify target table 'student' for update in FROM clause
原因是：更新数据时使用了查询，而查询的数据又做了更新的条件，mysql不支持这种方式。
怎么规避这个问题？
再加一层封装，如下：
2 S3 b$ P! b2 Z5 k. wmysql> 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;
, Q/ D0 Q/ ~9 h+ g+----+------+
| ID | NAME |
" U* b* [* j6 K. _" m+----+------+
: u; f/ R+ n& Z4 g2 _9 `2 c| 11 | aa |
+ d9 d: ]) r7 A4 E0 ~: U8 J; B- e| 13 | bb |
$ T) q% e7 J# k) `0 G9 d& X! [& Q" R| 16 | cc |
. h/ `' ]0 a) R9 _- e: s+ ~+----+------+
8 g3 C# x5 G3 T( d2 P( a0 D1 Z/ m3 rows in set

方法三例如：delete from bugdata where id not in (select minid from (select min(id) as minid from bugdata group by qxbh) b);