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

admin

方法1：
1、创建一个临时表，选取需要的数据。
2、清空原表。
3、临时表数据导入到原表。
4、删除临时表。
mysql> select * from student;
( U. H7 [5 m4 w  n! {% Z1 o+----+------+
| ID | NAME |
+----+------+
| 11 | aa |
0 p) V' H  _( w| 12 | aa |
6 q( g; _7 C5 L* C: c| 13 | bb |
! R  A9 P! x- R. U* y| 14 | bb |
2 h8 t9 l8 N9 {. s% k| 15 | bb |
| 16 | cc |
+----+------+
6 rows in set

mysql> create temporary table temp as select min(id),name from student group by name;
, W. l3 b) E5 g: S, p8 _Query OK, 3 rows affected
Records: 3 Duplicates: 0 Warnings: 0

mysql> truncate table student;
# e: }' a& l" e8 Z' VQuery OK, 0 rows affected

mysql> insert into student select * from temp;
( ?2 ^5 |9 e2 V( ?1 }" \2 @Query OK, 3 rows affected
Records: 3 Duplicates: 0 Warnings: 0

mysql> select * from student;
+----+------+
- W+ I0 R! t) y) k0 W- C+ y| ID | NAME |
+----+------+
- w2 }" v7 Y( I1 V7 _' r| 11 | aa |
| 13 | bb |
# W+ h) v( s1 c  ]# ?| 16 | cc |
6 @0 o4 O* J5 O% c  |5 R/ d: O+----+------+
- f6 m$ S7 K" v6 j: c# H* H3 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
Records: 3 Duplicates: 0 Warnings: 0

mysql> delete from student where id not in (select minid from temp);
3 f8 \/ Q! h+ m, iQuery OK, 3 rows affected

mysql> select * from student;
+----+------+
& {# V) Z8 D* I( R| ID | NAME |
+----+------+
9 k. [. Q9 Z: M, k# \| 11 | aa |
9 |4 E7 g# h% A9 c8 ], N- U: D  q| 13 | bb |
0 [& @3 m" B4 O| 16 | cc |
; h" |+ L/ L/ x1 ^) E+----+------+
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
原因是：更新数据时使用了查询，而查询的数据又做了更新的条件，mysql不支持这种方式。
( t$ i5 m5 q3 ]) I7 v3 a& b怎么规避这个问题？
再加一层封装，如下：
5 E% H7 v# J1 B) n8 |$ p# hmysql> 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;
+ m) O! E4 C% q. o$ k9 f9 F+----+------+
: o6 N: b& ]% @0 X6 C$ Z6 n' _| ID | NAME |
8 \' b$ G+ f7 M; P1 I# c0 q+----+------+
| 11 | aa |
5 E# V8 }9 y' b2 A( }3 ^. l5 q| 13 | bb |
| 16 | cc |
& X4 s9 X% ]- r+ }3 l% }+----+------+
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);
! F. j8 a/ ?" B8 \9 Z8 Y