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

admin

方法1：
1、创建一个临时表，选取需要的数据。
+ j; x* h" M2 M1 h. w$ }2、清空原表。
# G' P' w7 \' d5 A) Y7 s3、临时表数据导入到原表。
% I1 \1 g/ H$ S4、删除临时表。
3 n' y6 \# V% f* q7 Umysql> select * from student;
+----+------+
| ID | NAME |
6 V2 {8 M* W2 \, W& E, ^4 t+----+------+
| 11 | aa |
| 12 | aa |
| 13 | bb |
| 14 | bb |
| 15 | bb |
| 16 | cc |
+----+------+
- y( V$ L( s9 t* z# S6 rows in set

mysql> create temporary table temp as select min(id),name from student group by name;
6 n6 b3 Q, ~2 b- lQuery OK, 3 rows affected
Records: 3 Duplicates: 0 Warnings: 0

mysql> truncate table student;
+ x. G8 ^* A* L' sQuery OK, 0 rows affected

mysql> insert into student select * from temp;
9 j; P0 N, b0 y( r, yQuery OK, 3 rows affected
Records: 3 Duplicates: 0 Warnings: 0

mysql> select * from student;
& n# B& I6 m! m# l1 m2 Z8 U+----+------+
| ID | NAME |
: F9 M" {2 Q5 a% O" i5 j9 y+----+------+
0 M/ x6 s0 Y* D, g% y* m  R' L| 11 | aa |
0 m/ y. }) m/ @5 |& F. N1 U  u| 13 | bb |
| 16 | cc |
' w* F9 m& p0 `4 F: f, U+----+------+
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
, ~0 |4 x, x9 o3 W: ^Records: 3 Duplicates: 0 Warnings: 0

mysql> delete from student where id not in (select minid from temp);
3 {6 L* X- V5 p  \2 O3 ?  R; ]Query OK, 3 rows affected

mysql> select * from student;
. O  q1 r2 Z. j+ B! I0 h5 O+----+------+
| ID | NAME |
+----+------+
| 11 | aa |
| 13 | bb |
| 16 | cc |
+----+------+
) M3 \! E7 O* q5 K3 rows in set

---

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

mysql> delete from student where id not in (select min(id) from student group by name);
$ J3 ~2 l" g1 {' S/ N) D( m执行报错：1093 - You can't specify target table 'student' for update in FROM clause
. q  s2 r' g; N2 ?9 m. U) X2 T原因是：更新数据时使用了查询，而查询的数据又做了更新的条件，mysql不支持这种方式。
) H% I/ g: o6 p3 _  m# U( [怎么规避这个问题？
: l  i3 y9 a) B) J% P( B# x再加一层封装，如下：
( N7 }2 q$ Z5 u/ @mysql> delete from student where id not in (select minid from (select min(id) as minid from student group by name) b);
' R6 V) d  T! W2 I& d" ?! IQuery OK, 3 rows affected

mysql> select * from student;
2 e) Y. j9 d/ H6 `4 u5 a+----+------+
| ID | NAME |
/ j  d/ r! P; _+----+------+
# y3 T4 w& k0 t4 d. ~  c8 @; v3 w6 s  @| 11 | aa |
| 13 | bb |
| 16 | cc |
+----+------+
3 rows in set

. K+ Y1 |: B/ L# a3 w方法三例如：delete from bugdata where id not in (select minid from (select min(id) as minid from bugdata group by qxbh) b);
* R, v9 E. X8 [2 i7 J