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

admin

方法1：
1、创建一个临时表，选取需要的数据。
; K+ M0 E& t3 Y1 v: S6 F' H2、清空原表。
3、临时表数据导入到原表。
5 q, S  e$ Z. K$ L% o3 E4、删除临时表。
; o1 N. u7 d; A  ~mysql> select * from student;
+----+------+
| ID | NAME |
7 A/ d. m4 G+ ]& Q: v+----+------+
+ ~# ?' d2 a* ]; V2 s* l| 11 | aa |
| 12 | aa |
| 13 | bb |
7 _& j: L) Y) G) A9 J1 }( R| 14 | bb |
| 15 | bb |
| 16 | cc |
# ]9 k: t' B. S9 @+----+------+
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;
8 _# d9 T2 a9 U; r' J8 x+ yQuery OK, 0 rows affected

mysql> insert into student select * from temp;
& `( ?  _- o8 m0 |/ `Query OK, 3 rows affected
Records: 3 Duplicates: 0 Warnings: 0

mysql> select * from student;
0 H8 j) T1 d1 P/ o+----+------+
) P- @+ y! e1 R# F' F| ID | NAME |
! F1 n3 j8 ?: F6 @! ~( L/ |0 ]+----+------+
( u, Y* Z# a0 P' R' T6 p| 11 | aa |
$ \5 d2 e5 B9 ^- [| 13 | bb |
| 16 | cc |
+----+------+
6 ]: h: i8 A7 H& e: P& `. c3 rows in set

mysql> drop temporary table temp;
% j; @( ]$ x5 e) T3 |2 }Query OK, 0 rows affected
这个方法，显然存在效率问题。

---

方法2：按name分组，把最小的id保存到临时表，删除id不在最小id集合的记录，如下：
/ N+ q" a8 Z, Q& K+ n$ R+ V$ emysql> create temporary table temp as select min(id) as MINID from student group by name;
Query OK, 3 rows affected
4 k! C3 v3 H% }Records: 3 Duplicates: 0 Warnings: 0

mysql> delete from student where id not in (select minid from temp);
) ]/ m. k% T1 ?) l! I( K0 xQuery OK, 3 rows affected

mysql> select * from student;
6 Q+ |( R8 D7 T+----+------+
| ID | NAME |
  f5 D& J' K* U& D  d+----+------+
| 11 | aa |
4 h2 b7 d! t& S* f| 13 | bb |
6 R" w  i4 i7 R" J9 n0 \| 16 | cc |
+----+------+
3 rows in set

---

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

mysql> delete from student where id not in (select min(id) from student group by name);
( k. S* a' Y* O0 ?' ~5 R  N执行报错：1093 - You can't specify target table 'student' for update in FROM clause
& i1 M, j) Z5 P/ p' N! U原因是：更新数据时使用了查询，而查询的数据又做了更新的条件，mysql不支持这种方式。
怎么规避这个问题？
再加一层封装，如下：
) Z" r" k8 R# @6 W  f- s" vmysql> delete from student where id not in (select minid from (select min(id) as minid from student group by name) b);
7 B( U" \! j% d7 p7 Q* `Query OK, 3 rows affected

mysql> select * from student;
+----+------+
9 v3 m4 m; n! T+ P| ID | NAME |
+----+------+
| 11 | aa |
| 13 | bb |
| 16 | cc |
; F, ?" P9 t$ ~2 [) b+----+------+
. e7 M& R5 V% k" ~2 M& C  M  W: a3 rows in set

方法三例如：delete from bugdata where id not in (select minid from (select min(id) as minid from bugdata group by qxbh) b);

( B8 _% O( A2 D, c