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

admin

方法1：
1、创建一个临时表，选取需要的数据。
2、清空原表。
' {! n" L( R& r) H1 Q3、临时表数据导入到原表。
4、删除临时表。
/ \. F& H- {5 [mysql> select * from student;
' g1 |8 ~% ~: d% i( ~. w  m+----+------+
| ID | NAME |
* ^) O% F2 k9 B, [. G5 c5 T+----+------+
| 11 | aa |
| 12 | aa |
- E: M6 c' z; n2 m3 L/ H, ~8 N| 13 | bb |
| 14 | bb |
) v6 {3 ^  Y  c: y: X" S* F| 15 | bb |
| 16 | cc |
7 E; w1 [! \- W& `4 q' S' k% c- g: H+----+------+
. W0 I( e% q% Z6 C1 v; V6 rows in set

mysql> create temporary table temp as select min(id),name from student group by name;
Query OK, 3 rows affected
5 K* }' L- p, N# ~0 \) @/ t7 pRecords: 3 Duplicates: 0 Warnings: 0

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

mysql> insert into student select * from temp;
  `. i* _. p& _/ [/ f7 m( JQuery OK, 3 rows affected
  O0 x& g, Z$ B. b, xRecords: 3 Duplicates: 0 Warnings: 0

mysql> select * from student;
, j8 t3 M% j. s4 S+----+------+
| ID | NAME |
+----+------+
6 c: c8 R  }1 g# P# v| 11 | aa |
| 13 | bb |
' F4 O! n0 g8 Q% e" ~| 16 | cc |
- `$ r! w6 _4 ?8 H# t. }2 S" X' z+----+------+
; R/ @2 M# Z4 |$ q: h- d3 rows in set

mysql> drop temporary table temp;
7 ?2 Q2 Q! Y4 h0 Y0 c) {Query OK, 0 rows affected
4 e3 J. @* d1 I* ?8 h3 ?: k2 @这个方法，显然存在效率问题。

---

方法2：按name分组，把最小的id保存到临时表，删除id不在最小id集合的记录，如下：
, `) v/ T1 R1 l; o6 L* ?- f* ~" R- Umysql> create temporary table temp as select min(id) as MINID from student group by name;
Query OK, 3 rows affected
- M. j# J1 R3 w1 R+ SRecords: 3 Duplicates: 0 Warnings: 0

mysql> delete from student where id not in (select minid from temp);
8 G( a# S$ Q% J: PQuery OK, 3 rows affected

mysql> select * from student;
/ i. W, U" u' X; {, U; ^+----+------+
| ID | NAME |
+----+------+
- ]1 V. P3 |! A+ n' N# G| 11 | aa |
: L7 d8 J8 h. u/ H6 {| 13 | bb |
| 16 | cc |
1 G% {" K0 r# Y7 l4 m% J+----+------+
/ X" T- A% f! q+ t, N0 [3 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
9 t" C" E# v& K3 ^5 y1 m原因是：更新数据时使用了查询，而查询的数据又做了更新的条件，mysql不支持这种方式。
& w5 ?1 j) R9 W) e6 G怎么规避这个问题？
$ c* B* a) O" u: I再加一层封装，如下：
mysql> delete from student where id not in (select minid from (select min(id) as minid from student group by name) b);
# Y( @/ s: n% b. g! rQuery OK, 3 rows affected

mysql> select * from student;
+----+------+
| ID | NAME |
+----+------+
8 G8 ^. R5 m2 u% H| 11 | aa |
| 13 | bb |
| 16 | cc |
+----+------+
7 z/ g6 U0 _& D- e" }: O4 f: f3 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* Z5 r" S5 O3 x" t$ L" S
% G2 }: L( Z8 w6 i) S) \