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

admin

方法1：
1、创建一个临时表，选取需要的数据。
/ L0 n: e/ l- B  t3 o% C7 B2、清空原表。
3、临时表数据导入到原表。
5 n, a) H; T" k9 Q( @4、删除临时表。
mysql> select * from student;
9 e! q% h3 B6 v5 L  E- \" O. W# j+----+------+
/ n; V  _3 I7 g0 M2 h6 j$ A3 b2 j1 L  `| ID | NAME |
+----+------+
/ W' r2 V: C6 Z9 N$ @8 R+ M| 11 | aa |
| 12 | aa |
+ V$ m3 G6 ]5 g' L: I: W6 J; M( X| 13 | bb |
| 14 | bb |
| 15 | bb |
| 16 | cc |
/ ]8 y$ X0 O) [. m* g2 B+----+------+
6 rows in set

mysql> create temporary table temp as select min(id),name from student group by name;
5 V# c; I( x5 N# g4 N6 r/ BQuery OK, 3 rows affected
/ A1 ~5 M* w% w: f- h1 R. f- aRecords: 3 Duplicates: 0 Warnings: 0

mysql> truncate table student;
9 L( x9 [, {9 X$ \5 {! qQuery OK, 0 rows affected

mysql> insert into student select * from temp;
Query OK, 3 rows affected
. Y/ O( L* a. Y& h0 {0 U( P2 T& SRecords: 3 Duplicates: 0 Warnings: 0

mysql> select * from student;
+----+------+
/ V5 {+ ~" x0 \: Q! h8 d% G| ID | NAME |
; G6 g; G5 |. _1 A* ?5 Q+----+------+
| 11 | aa |
| 13 | bb |
* p6 Z7 Q4 E6 s* m( k| 16 | cc |
+----+------+
3 rows in set

mysql> drop temporary table temp;
Query 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
Records: 3 Duplicates: 0 Warnings: 0

mysql> delete from student where id not in (select minid from temp);
# G7 V6 r2 O& d/ O8 Z' n" v; IQuery OK, 3 rows affected

mysql> select * from student;
+----+------+
| ID | NAME |
# u; H, T! ~; `8 P- f, d' ^, ^+----+------+
| 11 | aa |
8 W% f/ H/ F$ V& U( j8 ~! o| 13 | bb |
| 16 | cc |
/ \9 Q" k& y1 F  E' s0 D+----+------+
1 X, `- ?0 k; c( f: `  [% W" [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
原因是：更新数据时使用了查询，而查询的数据又做了更新的条件，mysql不支持这种方式。
怎么规避这个问题？
再加一层封装，如下：
' n$ y+ @4 O2 L* Z/ N( }  `! U7 Cmysql> 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;
+----+------+
3 s4 ]6 N3 n* a4 F9 V| ID | NAME |
+----+------+
+ w2 i9 c1 S9 N1 ^- J2 t7 }| 11 | aa |
| 13 | bb |
| 16 | cc |
! q: ]! v% [& R0 W2 f9 O- F+----+------+
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);
5 Q6 m* U- `' \, R* h