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

admin

方法1：
1、创建一个临时表，选取需要的数据。
2、清空原表。
3、临时表数据导入到原表。
4、删除临时表。
mysql> select * from student;
! o4 z& j5 A/ q+----+------+
% w/ D: O% A' ]" K/ V| ID | NAME |
2 W9 z* G. j' T( p! H) X3 t: t+----+------+
, i, x$ P7 o  `1 k4 p| 11 | aa |
| 12 | aa |
| 13 | bb |
. j6 o8 h5 d6 Q. o| 14 | bb |
. R8 y5 t) w" Z+ x8 D| 15 | bb |
1 l( y5 V! M; R! e7 R| 16 | cc |
" Z5 [) F& B; W/ k% G: H5 f  e+----+------+
" s/ R( M( [) F2 e6 rows in set

mysql> create temporary table temp as select min(id),name from student group by name;
Query OK, 3 rows affected
& a% r1 Q; D3 U8 ~Records: 3 Duplicates: 0 Warnings: 0

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

mysql> insert into student select * from temp;
Query OK, 3 rows affected
5 [  L& O( h3 G* yRecords: 3 Duplicates: 0 Warnings: 0

mysql> select * from student;
+----+------+
| ID | NAME |
+----+------+
# g& G& l# p& N- j& P| 11 | aa |
* p, H/ U8 T* E  Z5 ~- ?& p: m. O| 13 | bb |
| 16 | cc |
% X0 }6 U% ^' G0 W# Y* |3 }" @+----+------+
1 [: x+ @$ j4 r3 rows in set

mysql> drop temporary table temp;
6 N! Z9 u& k+ Y: w. m- QQuery OK, 0 rows affected
这个方法，显然存在效率问题。

---

方法2：按name分组，把最小的id保存到临时表，删除id不在最小id集合的记录，如下：
8 c& e+ O2 G) Cmysql> create temporary table temp as select min(id) as MINID from student group by name;
/ x; |( l' k- r0 j" a4 q% NQuery OK, 3 rows affected
. C3 K* R( v) ~" E4 b% t6 VRecords: 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;
3 {/ n( Z: V4 b- W+----+------+
# |5 a: }  P. f( I| ID | NAME |
+----+------+
| 11 | aa |
| 13 | bb |
| 16 | cc |
+----+------+
: Z" R8 ]& @" S( ]* m( D4 [1 m3 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不支持这种方式。
5 N6 [* ?2 Y  r4 x  _8 _* x  D怎么规避这个问题？
6 n  s1 x# [6 x) i+ g5 m9 ]( g再加一层封装，如下：
mysql> delete from student where id not in (select minid from (select min(id) as minid from student group by name) b);
' c% N# ~: m7 `" R+ A* r  }  aQuery OK, 3 rows affected

mysql> select * from student;
+----+------+
) H3 F( X$ B, I$ M. J. Z| ID | NAME |
+----+------+
' A% M& t4 \/ |' ~7 z| 11 | aa |
| 13 | bb |
' o3 h5 L5 S$ c6 G* l0 V| 16 | cc |
( {# x, f  Z6 Y7 o' }3 s+----+------+
. |4 u) d1 y. G7 ]7 }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 u$ l1 y5 T) y) W5 U4 {, B% `: T1 o( h