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

admin

方法1：
1、创建一个临时表，选取需要的数据。
2、清空原表。
3、临时表数据导入到原表。
+ @$ Q7 v, x0 N- N7 n+ F4、删除临时表。
mysql> select * from student;
+----+------+
1 c4 C0 b0 |) S! ~| ID | NAME |
+----+------+
| 11 | aa |
: S( l8 ]5 c; b( a4 B% D( N) U| 12 | aa |
; w& l3 b2 }1 P2 r' n# z| 13 | bb |
| 14 | bb |
: ~4 {, L( U( {7 T| 15 | bb |
| 16 | cc |
6 ?/ g* s* b4 @4 ^/ m6 p' ~/ j+----+------+
; C2 c! r- N- e" m- S3 A6 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;
# k) z2 J, L3 r( V# eQuery OK, 0 rows affected

mysql> insert into student select * from temp;
Query OK, 3 rows affected
5 g" C9 B) G! I& `2 J( |Records: 3 Duplicates: 0 Warnings: 0

mysql> select * from student;
. ?# w( ^+ N( ^+ d+----+------+
| ID | NAME |
+----+------+
| 11 | aa |
| 13 | bb |
| 16 | cc |
+----+------+
( G: G+ n8 e8 Q" R; h  G0 f3 rows in set

mysql> drop temporary table temp;
0 n3 ]4 @% J1 c* d) ?Query OK, 0 rows affected
; Z# O6 _: R" M' a. {这个方法，显然存在效率问题。

---

方法2：按name分组，把最小的id保存到临时表，删除id不在最小id集合的记录，如下：
mysql> create temporary table temp as select min(id) as MINID from student group by name;
8 ]1 f# [/ k3 C0 A% dQuery OK, 3 rows affected
Records: 3 Duplicates: 0 Warnings: 0

mysql> delete from student where id not in (select minid from temp);
" Z; k) ^" ^# m5 J- T8 u% VQuery OK, 3 rows affected

mysql> select * from student;
+----+------+
9 j5 K6 q  a8 {0 H6 _8 e| ID | NAME |
+----+------+
| 11 | aa |
| 13 | bb |
| 16 | cc |
+----+------+
. R4 n, |% |9 p& r! s' b3 rows in set

---

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

mysql> delete from student where id not in (select min(id) from student group by name);
0 U( r4 {, e4 A8 g  H) R! w执行报错：1093 - You can't specify target table 'student' for update in FROM clause
- ?+ W( ?! q( |4 H% X6 z- y原因是：更新数据时使用了查询，而查询的数据又做了更新的条件，mysql不支持这种方式。
8 }1 Q# B9 ^" O1 `( L怎么规避这个问题？
再加一层封装，如下：
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;
# ?/ x2 {) G2 X6 N4 L0 v+----+------+
! \1 l" q  C3 G$ h! S( l3 [| ID | NAME |
+----+------+
| 11 | aa |
( K( s: m4 c$ t& d. U| 13 | bb |
| 16 | cc |
, D* q" l, K; }0 Y6 y& V* {5 h+----+------+
3 rows in set

% ], c6 D0 O9 X方法三例如：delete from bugdata where id not in (select minid from (select min(id) as minid from bugdata group by qxbh) b);
' {3 ]5 `# y0 ]  b/ C' F