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

admin

方法1：
: U' }2 n+ e3 C% {2 E3 ?1、创建一个临时表，选取需要的数据。
2、清空原表。
3、临时表数据导入到原表。
/ X+ n3 r4 A3 Y) `7 z9 U. a7 H5 J# m4、删除临时表。
* S3 }2 p; v- g2 e( h7 }7 nmysql> select * from student;
+----+------+
+ j, v. O, Y6 _0 x# f& X5 m) z+ n$ x| ID | NAME |
+----+------+
| 11 | aa |
| 12 | aa |
! u7 T- u2 p2 d. }1 Q, S| 13 | bb |
| 14 | bb |
| 15 | bb |
| 16 | cc |
4 S; `( U) }% b+----+------+
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;
Query OK, 3 rows affected
Records: 3 Duplicates: 0 Warnings: 0

mysql> select * from student;
- o% x- J7 w& K) i4 y+----+------+
- b# o* |+ {# ^6 _) u0 d' |+ V| ID | NAME |
+----+------+
| 11 | aa |
| 13 | bb |
| 16 | cc |
+----+------+
7 Z0 `' @, C( v9 q6 n& b3 rows in set

mysql> drop temporary table temp;
Query OK, 0 rows affected
+ }8 B7 J' j3 I5 l+ q* {- o, U这个方法，显然存在效率问题。

---

方法2：按name分组，把最小的id保存到临时表，删除id不在最小id集合的记录，如下：
2 h" Q. [3 a1 F, Y# l5 @mysql> create temporary table temp as select min(id) as MINID from student group by name;
+ H0 F7 C5 F# u4 cQuery OK, 3 rows affected
Records: 3 Duplicates: 0 Warnings: 0

mysql> delete from student where id not in (select minid from temp);
* a9 I  P- V# X  QQuery OK, 3 rows affected

mysql> select * from student;
& G+ E, h* g# s$ O; O( B1 X+----+------+
| ID | NAME |
+----+------+
" F! I3 g& J: e: x| 11 | aa |
| 13 | bb |
| 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
7 P7 \" n& m- ?5 D( V  B原因是：更新数据时使用了查询，而查询的数据又做了更新的条件，mysql不支持这种方式。
怎么规避这个问题？
再加一层封装，如下：
; d( ^  ~! j* ]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;
+----+------+
| ID | NAME |
+----+------+
| 11 | aa |
& o- M# \- u( ^4 Q. r| 13 | bb |
| 16 | cc |
9 r5 m+ i/ c% H2 _1 `- _  y+----+------+
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);
1 d& V9 n  q1 k1 a7 @8 k0 c