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

admin

方法1：
9 \6 _5 W. n& L, h" G, n  ~1 z# ]1、创建一个临时表，选取需要的数据。
8 J% u8 Z9 R2 a( j$ A% W4 ^2、清空原表。
3、临时表数据导入到原表。
0 n" t4 L, Y: t; l! u9 L  {3 u4、删除临时表。
mysql> select * from student;
+----+------+
% y4 l7 h( L! O$ V: z| ID | NAME |
( m  A* ?/ G+ T; U' j- Q2 G+----+------+
| 11 | aa |
| 12 | aa |
| 13 | bb |
5 ?6 K% a, t7 N* y# i| 14 | bb |
" e' T* K/ l! y* ^  T) E) n# x/ E, D| 15 | bb |
| 16 | cc |
1 g, w9 K+ J, C: ]# s% P0 [+----+------+
6 rows in set

mysql> create temporary table temp as select min(id),name from student group by name;
Query OK, 3 rows affected
& ?- H" j# y1 f9 u; fRecords: 3 Duplicates: 0 Warnings: 0

mysql> truncate table student;
Query OK, 0 rows affected

mysql> insert into student select * from temp;
Query OK, 3 rows affected
Records: 3 Duplicates: 0 Warnings: 0

mysql> select * from student;
+----+------+
| ID | NAME |
; \; N( J& h0 A5 D0 n2 v6 y7 S+----+------+
. ~; S5 Q7 \- {8 h! }% S| 11 | aa |
8 A( M2 c2 c" m- d8 C| 13 | bb |
$ l  E3 o- H: E7 n| 16 | cc |
- F. v/ b0 P$ q0 a2 F, [1 ^+----+------+
3 rows in set

mysql> drop temporary table temp;
$ f; ]8 W' X7 h+ e! z0 J; pQuery OK, 0 rows affected
1 P3 ^. \- t2 X2 J. F( v这个方法，显然存在效率问题。

---

方法2：按name分组，把最小的id保存到临时表，删除id不在最小id集合的记录，如下：
+ _" B  R6 {+ w- k9 I& S  Omysql> create temporary table temp as select min(id) as MINID from student group by name;
Query OK, 3 rows affected
4 |6 R  B( b. Q* ~- h, u! Y, y, lRecords: 3 Duplicates: 0 Warnings: 0

mysql> delete from student where id not in (select minid from temp);
5 ^6 s! r% ^3 b- k( \Query OK, 3 rows affected

mysql> select * from student;
+----+------+
| ID | NAME |
2 h8 b# e3 ?7 W6 d* O2 s' j+----+------+
' F6 [4 q  y* r3 B| 11 | aa |
: f2 G) F3 e* D& i2 t6 t| 13 | bb |
. |  u5 n2 M" _# F& L. Y$ H| 16 | cc |
% n9 {4 y% |* O  C$ s+----+------+
3 rows in set

---

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

mysql> delete from student where id not in (select min(id) from student group by name);
' c  F, v. q) [1 {6 a执行报错：1093 - You can't specify target table 'student' for update in FROM clause
* x3 Y# M8 t+ [2 B7 u原因是：更新数据时使用了查询，而查询的数据又做了更新的条件，mysql不支持这种方式。
" I& t9 ?$ S& `8 m1 m$ Y& s怎么规避这个问题？
再加一层封装，如下：
mysql> delete from student where id not in (select minid from (select min(id) as minid from student group by name) b);
  B3 r3 v# ^- |# S, JQuery OK, 3 rows affected

mysql> select * from student;
+----+------+
5 Y2 e% x; j& s1 M; ]8 L8 ~2 i! l2 G| ID | NAME |
( D7 I0 e' T) |7 Z0 w3 p+----+------+
  \1 U; T6 J* r7 ^3 {| 11 | aa |
# ?4 c# j( `. M+ P" o  l# }9 K9 X| 13 | bb |
| 16 | cc |
& a' a5 A2 b# @' C1 ^; r: L0 k+----+------+
: a$ z  M3 W# n0 B, ?3 rows in set

4 p, L" d! Q2 T$ v$ F$ Z方法三例如：delete from bugdata where id not in (select minid from (select min(id) as minid from bugdata group by qxbh) b);
  {. w8 P( c* B( H4 H) z
; g2 X& F/ @& c* P2 ?0 M
/ `. {+ ~3 {8 ?  e' D8 L8 D; `