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

admin

方法1：
1、创建一个临时表，选取需要的数据。
, m+ W; A. N! E) C2、清空原表。
0 ?! o# Y3 V; m! U3、临时表数据导入到原表。
) o& @* \1 B3 f% j' T% N' V4、删除临时表。
5 g5 J8 s. ?( |5 R, _* M8 `mysql> select * from student;
0 q( f# S0 j- S. u- s+----+------+
| ID | NAME |
+----+------+
  g8 i2 Y, I% C, S" G7 ?4 G| 11 | aa |
' `% s! U2 n; |+ w. r| 12 | aa |
| 13 | bb |
| 14 | bb |
| 15 | bb |
1 q5 F$ R: A# Z8 M$ z| 16 | cc |
2 e" L7 L7 `  x/ P3 ^+----+------+
8 F. c! _6 T2 E/ x8 R6 rows in set

mysql> create temporary table temp as select min(id),name from student group by name;
* b7 @* D! k7 J- b* RQuery 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;
Query OK, 3 rows affected
, @$ D* n( x$ S& u( bRecords: 3 Duplicates: 0 Warnings: 0

mysql> select * from student;
7 D/ c( v' B# i* f# Y2 v) U+----+------+
| ID | NAME |
+----+------+
| 11 | aa |
| 13 | bb |
. }8 p. l  ?. Z8 P' L/ a| 16 | cc |
! ]' Q. _( k  b7 O3 @# G, b) P+----+------+
3 rows in set

mysql> drop temporary table temp;
9 n$ _. Z2 K% S( E: |; U1 _7 |$ |3 fQuery OK, 0 rows affected
; B2 a- y! X* u: ~; a这个方法，显然存在效率问题。

---

方法2：按name分组，把最小的id保存到临时表，删除id不在最小id集合的记录，如下：
1 r" J. R% N: O0 {; M0 ?5 @$ U6 j% hmysql> create temporary table temp as select min(id) as MINID from student group by name;
Query OK, 3 rows affected
8 E* m- e$ Z0 s9 C" y. qRecords: 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;
+----+------+
7 z: X7 J* ^' ]$ ~3 N/ l5 ^: ]5 _) L/ t| ID | NAME |
+----+------+
# @! k. ]5 o4 S/ T3 R7 n% e# ~| 11 | aa |
| 13 | bb |
| 16 | cc |
+----+------+
- F4 T8 |0 H: v3 rows in set

---

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

mysql> delete from student where id not in (select min(id) from student group by name);
8 k: l9 L  |( R% {* d' T执行报错：1093 - You can't specify target table 'student' for update in FROM clause
原因是：更新数据时使用了查询，而查询的数据又做了更新的条件，mysql不支持这种方式。
; E" L7 l0 I5 h怎么规避这个问题？
- F* X2 ]8 Y, o再加一层封装，如下：
mysql> delete from student where id not in (select minid from (select min(id) as minid from student group by name) b);
$ @) E' m3 O2 f* dQuery OK, 3 rows affected

mysql> select * from student;
) w7 A0 K7 L4 Y6 }' m/ v+----+------+
7 W* [4 u/ W6 F& ~3 n7 ~5 [| ID | NAME |
4 R  G( ?) ^# w* b2 ~& M* y: {+----+------+
| 11 | aa |
| 13 | bb |
| 16 | cc |
- N& _# Z# q) w! `+----+------+
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);

( G9 }5 W2 E! P! I" S