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

admin

方法1：
1、创建一个临时表，选取需要的数据。
! D# y+ Z# w; c4 a, Z2 P& k+ P% r2、清空原表。
3、临时表数据导入到原表。
4、删除临时表。
mysql> select * from student;
2 P1 C) z3 a% L% |5 Z% m# [+----+------+
, m% A/ @" ?4 C. l4 |: a7 y' R| ID | NAME |
+----+------+
| 11 | aa |
| 12 | aa |
& `$ S3 z2 o" A( [4 O- t| 13 | bb |
| 14 | bb |
7 c& e- N5 k2 S5 z" e/ B( U| 15 | bb |
| 16 | cc |
* n8 u3 j, A- y, s$ ~6 b  W! m( F+----+------+
6 rows in set

mysql> create temporary table temp as select min(id),name from student group by name;
/ @" k" a5 E. S0 i8 K' n1 x) iQuery OK, 3 rows affected
Records: 3 Duplicates: 0 Warnings: 0

mysql> truncate table student;
9 u  C0 d* M$ l0 p- k) KQuery OK, 0 rows affected

mysql> insert into student select * from temp;
Query OK, 3 rows affected
- [4 Y9 G# W* y% J  |Records: 3 Duplicates: 0 Warnings: 0

mysql> select * from student;
+----+------+
8 A9 H1 l/ t, f' d" L" L* e( b| ID | NAME |
+----+------+
| 11 | aa |
* A4 {! j6 A, U| 13 | bb |
| 16 | cc |
7 Y" J- ^5 f+ E& o+----+------+
; E; C6 p( E/ v4 y& J5 x3 rows in set

mysql> drop temporary table temp;
+ A% R8 V4 `  q0 P5 @% XQuery OK, 0 rows affected
* ]+ j6 p2 V2 F" z1 r  O- U5 N# u$ S这个方法，显然存在效率问题。

---

方法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);
Query OK, 3 rows affected

mysql> select * from student;
+----+------+
| ID | NAME |
, T8 m! X/ S% N9 k; Y$ P1 a( I+----+------+
8 l: ~+ ]# |7 R0 ]) H& V6 T( ^| 11 | aa |
| 13 | bb |
9 }. z2 F" N, p& l| 16 | cc |
+----+------+
# a( ~" I, L9 z3 rows in set

---

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

mysql> delete from student where id not in (select min(id) from student group by name);
0 Y* C# Y& _  i3 u3 F# ^! n执行报错：1093 - You can't specify target table 'student' for update in FROM clause
原因是：更新数据时使用了查询，而查询的数据又做了更新的条件，mysql不支持这种方式。
( `9 P1 P4 N7 r# L# w; v7 [怎么规避这个问题？
8 w9 z& r* k" l% a8 V再加一层封装，如下：
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 |
+----+------+
. z) \4 l: C2 j| 11 | aa |
* e& U  S7 q9 _0 f| 13 | bb |
8 t. p9 Z7 K. V3 N% ?% w* j  v' f( a| 16 | cc |
$ D: L3 j( o$ @7 i+----+------+
( _* Y+ x0 U" v1 i- k3 rows in set

方法三例如：delete from bugdata where id not in (select minid from (select min(id) as minid from bugdata group by qxbh) b);
6 u2 b0 w' A) ^( q( N
$ b/ R- L8 |& U$ x
1 A  `; O8 \& l