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

admin

方法1：
1、创建一个临时表，选取需要的数据。
2、清空原表。
. t2 l: C; D  d" j6 u3、临时表数据导入到原表。
4、删除临时表。
% Y! |& |+ z" R- \, X) B% rmysql> select * from student;
9 F1 y+ R6 S% g1 ~+----+------+
| ID | NAME |
+----+------+
| 11 | aa |
| 12 | aa |
- X, D1 ]: z1 @, e5 J, ^) \| 13 | bb |
4 g6 z3 w! n8 ]7 U: O: ~| 14 | bb |
& ^8 R$ d- K& J: v) a$ L: s' w| 15 | bb |
3 T2 V# w9 \5 L4 m6 H| 16 | cc |
6 q5 ?2 |; H1 p$ W; F' q5 a+----+------+
3 k5 c( X$ F9 |: l3 x* s, {) i- [$ Q6 rows in set

mysql> create temporary table temp as select min(id),name from student group by name;
Query OK, 3 rows affected
6 j  N6 m  N4 O/ a( W7 j' SRecords: 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
% [3 _# u: c5 B! lRecords: 3 Duplicates: 0 Warnings: 0

mysql> select * from student;
* E4 X* k- F+ g* G+ n# o: [+----+------+
1 G! r- n$ ~: M( S| ID | NAME |
+----+------+
| 11 | aa |
, `' `, s  }/ `- Y  o( C| 13 | bb |
; C1 G; Y  A0 i2 t% _, i| 16 | cc |
1 M' F4 ~: y) J2 c+----+------+
0 O2 H& r+ x" y' y7 x; p3 rows in set

mysql> drop temporary table temp;
Query OK, 0 rows affected
5 z9 f6 j4 Q" I5 w- l7 \5 f这个方法，显然存在效率问题。

---

方法2：按name分组，把最小的id保存到临时表，删除id不在最小id集合的记录，如下：
$ @3 b' j2 s6 q0 C+ r" zmysql> create temporary table temp as select min(id) as MINID from student group by name;
5 o" n5 t8 m8 s5 M6 I! [: Q& qQuery OK, 3 rows affected
Records: 3 Duplicates: 0 Warnings: 0

mysql> delete from student where id not in (select minid from temp);
4 ~- T, J% L# g& v* n1 h( yQuery OK, 3 rows affected

mysql> select * from student;
+----+------+
| ID | NAME |
) ^$ F% D/ }6 q4 i" r! X+----+------+
. S: a5 M1 W: p| 11 | aa |
| 13 | bb |
| 16 | cc |
+----+------+
  j) }; Z1 {, z! g6 D% s3 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
2 a. c& W( J* S. R( `4 o原因是：更新数据时使用了查询，而查询的数据又做了更新的条件，mysql不支持这种方式。
怎么规避这个问题？
再加一层封装，如下：
! T- R# N5 a* @* Cmysql> delete from student where id not in (select minid from (select min(id) as minid from student group by name) b);
# \1 c6 G7 v5 G5 D! PQuery OK, 3 rows affected

mysql> select * from student;
, |" V- R6 a0 Y7 S3 Z; `+----+------+
* ?, {+ r, z) [8 ^$ z* x9 || ID | NAME |
( U# y% o6 }% h7 P% O+----+------+
| 11 | aa |
| 13 | bb |
  g  E7 u3 x+ i" [% e| 16 | cc |
+----+------+
, \1 C1 X% D& P( A. j3 C3 rows in set

方法三例如：delete from bugdata where id not in (select minid from (select min(id) as minid from bugdata group by qxbh) b);
" J/ p: |" o4 _" v& O4 Z) I
. p1 x0 Q8 X5 s8 @  d# }. O