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

admin

方法1：
( n6 F& J9 C4 E. `# O) t9 A% [1、创建一个临时表，选取需要的数据。
2、清空原表。
3、临时表数据导入到原表。
4、删除临时表。
; ?. H1 c+ u: mmysql> select * from student;
% q* h, a& l. D6 s: d8 r6 S! k% Y  }+----+------+
| ID | NAME |
4 ^) S8 F: s# C+----+------+
. u. g3 }1 ?: _$ s| 11 | aa |
| 12 | aa |
| 13 | bb |
| 14 | bb |
| 15 | bb |
7 X" P: Q0 y6 H| 16 | cc |
+----+------+
; b: k; n: A% ~) _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;
, |0 @' U' F- K! c. V& {2 Y: JQuery OK, 3 rows affected
" c' u- F; _: I( C8 {6 aRecords: 3 Duplicates: 0 Warnings: 0

mysql> select * from student;
0 `$ |$ F% N/ j) @' l4 E+----+------+
3 o; {& L) L2 K2 g7 M  m| ID | NAME |
+----+------+
! @8 a( p; e2 l* E' E" L- c) h' Y( J| 11 | aa |
| 13 | bb |
| 16 | cc |
+----+------+
( \4 @+ O# X& W2 E  `3 rows in set

mysql> drop temporary table temp;
Query OK, 0 rows affected
; k3 {6 d+ {7 i: ]这个方法，显然存在效率问题。

---

方法2：按name分组，把最小的id保存到临时表，删除id不在最小id集合的记录，如下：
2 L4 @+ C( ]2 E+ R8 fmysql> create temporary table temp as select min(id) as MINID from student group by name;
Query OK, 3 rows affected
4 H3 X& v. A4 j7 f# QRecords: 3 Duplicates: 0 Warnings: 0

mysql> delete from student where id not in (select minid from temp);
  y' M4 w# o, S2 J) G! r1 L9 M3 mQuery OK, 3 rows affected

mysql> select * from student;
+----+------+
8 P3 u3 s2 D* B, G; v3 D! G9 O| ID | NAME |
+----+------+
, N  p% O, b) m" x, T0 c7 i| 11 | aa |
| 13 | bb |
5 z4 k( f1 C& G9 o1 \| 16 | cc |
: ~# T3 ?1 ~. m) w& l  _+ |+----+------+
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
+ j' t/ ]) x) a2 O' J& G原因是：更新数据时使用了查询，而查询的数据又做了更新的条件，mysql不支持这种方式。
" j) ]& z) ~( ?; e1 x9 X; e4 l: l怎么规避这个问题？
4 [- ]  C% A4 |# w+ _2 j% i再加一层封装，如下：
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;
* y9 |$ J) |1 D, K; R: ~+----+------+
' t6 _! S" z3 `7 J* v$ F6 t6 R| ID | NAME |
+----+------+
| 11 | aa |
| 13 | bb |
| 16 | cc |
+----+------+
( b3 I* m% X3 l) C3 rows in set

方法三例如：delete from bugdata where id not in (select minid from (select min(id) as minid from bugdata group by qxbh) b);
+ v7 w9 I$ b1 K1 k" Q5 ?

: t: s7 a$ f; I7 G5 V