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

admin

方法1：
  P; s. H, n! ?& u% m: Q1、创建一个临时表，选取需要的数据。
2、清空原表。
9 S0 A0 ^6 U  |3、临时表数据导入到原表。
* V& e- O: N1 P0 N4、删除临时表。
( j7 q9 ~  f! umysql> select * from student;
+----+------+
| ID | NAME |
- D+ ]; k! q7 F- y  N+----+------+
8 N% g9 n$ O! e7 f5 r/ ~, e+ H) q: f| 11 | aa |
" ~4 o5 f& \7 C" j| 12 | aa |
8 W8 {- M4 K: W| 13 | bb |
| 14 | bb |
| 15 | bb |
- E  c" |) |! D  w! }| 16 | cc |
+----+------+
7 Z! a. B% `* a. |6 rows in set

mysql> create temporary table temp as select min(id),name from student group by name;
Query OK, 3 rows affected
5 D9 P" c" y: a$ e% u! \1 L3 h1 ARecords: 3 Duplicates: 0 Warnings: 0

mysql> truncate table student;
* y: h; p& D6 c* t0 d+ sQuery 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;
+----+------+
0 ~3 ]) e* h& T: k- v' R/ W& Q| ID | NAME |
+ u/ Y# y% W1 W2 f2 D+----+------+
| 11 | aa |
| 13 | bb |
- }3 \* F0 P. j3 B0 y: R" ?+ F| 16 | cc |
1 W% L( d% D* ~2 \$ G+----+------+
3 rows in set

mysql> drop temporary table temp;
8 W1 m2 X1 s8 \, X$ FQuery OK, 0 rows affected
% @' K1 d- ^6 ~这个方法，显然存在效率问题。

---

方法2：按name分组，把最小的id保存到临时表，删除id不在最小id集合的记录，如下：
mysql> create temporary table temp as select min(id) as MINID from student group by name;
4 u- q. {' i1 s, i& r& qQuery OK, 3 rows affected
' d6 @  _1 u. z! HRecords: 3 Duplicates: 0 Warnings: 0

mysql> delete from student where id not in (select minid from temp);
Query OK, 3 rows affected

mysql> select * from student;
+----+------+
| ID | NAME |
) H- x8 H+ k) P% g. s! j8 w- ~+----+------+
3 H2 h" M% W6 y2 g% @| 11 | aa |
9 o* D( T. ]; }4 m  X* }3 C| 13 | bb |
| 16 | cc |
+----+------+
0 u9 h4 N$ |8 k3 v3 rows in set

---

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

mysql> delete from student where id not in (select min(id) from student group by name);
1 i4 E4 V: D- X* i执行报错：1093 - You can't specify target table 'student' for update in FROM clause
原因是：更新数据时使用了查询，而查询的数据又做了更新的条件，mysql不支持这种方式。
怎么规避这个问题？
7 e# b9 E- O  E+ D再加一层封装，如下：
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;
+----+------+
| ID | NAME |
+----+------+
| 11 | aa |
| 13 | bb |
| 16 | cc |
% l8 d$ t; Z1 S) \7 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);

+ ?& s/ k  H, D  b3 G" \; y; e8 K
1 t$ W7 h% R) ?% Y# ?4 R0 G