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

admin

方法1：
8 k8 u0 W, L( |' U0 x: ]/ j1、创建一个临时表，选取需要的数据。
2、清空原表。
3、临时表数据导入到原表。
4、删除临时表。
: b. M( Z. z$ I  R. C9 S- nmysql> select * from student;
+----+------+
| ID | NAME |
2 t3 i" J9 P. Z! w( l4 K2 c+----+------+
6 q1 q2 f' p1 [6 Y1 M2 ~| 11 | aa |
| 12 | aa |
8 Q/ R! ^' ?+ P| 13 | bb |
2 Z2 B' k* Y% j! a1 ]/ x| 14 | bb |
| 15 | bb |
| 16 | cc |
# R( P2 e1 \; E# h+----+------+
' R& E8 `/ v5 e! A9 M1 M6 rows in set

mysql> create temporary table temp as select min(id),name from student group by name;
' o6 z7 G5 M. XQuery OK, 3 rows affected
$ j1 D& L: v+ j; L# q; y" h! e5 E4 TRecords: 3 Duplicates: 0 Warnings: 0

mysql> truncate table student;
) a  V7 e5 o- z5 P* n( D7 O4 mQuery OK, 0 rows affected

mysql> insert into student select * from temp;
  z4 L( f, Y  U% D$ X! rQuery OK, 3 rows affected
Records: 3 Duplicates: 0 Warnings: 0

mysql> select * from student;
* r7 E) G' J  i3 ~- z+ V8 V+----+------+
9 T- d% {8 X$ L  @$ X3 A6 x" T, J| ID | NAME |
% A4 d; R! x. |9 f+----+------+
| 11 | aa |
1 g6 G) x* p' @$ \. ^! e| 13 | bb |
| 16 | cc |
+----+------+
3 rows in set

mysql> drop temporary table temp;
# U9 U# f' ?( i; ZQuery OK, 0 rows affected
7 \1 S" Q/ F( @. u8 B这个方法，显然存在效率问题。

---

方法2：按name分组，把最小的id保存到临时表，删除id不在最小id集合的记录，如下：
9 N8 e. K2 s8 M  G1 H- @& k$ _0 o1 Dmysql> create temporary table temp as select min(id) as MINID from student group by name;
0 @4 B8 @' r  n8 w4 o' i$ LQuery OK, 3 rows affected
Records: 3 Duplicates: 0 Warnings: 0

mysql> delete from student where id not in (select minid from temp);
0 X4 I) @+ d0 h8 o% M* u# W+ i& }+ _Query OK, 3 rows affected

mysql> select * from student;
+----+------+
, w, h7 X9 D9 o9 ]* K' |' z| ID | NAME |
+----+------+
" b0 F; v0 j; Y9 ^. s| 11 | aa |
| 13 | bb |
  S9 ?0 [) D. m, C) k4 o/ || 16 | cc |
+----+------+
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不支持这种方式。
6 D0 I' K) x( A8 f- y$ z怎么规避这个问题？
再加一层封装，如下：
0 Y4 j% q/ G+ k# p* o9 b4 _mysql> delete from student where id not in (select minid from (select min(id) as minid from student group by name) b);
$ o  {8 {. g7 M8 q% Z" YQuery OK, 3 rows affected

mysql> select * from student;
$ @; E( K+ ~9 g2 e; j! K+----+------+
+ W3 [0 ]& d8 ?4 v: ?3 p9 e( a: c| ID | NAME |
+----+------+
| 11 | aa |
: m8 G/ l& j1 Z* ~; s| 13 | bb |
| 16 | cc |
: r3 {- ~  T. F# m+----+------+
7 c, }7 p8 W0 R: y' C5 _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);
  w' M+ p: Z: s" {: p

; y, `1 _. j6 R. m. V4 {# n1 w