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

admin

方法1：
& E$ I; k+ r2 J1、创建一个临时表，选取需要的数据。
0 Z0 z$ Z1 Z3 y0 a: I2、清空原表。
5 M- J9 W" r) q8 D. s- A5 u3、临时表数据导入到原表。
4、删除临时表。
/ d' f- Y& B  Lmysql> select * from student;
+----+------+
. |  i) K+ }5 A9 y| ID | NAME |
+----+------+
| 11 | aa |
| 12 | aa |
6 g% r6 b3 `* Q| 13 | bb |
8 a( n! L- [7 x  f7 `| 14 | bb |
| 15 | bb |
$ M6 U5 v" a, |$ m& U# e| 16 | cc |
7 w! a( c$ k  h+----+------+
6 rows in set

mysql> create temporary table temp as select min(id),name from student group by name;
! |& Z6 J, Q$ r/ D9 ?7 iQuery OK, 3 rows affected
Records: 3 Duplicates: 0 Warnings: 0

mysql> truncate table student;
& A* N) \: m9 p4 i7 `6 A2 r7 }& pQuery OK, 0 rows affected

mysql> insert into student select * from temp;
Query OK, 3 rows affected
& S  _- S2 {  s5 I$ h! FRecords: 3 Duplicates: 0 Warnings: 0

mysql> select * from student;
2 L! ]) r4 ^: _& Q) T4 ?+----+------+
| ID | NAME |
6 {( e4 y. d4 a' m1 O* E+----+------+
| 11 | aa |
* K6 A4 B6 a( c0 n2 D; D) G| 13 | bb |
  \6 Z9 e: o3 W6 H! }| 16 | cc |
+----+------+
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
) F7 V, I- D# TRecords: 3 Duplicates: 0 Warnings: 0

mysql> delete from student where id not in (select minid from temp);
* Z7 D4 O7 f' T5 @* ~3 C' ~Query OK, 3 rows affected

mysql> select * from student;
+----+------+
| ID | NAME |
+----+------+
| 11 | aa |
! g: K$ y. Q& q7 K| 13 | bb |
| 16 | cc |
+----+------+
* L3 w2 h* o5 k; F) A2 p& X7 B3 rows in set

---

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

mysql> delete from student where id not in (select min(id) from student group by name);
2 F5 A: i  D! h& V执行报错：1093 - You can't specify target table 'student' for update in FROM clause
* X) @4 A! ]6 M! M  u原因是：更新数据时使用了查询，而查询的数据又做了更新的条件，mysql不支持这种方式。
$ V$ F  G; B$ {: H& s怎么规避这个问题？
! W3 L6 U6 z  N9 m! e; _再加一层封装，如下：
mysql> 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;
' C" {2 a6 h5 `+----+------+
| ID | NAME |
* W0 e4 P: e; x& k+ f+----+------+
( c, C' c- `; I" L| 11 | aa |
| 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);


/ c1 M1 T, R9 V% n