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

admin

方法1：
1、创建一个临时表，选取需要的数据。
2、清空原表。
6 O# Y" R3 f. `! N" c3、临时表数据导入到原表。
5 U) Q2 j9 k- H4 B4、删除临时表。
2 p  B! b$ o' j( N7 Rmysql> select * from student;
+----+------+
| ID | NAME |
+----+------+
| 11 | aa |
| 12 | aa |
/ f" J. G  V: K, p| 13 | bb |
# D8 [) t- R" I0 I% C8 A: D| 14 | bb |
| 15 | bb |
| 16 | cc |
" l7 C3 f! z# Z+----+------+
6 rows in set

mysql> create temporary table temp as select min(id),name from student group by name;
Query OK, 3 rows affected
: u4 ~0 y- e( N, E8 m- KRecords: 3 Duplicates: 0 Warnings: 0

mysql> truncate table student;
. t/ d! R8 v' pQuery OK, 0 rows affected

mysql> insert into student select * from temp;
0 Q7 z5 ]$ N; u3 UQuery OK, 3 rows affected
Records: 3 Duplicates: 0 Warnings: 0

mysql> select * from student;
+----+------+
1 U. N* x( \' l- q: V+ v$ w| ID | NAME |
$ D7 K5 k9 O7 `6 S4 a$ {5 q+----+------+
8 Q& W  k2 b/ K| 11 | aa |
| 13 | bb |
| 16 | cc |
+----+------+
( D1 A+ f0 N. H' p7 \9 [3 rows in set

mysql> drop temporary table temp;
7 B/ S7 @1 I( w  ^+ ~1 DQuery OK, 0 rows affected
/ Q# j% {; `/ ~4 W6 G2 J6 l这个方法，显然存在效率问题。

---

方法2：按name分组，把最小的id保存到临时表，删除id不在最小id集合的记录，如下：
3 l# q. D- c7 }2 v( {mysql> create temporary table temp as select min(id) as MINID from student group by name;
Query OK, 3 rows affected
8 \: Q$ c% e7 u+ cRecords: 3 Duplicates: 0 Warnings: 0

mysql> delete from student where id not in (select minid from temp);
$ `  l( f) S+ l8 ^& @& q1 m/ b3 g7 k- sQuery OK, 3 rows affected

mysql> select * from student;
- I( N: u/ @0 g$ F) \! i' B+----+------+
# u/ W& C: u: o/ I+ S7 U7 }1 e1 {5 b| ID | NAME |
6 b6 \- k7 j" u/ I+----+------+
| 11 | aa |
0 \. l, V, B0 c7 {9 U3 j| 13 | bb |
0 h# J+ z/ D" `! U| 16 | cc |
. i' f* \: M1 S& \) }3 w2 D) P* T: m+----+------+
3 rows in set

---

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

mysql> delete from student where id not in (select min(id) from student group by name);
- q# V- A) P  J- ?9 T2 m执行报错：1093 - You can't specify target table 'student' for update in FROM clause
, R9 @& M- X( W5 i2 M1 a* f原因是：更新数据时使用了查询，而查询的数据又做了更新的条件，mysql不支持这种方式。
怎么规避这个问题？
再加一层封装，如下：
mysql> delete from student where id not in (select minid from (select min(id) as minid from student group by name) b);
) |) ?6 K# L; W" p; |Query OK, 3 rows affected

mysql> select * from student;
+----+------+
| ID | NAME |
% f. n! m- S2 Y( X+----+------+
| 11 | aa |
8 Z6 O' k# v9 Y$ E- w& b: q| 13 | bb |
| 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);
: g- `% H6 W+ ^, ]: i/ M

+ H3 c6 d3 k1 }! i8 q( w! ]0 l1 X