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

admin

方法1：
1、创建一个临时表，选取需要的数据。
: M1 j1 w! H! L1 v2、清空原表。
3、临时表数据导入到原表。
9 z* M0 R( B9 a5 T- h4、删除临时表。
: o4 n$ L  r) L  J, t# c9 jmysql> select * from student;
" B; d2 }3 M8 w* h' P" ~' ^1 g+----+------+
| ID | NAME |
9 s' w# S; C% H& r" s+----+------+
| 11 | aa |
| 12 | aa |
9 e& }+ ]- f$ u: S| 13 | bb |
/ x1 M" _% ]1 H| 14 | bb |
| 15 | bb |
| 16 | cc |
1 k% x  y% A/ g8 C5 t% A& s+ S# s, d+----+------+
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;
  O% J0 n. U5 }- MQuery OK, 3 rows affected
/ T# L% q2 w" _6 Q( nRecords: 3 Duplicates: 0 Warnings: 0

mysql> select * from student;
+----+------+
| ID | NAME |
+----+------+
" N1 l) R8 x6 ~2 Q| 11 | aa |
" R5 |( n4 J4 {, \2 M% z| 13 | bb |
| 16 | cc |
4 `) r5 K" M# k0 t* L+----+------+
3 rows in set

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

---

方法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
7 q: W$ G+ a& c& X% G+ p3 C0 GRecords: 3 Duplicates: 0 Warnings: 0

mysql> delete from student where id not in (select minid from temp);
$ r5 }) B- @8 E% h0 V' L$ _Query OK, 3 rows affected

mysql> select * from student;
3 Z* C  X; H$ y+----+------+
: N3 ?1 X3 Z" I/ e; G| ID | NAME |
: A" {& j) Z: o. x0 d  C' h+----+------+
* R" @  T' W* y5 T| 11 | aa |
. G1 M6 y: A! T5 g6 s8 o| 13 | bb |
. @+ G- f- M! s6 y6 \| 16 | cc |
+----+------+
3 rows in set

---

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

mysql> delete from student where id not in (select min(id) from student group by name);
( ~6 L* e* Q# J* h执行报错：1093 - You can't specify target table 'student' for update in FROM clause
原因是：更新数据时使用了查询，而查询的数据又做了更新的条件，mysql不支持这种方式。
: `; j7 b2 L' T* B. t; O怎么规避这个问题？
2 N; p) i& e5 l! E; F$ c再加一层封装，如下：
9 A1 ~6 m; B1 p. ymysql> 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 |
8 y; r6 z6 E# r9 b3 m) V+----+------+
7 i4 E) u  B3 o6 i9 q) ]| 11 | aa |
5 y  B+ V5 G3 T/ ]! G  u' p1 M# j2 {| 13 | bb |
| 16 | cc |
+----+------+
7 S! H  ^1 w# ?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 J; U- B" ~0 k+ Y/ m5 Q