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

admin

方法1：
! a" ]) C( R7 _0 C$ y1、创建一个临时表，选取需要的数据。
% Y- @" W8 @' v2、清空原表。
: f; U$ j' p0 k3、临时表数据导入到原表。
  ]5 G' N! M8 s( _0 h- z4 U& C4、删除临时表。
mysql> select * from student;
0 C% U$ i8 m- N* Y1 _+----+------+
8 m% s8 Y) z$ ^| ID | NAME |
+----+------+
| 11 | aa |
, P" p3 }( V/ Q8 G7 Q2 P7 W| 12 | aa |
/ l; B0 N" T8 t8 w# S1 A! }| 13 | bb |
| 14 | bb |
: A' W3 a. g" h3 Q| 15 | bb |
| 16 | cc |
% M2 i. y2 J, E6 E& k+----+------+
' B2 q/ w. n  P2 T5 ^. U8 F1 J( V6 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;
Query OK, 3 rows affected
) u% `8 I' x6 [Records: 3 Duplicates: 0 Warnings: 0

mysql> select * from student;
1 p8 n4 z5 [' p# G3 ?6 _% x2 s/ V+ z+----+------+
! ^( v; A& o' f4 }7 i+ g| ID | NAME |
+----+------+
4 E- W$ N# Y! {9 [) T| 11 | aa |
, l. Z2 ~0 `0 n0 o  W0 r2 K| 13 | bb |
| 16 | cc |
+----+------+
& K, W9 t6 ~0 V! w% t3 rows in set

mysql> drop temporary table temp;
Query OK, 0 rows affected
这个方法，显然存在效率问题。

---

方法2：按name分组，把最小的id保存到临时表，删除id不在最小id集合的记录，如下：
# z7 G, F2 ?. S5 |$ n! imysql> create temporary table temp as select min(id) as MINID from student group by name;
Query OK, 3 rows affected
$ X2 R0 u" \# }% IRecords: 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;
+----+------+
* t! p7 x$ L* i: y2 V| ID | NAME |
+----+------+
| 11 | aa |
| 13 | bb |
7 k, X- V% x) s8 P2 `6 k| 16 | cc |
0 N$ O" Y" J% J# `3 S3 x; H: u3 M+ n/ y+----+------+
3 rows in set

---

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

mysql> delete from student where id not in (select min(id) from student group by name);
. X4 T3 h: E' R! k; q4 u9 c执行报错：1093 - You can't specify target table 'student' for update in FROM clause
" C1 e$ a1 K( q原因是：更新数据时使用了查询，而查询的数据又做了更新的条件，mysql不支持这种方式。
怎么规避这个问题？
: Y+ l6 H* g% K% T  I0 b0 a再加一层封装，如下：
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;
+----+------+
7 j. {9 E# v6 s" e# W/ e| ID | NAME |
+----+------+
| 11 | aa |
| 13 | bb |
# ]$ o. f- t. }5 a& o: ~, u' [| 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);


8 l8 q9 g3 o4 f. m