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

admin

方法1：
1、创建一个临时表，选取需要的数据。
+ r- W* y1 t0 |& S; i# G! G2、清空原表。
% ^: s5 o3 j% Y$ K4 y3、临时表数据导入到原表。
# w; ^* r. ?6 }7 Q+ z- ~4、删除临时表。
. ?( n  A6 p% b3 G( Imysql> select * from student;
+----+------+
| ID | NAME |
+----+------+
| 11 | aa |
| 12 | aa |
6 c" K7 W' k& a* d' y; @| 13 | bb |
; N" p3 k7 M! k1 d| 14 | bb |
| 15 | bb |
| 16 | cc |
) z0 `  @! [$ e' i7 V+----+------+
9 g+ Z2 G& O& t) x8 Q3 |6 rows in set

mysql> create temporary table temp as select min(id),name from student group by name;
, b$ Q4 j; D0 k  r+ f9 |4 xQuery OK, 3 rows affected
7 {( l8 A; v9 Z9 i4 N! c. n( _Records: 3 Duplicates: 0 Warnings: 0

mysql> truncate table student;
$ G# F. M$ b5 U: O5 B1 bQuery OK, 0 rows affected

mysql> insert into student select * from temp;
( e% b. Y8 `' U5 xQuery OK, 3 rows affected
Records: 3 Duplicates: 0 Warnings: 0

mysql> select * from student;
! N5 w+ h2 m% }  W- Q! _7 x+----+------+
/ O( M. Q% u% X( e| ID | NAME |
7 M9 B+ p- q/ Z# C. q& w+----+------+
| 11 | aa |
| 13 | bb |
: j3 I; |+ @' E) W* ]+ k: [. E; J| 16 | cc |
* l- \& q/ w( ~$ ]+----+------+
& o: q4 @: y( U( ]3 rows in set

mysql> drop temporary table temp;
1 d( l2 k# {3 o/ PQuery OK, 0 rows affected
! L6 M/ n; m0 u4 t9 }5 V8 C这个方法，显然存在效率问题。

---

方法2：按name分组，把最小的id保存到临时表，删除id不在最小id集合的记录，如下：
$ c  \& R6 I' p+ m" Xmysql> create temporary table temp as select min(id) as MINID from student group by name;
6 p! A. W2 `. |' b! T! o6 YQuery OK, 3 rows affected
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;
+----+------+
| ID | NAME |
+----+------+
| 11 | aa |
| 13 | bb |
4 M7 I. l. W  e8 F| 16 | cc |
+----+------+
3 rows in set

---

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

mysql> delete from student where id not in (select min(id) from student group by name);
) p: O0 k% F- b( c执行报错：1093 - You can't specify target table 'student' for update in FROM clause
原因是：更新数据时使用了查询，而查询的数据又做了更新的条件，mysql不支持这种方式。
, t0 E4 p$ k( h" B$ Z2 ^怎么规避这个问题？
再加一层封装，如下：
. h. s  G: ^7 }7 Tmysql> delete from student where id not in (select minid from (select min(id) as minid from student group by name) b);
9 M! k9 T6 O, ~) ~Query OK, 3 rows affected

mysql> select * from student;
* L5 R5 @. W/ ]7 S# d: J+----+------+
/ w6 ]2 w3 V. {/ m! F; D| ID | NAME |
+----+------+
| 11 | aa |
4 u+ \- q: ?. y- E1 B| 13 | bb |
) M1 r  \2 U- ~% T| 16 | cc |
+----+------+
3 rows in set

. n( b( @  o$ }/ k/ B1 {+ H方法三例如：delete from bugdata where id not in (select minid from (select min(id) as minid from bugdata group by qxbh) b);

: |) {- |: u' E* D
& V1 T3 B  ?* a0 ?+ _3 r