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

admin

方法1：
! b1 ]" f3 n/ `$ h1、创建一个临时表，选取需要的数据。
" ]/ O. c1 z0 P* m% b' @2、清空原表。
+ N. _" O9 O$ W: d: H3、临时表数据导入到原表。
4、删除临时表。
- [3 R1 G% B0 \* F8 K1 cmysql> select * from student;
+----+------+
| ID | NAME |
+----+------+
| 11 | aa |
7 ~. c- q0 W* u1 s| 12 | aa |
| 13 | bb |
3 E/ p3 @5 k' E  b| 14 | bb |
| 15 | bb |
| 16 | cc |
+----+------+
6 rows in set

mysql> create temporary table temp as select min(id),name from student group by name;
% [( e; |/ L1 R6 r" y' O/ sQuery OK, 3 rows affected
Records: 3 Duplicates: 0 Warnings: 0

mysql> truncate table student;
! @! `) S5 C! U6 Y" O% y( GQuery OK, 0 rows affected

mysql> insert into student select * from temp;
7 o" [' n+ m# a2 n- X' ~Query OK, 3 rows affected
Records: 3 Duplicates: 0 Warnings: 0

mysql> select * from student;
1 `5 Z" l. w5 a+----+------+
2 z& [- c+ V! _, A# N! D& i0 a; I| ID | NAME |
5 k; T& ^% b6 s! U5 V8 ]3 O/ I2 b. G5 k+----+------+
8 h2 i& K0 u% o3 M' @| 11 | aa |
| 13 | bb |
! v1 j5 r; h& j. l$ j| 16 | cc |
+----+------+
! S# [! ^( b9 a: a+ X, H6 o3 rows in set

mysql> drop temporary table temp;
Query OK, 0 rows affected
, S& K7 r2 Y, c/ p这个方法，显然存在效率问题。

---

方法2：按name分组，把最小的id保存到临时表，删除id不在最小id集合的记录，如下：
mysql> create temporary table temp as select min(id) as MINID from student group by name;
Query OK, 3 rows affected
* \8 M7 ~- A' H  YRecords: 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;
+----+------+
9 B1 |+ ?7 p5 N2 `6 p/ r& K| ID | NAME |
+----+------+
7 v! J! \* p0 i* g- ~| 11 | aa |
4 [; q) t; u! Z' Y% [| 13 | bb |
| 16 | cc |
5 l! ~  H2 C) f6 ~& G/ Q. o$ Z+----+------+
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
; M, m+ |' r  t原因是：更新数据时使用了查询，而查询的数据又做了更新的条件，mysql不支持这种方式。
5 q( @! `" }" E) R( f6 P$ ?' M" X怎么规避这个问题？
再加一层封装，如下：
4 Y* Y0 P# \; |+ g( hmysql> delete from student where id not in (select minid from (select min(id) as minid from student group by name) b);
" X$ p+ M  W" v0 xQuery OK, 3 rows affected

mysql> select * from student;
+----+------+
: Q2 t  s' E0 _" v6 G| ID | NAME |
+----+------+
  _# p% u1 ?1 `; {( ]! b' c9 a% k$ u7 U| 11 | aa |
  O! D( w( A# p+ U5 r2 E2 D6 [9 K| 13 | bb |
| 16 | cc |
+----+------+
8 D  o2 ^+ ^9 ~6 F3 rows in set

1 [" z% J. m. ]* D& \7 ]' l方法三例如：delete from bugdata where id not in (select minid from (select min(id) as minid from bugdata group by qxbh) b);


6 Q9 A. R6 Y7 C3 X