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

admin

方法1：
1、创建一个临时表，选取需要的数据。
( |0 w0 |1 ]' y( m5 z8 }3 _7 M: A2、清空原表。
* y1 b% I. q6 i: O: Y4 H3、临时表数据导入到原表。
! p1 x) V. _, I" b; i4、删除临时表。
mysql> select * from student;
7 M5 h2 ]) N' J$ S; A; x0 F+----+------+
| ID | NAME |
+----+------+
| 11 | aa |
| 12 | aa |
| 13 | bb |
$ ~  f5 p, r! U| 14 | bb |
0 e& }5 q  [0 M2 h1 D( h% f| 15 | bb |
| 16 | cc |
  }% a! J4 f3 Y6 @; X, G+----+------+
6 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;
Query OK, 0 rows affected

mysql> insert into student select * from temp;
: Z8 P/ Z' v9 e, X) O" RQuery OK, 3 rows affected
8 i! j2 _; W, TRecords: 3 Duplicates: 0 Warnings: 0

mysql> select * from student;
$ h7 O  y9 q) s. D' v+ N+----+------+
| ID | NAME |
( @" F, q$ C' e# V  I9 `+----+------+
. G" _% h# |, p5 v8 O+ }| 11 | aa |
" j; T& X8 \6 b/ y) j| 13 | bb |
| 16 | cc |
+----+------+
/ |+ c* \- O$ q; v# E  E6 e3 rows in set

mysql> drop temporary table temp;
% K% @# X! M) a# U, A* ]! @6 lQuery OK, 0 rows affected
这个方法，显然存在效率问题。

---

方法2：按name分组，把最小的id保存到临时表，删除id不在最小id集合的记录，如下：
; M& ~/ g  H6 s4 o4 Y/ Vmysql> create temporary table temp as select min(id) as MINID from student group by name;
Query OK, 3 rows affected
+ x& w4 j4 s8 w0 Y0 ~Records: 3 Duplicates: 0 Warnings: 0

mysql> delete from student where id not in (select minid from temp);
; I( o3 ^6 O  R+ J' MQuery OK, 3 rows affected

mysql> select * from student;
+----+------+
| ID | NAME |
* c# e5 j4 W; u8 A2 I+----+------+
| 11 | aa |
2 g) B* ~" @. F| 13 | bb |
| 16 | cc |
+----+------+
3 rows in set

---

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

mysql> delete from student where id not in (select min(id) from student group by name);
$ r. P' A# l4 Z执行报错：1093 - You can't specify target table 'student' for update in FROM clause
. n9 g# x2 z7 l. h5 }1 R原因是：更新数据时使用了查询，而查询的数据又做了更新的条件，mysql不支持这种方式。
8 Q1 E8 _/ M7 O3 n怎么规避这个问题？
" z# ^4 D7 q; H6 A9 [6 z再加一层封装，如下：
mysql> delete from student where id not in (select minid from (select min(id) as minid from student group by name) b);
' ]5 G' A1 c, H8 b2 V, UQuery OK, 3 rows affected

mysql> select * from student;
+----+------+
1 Y$ [! C0 C' g$ G7 p- n8 [| ID | NAME |
: g8 a( I1 J/ c+----+------+
( y/ K4 \! |8 O5 ^* l( [: k" p| 11 | aa |
8 T* P! G- Q5 {+ o| 13 | bb |
| 16 | cc |
0 }9 F3 n* d7 j, R0 K/ \6 m5 Z+----+------+
- c8 n1 J3 h9 j& R" q3 rows in set

  O9 S2 u0 a' v3 @! d$ [方法三例如：delete from bugdata where id not in (select minid from (select min(id) as minid from bugdata group by qxbh) b);
5 g0 g2 J* N2 l9 [' Z
* x* C: [. M6 ~4 W1 Q+ Z1 X