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

admin

方法1：
1、创建一个临时表，选取需要的数据。
2、清空原表。
) @6 u! w+ U  T8 \) S3、临时表数据导入到原表。
4、删除临时表。
' p2 V- `) }8 gmysql> select * from student;
$ n; Z1 a  D  c1 T+ j5 o% X+----+------+
| ID | NAME |
+----+------+
+ ~, x+ K9 t! ]9 R6 }! ?7 L( W| 11 | aa |
3 R2 J5 ?3 o) G. s9 V. K: o| 12 | aa |
| 13 | bb |
$ ~( d7 _/ C" _  c| 14 | bb |
| 15 | bb |
| 16 | cc |
+----+------+
6 rows in set

mysql> create temporary table temp as select min(id),name from student group by name;
4 ~5 j: N7 N. f- Y5 u5 w% yQuery OK, 3 rows affected
Records: 3 Duplicates: 0 Warnings: 0

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

mysql> insert into student select * from temp;
% w' v* A* P$ @" {# q3 J! CQuery OK, 3 rows affected
Records: 3 Duplicates: 0 Warnings: 0

mysql> select * from student;
+----+------+
| ID | NAME |
+----+------+
' O8 D, ~1 }0 || 11 | aa |
0 ^0 q* O" h$ n* d) u. M" b| 13 | bb |
| 16 | cc |
: f, p5 E8 s0 w+----+------+
3 rows in set

mysql> drop temporary table temp;
Query OK, 0 rows affected
& `. P: Y) P2 ?5 X& E这个方法，显然存在效率问题。

---

方法2：按name分组，把最小的id保存到临时表，删除id不在最小id集合的记录，如下：
mysql> create temporary table temp as select min(id) as MINID from student group by name;
9 x  d! T- z/ ^; D2 ?6 JQuery 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;
8 E9 z, p5 u  u+----+------+
| ID | NAME |
+----+------+
| 11 | aa |
| 13 | bb |
| 16 | cc |
' J% ~" K" h9 Z% i+----+------+
3 rows in set

---

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

mysql> delete from student where id not in (select min(id) from student group by name);
* ]8 a' F: P% l  X执行报错：1093 - You can't specify target table 'student' for update in FROM clause
原因是：更新数据时使用了查询，而查询的数据又做了更新的条件，mysql不支持这种方式。
8 a- n7 J; k$ d9 [; x怎么规避这个问题？
  N/ O/ w: A8 N1 l+ v8 b再加一层封装，如下：
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;
) ]2 k( M) o1 F* P$ ?+ o: B, N+----+------+
| ID | NAME |
  W8 f' n, ^% L! w+ T& w+----+------+
/ o8 |2 U8 S$ I' x3 V: `| 11 | aa |
' m# R7 t+ i7 J6 _1 {7 j. L  `/ k| 13 | bb |
| 16 | cc |
+----+------+
3 rows in set

方法三例如：delete from bugdata where id not in (select minid from (select min(id) as minid from bugdata group by qxbh) b);
' U; Q' L7 d* L