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

admin

方法1：
" w; _. `* I7 K6 e$ K' Z1、创建一个临时表，选取需要的数据。
2、清空原表。
3、临时表数据导入到原表。
4、删除临时表。
3 v( a0 M( G( h; J7 D% m! W& P' ]mysql> select * from student;
5 V; O1 e" d7 q8 H) s( D! ]' f+----+------+
* b! B, ?2 \: Q1 D* B| ID | NAME |
+----+------+
) \) ]! E  V0 z) p" j# a! l| 11 | aa |
+ A* w  @% b) }1 G) |# Q| 12 | aa |
. ?# x5 S- ^# @% H- t9 l| 13 | bb |
* Q, z9 B5 ~, z+ a| 14 | bb |
| 15 | bb |
$ X; K0 b( s& L  g| 16 | cc |
' b+ Q) |4 {  ~' `' A2 Y3 |4 f" o+----+------+
* T) O+ a5 O* w1 j6 rows in set

mysql> create temporary table temp as select min(id),name from student group by name;
Query OK, 3 rows affected
6 D0 p, Y- p. t. \" M0 z/ }Records: 3 Duplicates: 0 Warnings: 0

mysql> truncate table student;
2 {% n% H* g0 F0 F8 o% p* JQuery OK, 0 rows affected

mysql> insert into student select * from temp;
Query OK, 3 rows affected
7 r4 z3 C5 y6 ^8 _  V1 B+ kRecords: 3 Duplicates: 0 Warnings: 0

mysql> select * from student;
: o# U# N) c3 z. W& S; f5 Z) _. y+ F+----+------+
3 O$ g' X0 y, Q; f$ T| ID | NAME |
. a5 l* P( c  Y3 B, p+----+------+
( E( S0 u" P) K6 x( K! w* l| 11 | aa |
& p6 p3 _; _! D2 c& W9 t0 N1 h) Y| 13 | bb |
| 16 | cc |
! R& E* g# T! \+----+------+
' _- O$ ~  D  Q; g" _/ A3 rows in set

mysql> drop temporary table temp;
Query OK, 0 rows affected
这个方法，显然存在效率问题。

---

方法2：按name分组，把最小的id保存到临时表，删除id不在最小id集合的记录，如下：
! P6 @- a: y& \4 zmysql> create temporary table temp as select min(id) as MINID from student group by name;
6 G4 |* H2 p6 }+ h4 i0 a0 lQuery 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;
2 _# Z: Q6 X4 E7 w+----+------+
| ID | NAME |
( _9 s$ E1 }' E/ [; z- x+----+------+
' y% j9 Z; }+ |1 y8 D| 11 | aa |
9 I# h4 n5 z- q, l+ u0 Y$ N2 q| 13 | bb |
| 16 | cc |
+----+------+
) p0 z  [: q1 P" ?4 h8 `' I' V: d3 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不支持这种方式。
% [, `- _2 H  b1 u, A怎么规避这个问题？
2 v' q3 l2 v; s再加一层封装，如下：
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;
0 P; E1 p7 t' ~' I3 p+----+------+
| ID | NAME |
0 M$ ?/ q$ [4 q9 x. p# V; v. b+----+------+
& v0 M6 J6 k$ E, H4 X5 f, H| 11 | aa |
| 13 | bb |
| 16 | cc |
. D) P' k; l" x- A- r" ]. C: z+----+------+
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);


/ q8 u9 W0 A) ?0 \