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

admin

方法1：
+ f. X: W$ R% K6 H9 h1、创建一个临时表，选取需要的数据。
2、清空原表。
/ e7 _% p" O. k3、临时表数据导入到原表。
4、删除临时表。
mysql> select * from student;
+----+------+
( [! x5 a7 f4 u" j" ~| ID | NAME |
+----+------+
| 11 | aa |
| 12 | aa |
| 13 | bb |
6 M) d1 D% _5 S# G| 14 | bb |
| 15 | bb |
0 \3 o5 z5 S/ X7 ]8 ^5 P; V| 16 | cc |
7 r9 V1 l5 V" I/ C5 d8 m+----+------+
6 rows in set

mysql> create temporary table temp as select min(id),name from student group by name;
3 H. K1 E* o3 D# E% cQuery 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;
5 w1 W2 y8 ~+ h% F2 H1 dQuery OK, 3 rows affected
9 m7 N: k1 p. K; U/ b8 `Records: 3 Duplicates: 0 Warnings: 0

mysql> select * from student;
, T3 p, {7 B  o' U+----+------+
| ID | NAME |
+----+------+
' R5 E- n/ @3 B- c, f. m; K. J| 11 | aa |
& ~* y. S( n$ |7 _. S9 `: ]0 ?| 13 | bb |
" r& h) ]+ h6 @1 f  }: p! [' b| 16 | cc |
9 N; @* C: f0 j* d+----+------+
5 |7 n3 C- [# u) w$ s3 rows in set

mysql> drop temporary table temp;
Query OK, 0 rows affected
! P8 l& I" a$ `7 @% i7 C; F- N' ]( S这个方法，显然存在效率问题。

---

方法2：按name分组，把最小的id保存到临时表，删除id不在最小id集合的记录，如下：
- _2 v! p3 ]% G: Mmysql> create temporary table temp as select min(id) as MINID from student group by name;
Query OK, 3 rows affected
. j4 u7 [! B7 v& ]Records: 3 Duplicates: 0 Warnings: 0

mysql> delete from student where id not in (select minid from temp);
7 g* s! H. @/ O* J6 P2 nQuery OK, 3 rows affected

mysql> select * from student;
# ]% U$ X( x! d5 }+----+------+
4 `, h. U4 l: `+ s' q( K| ID | NAME |
) K" [$ S, D( b/ _& P  r/ w+----+------+
. w* }+ _( x; _0 o' \| 11 | aa |
. Q; R- {% t* n! N0 B. E| 13 | bb |
| 16 | cc |
+----+------+
; s$ J6 j3 S6 n3 L7 x& J3 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不支持这种方式。
: D  N  |1 O' u. N* E* A怎么规避这个问题？
- `- x1 I7 Y) C6 ?7 M! q0 t再加一层封装，如下：
0 j* U$ U. ^* e6 fmysql> 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;
+----+------+
" N9 h& Y: J) }$ U2 U9 U9 f| ID | NAME |
4 B2 f6 r7 v' Q! O+----+------+
| 11 | aa |
| 13 | bb |
& M/ v  u7 Q0 g  B| 16 | cc |
+----+------+
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- x2 X3 S6 n
" P% l7 C9 C7 \7 t2 t
3 f! R4 N; t2 R3 W8 D( N. f0 }3 ?