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

admin

方法1：
: X; F3 ]) Z0 M% R( d& n- u+ G1、创建一个临时表，选取需要的数据。
2、清空原表。
2 d- F" m/ K! u3、临时表数据导入到原表。
4、删除临时表。
9 U! N% ^- Q+ c; omysql> select * from student;
+----+------+
| ID | NAME |
% `8 U. B  s( h/ b* P* ]1 }8 R+----+------+
| 11 | aa |
| 12 | aa |
! U: ^1 y. K/ @# P2 O| 13 | bb |
& ], v2 B# ^( T0 T| 14 | bb |
2 u$ Q# h# u" c. B% }8 P| 15 | bb |
" k! ]/ F9 e) `, H# @| 16 | cc |
+----+------+
6 rows in set

mysql> create temporary table temp as select min(id),name from student group by name;
4 q) `5 ~; ]4 \; i5 V1 j5 i% V( }Query OK, 3 rows affected
) `; k; y% q) K* _  U4 ?9 y$ eRecords: 3 Duplicates: 0 Warnings: 0

mysql> truncate table student;
3 P/ s$ Z4 r, B8 xQuery OK, 0 rows affected

mysql> insert into student select * from temp;
2 s7 M% A/ D, q- ~  r. f7 {* gQuery OK, 3 rows affected
0 p$ A. x( Q! h5 b5 R2 |& q+ JRecords: 3 Duplicates: 0 Warnings: 0

mysql> select * from student;
( L/ ]/ I6 J, J# K0 M+----+------+
| ID | NAME |
2 ~1 W" B, c, n$ ~& D+----+------+
| 11 | aa |
| 13 | bb |
9 I) n" G2 h, S) j% |/ ~| 16 | cc |
$ _1 S) V3 W+ c0 s( ]5 z+----+------+
3 rows in set

mysql> drop temporary table temp;
' D5 S  K! z  J& X7 xQuery OK, 0 rows affected
9 g" D$ m: d& G) J9 E2 L+ V这个方法，显然存在效率问题。

---

方法2：按name分组，把最小的id保存到临时表，删除id不在最小id集合的记录，如下：
1 O7 V$ d# K6 W4 D9 u6 b5 Cmysql> create temporary table temp as select min(id) as MINID from student group by name;
Query OK, 3 rows affected
Records: 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;
8 z4 F% Z, y# Q! ^. k; M2 I+----+------+
4 |* T$ \! }+ F| ID | NAME |
# C( W* P3 L6 }6 P+----+------+
| 11 | aa |
| 13 | bb |
7 ^, Y; s3 b: y) W! I2 L| 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
' y4 t* U$ @9 E4 |  \; \) S* _原因是：更新数据时使用了查询，而查询的数据又做了更新的条件，mysql不支持这种方式。
+ H1 L, S# A% l% r怎么规避这个问题？
再加一层封装，如下：
mysql> delete from student where id not in (select minid from (select min(id) as minid from student group by name) b);
" p5 m+ ^; L( \7 l; U( KQuery OK, 3 rows affected

mysql> select * from student;
+----+------+
6 ]) W: ?/ M1 ^- a6 k& z& Y+ N| ID | NAME |
+----+------+
1 `2 E1 L8 b' X1 E3 V: P  s| 11 | aa |
| 13 | bb |
| 16 | cc |
+----+------+
: G4 t- K% K' E0 n0 l3 rows in set

方法三例如：delete from bugdata where id not in (select minid from (select min(id) as minid from bugdata group by qxbh) b);
# |6 m/ c8 W2 q/ i
' T! M8 K7 N8 a. ~" C! ?# `+ X
8 x# N6 L: ?+ g$ Z$ T