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

admin

方法1：
3 m; z( o$ [) N& e( \; `9 s1、创建一个临时表，选取需要的数据。
2、清空原表。
3、临时表数据导入到原表。
# j  Q: w) Q' q4、删除临时表。
mysql> select * from student;
+----+------+
5 w! N% G+ Y9 B& E- F- R: y| ID | NAME |
+----+------+
| 11 | aa |
| 12 | aa |
  t  f: m1 ?# m4 b9 H+ n| 13 | bb |
| 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;
Query OK, 3 rows affected
Records: 3 Duplicates: 0 Warnings: 0

mysql> truncate table student;
. i5 T! Y. c6 q( E& jQuery OK, 0 rows affected

mysql> insert into student select * from temp;
) v) T% d0 y9 C3 rQuery OK, 3 rows affected
5 Y; K' @. |5 W6 n/ q* DRecords: 3 Duplicates: 0 Warnings: 0

mysql> select * from student;
+----+------+
' H" ], Y; F' e! P) D) T| ID | NAME |
3 f+ L0 S4 W" y+----+------+
8 f3 o) L/ N* a; ?5 k| 11 | aa |
; h+ I: a+ x, D1 b6 }" S: T8 g| 13 | bb |
8 h. z4 A2 Z# i9 c; Q) |, C3 ~| 16 | cc |
+----+------+
( n# Z  p8 E& J- ]' \3 rows in set

mysql> drop temporary table temp;
7 B/ }4 J8 c% D7 ~+ N% MQuery 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
% o( `- F! ?* |7 l. ARecords: 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;
# n# l8 l% u3 T' i1 L+----+------+
| ID | NAME |
+----+------+
| 11 | aa |
| 13 | bb |
| 16 | cc |
+----+------+
2 c- Y; H+ U$ }. |/ F3 rows in set

---

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

mysql> delete from student where id not in (select min(id) from student group by name);
2 T5 Q! l7 B+ B  E9 Q7 L执行报错：1093 - You can't specify target table 'student' for update in FROM clause
' S3 D, U9 E& P原因是：更新数据时使用了查询，而查询的数据又做了更新的条件，mysql不支持这种方式。
0 k( Y/ ]$ k1 ?" E+ s; M怎么规避这个问题？
再加一层封装，如下：
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;
+----+------+
| ID | NAME |
+----+------+
" c% X* c' Y1 y0 N| 11 | aa |
. U, a' ^' P% ]" s5 r| 13 | bb |
| 16 | cc |
+----+------+
9 Y2 u+ v0 s. v) n- a- R) T3 rows in set

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


- Q: ]0 i8 ^3 o8 N: j