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

admin

方法1：
1、创建一个临时表，选取需要的数据。
' P" [) R8 y& t2、清空原表。
6 A1 p; d5 c* m3、临时表数据导入到原表。
4、删除临时表。
  c, |7 t8 D) u$ V  Vmysql> select * from student;
+----+------+
| ID | NAME |
+----+------+
| 11 | aa |
| 12 | aa |
- L6 P; [) ~/ b| 13 | bb |
| 14 | bb |
8 D" I$ A) r! V8 ~2 W3 F| 15 | bb |
| 16 | cc |
, e- f2 [1 l# l* H+ l- }+----+------+
6 rows in set

mysql> create temporary table temp as select min(id),name from student group by name;
2 V5 a* L, j2 [6 G- C6 N4 |( AQuery OK, 3 rows affected
2 ?/ v4 Z# {% T) {& J. `: URecords: 3 Duplicates: 0 Warnings: 0

mysql> truncate table student;
3 w9 X! m8 f: z0 j* cQuery OK, 0 rows affected

mysql> insert into student select * from temp;
" Z& y% t) Z/ `7 J& |6 DQuery OK, 3 rows affected
; G+ d: B  u" I- l- x+ R3 z4 VRecords: 3 Duplicates: 0 Warnings: 0

mysql> select * from student;
& Y( h" _" B/ A4 Z7 F' i+----+------+
, g+ d5 A. J; X" r2 J* _| ID | NAME |
; X1 `& [* U3 Z  {0 {+----+------+
| 11 | aa |
' e; F  Q% L' Q2 J8 i5 `2 H& E| 13 | bb |
| 16 | cc |
+ Y; J3 N5 f  }$ G+----+------+
* }) v3 k1 T4 E8 U6 ~3 rows in set

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

---

方法2：按name分组，把最小的id保存到临时表，删除id不在最小id集合的记录，如下：
) r: z! N4 [  \$ omysql> create temporary table temp as select min(id) as MINID from student group by name;
Query OK, 3 rows affected
9 J1 ]) s- `: Y( e- I3 eRecords: 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;
+----+------+
8 h8 u2 ^' O/ d# V| ID | NAME |
* l/ d! e' d6 z  \$ A- }+----+------+
| 11 | aa |
| 13 | bb |
| 16 | cc |
+----+------+
( _; g9 a- T. {* E" D; O. R3 rows in set

---

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

mysql> delete from student where id not in (select min(id) from student group by name);
$ `" M2 ]( T2 _* f" B执行报错：1093 - You can't specify target table 'student' for update in FROM clause
原因是：更新数据时使用了查询，而查询的数据又做了更新的条件，mysql不支持这种方式。
2 \% f- |+ D/ N' ~* {+ B4 ~怎么规避这个问题？
再加一层封装，如下：
mysql> delete from student where id not in (select minid from (select min(id) as minid from student group by name) b);
7 L; k$ B  w% R8 E6 r3 c: ~Query OK, 3 rows affected

mysql> select * from student;
+----+------+
| ID | NAME |
+----+------+
| 11 | aa |
| 13 | bb |
| 16 | cc |
* H  n. m3 j6 k) o+----+------+
9 ~7 E/ H# x' B( q! Q2 @, O' b" o3 rows in set

方法三例如：delete from bugdata where id not in (select minid from (select min(id) as minid from bugdata group by qxbh) b);
9 ^: c7 P. R$ F( U* W/ h$ r1 H
/ [/ y: {8 B" a" o
1 L$ j; l1 `" f. i3 s/ x9 A7 @. ^