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

admin

方法1：
1 {- H9 T+ W$ d' J+ h! S- v; Q+ t1、创建一个临时表，选取需要的数据。
2、清空原表。
6 M2 z0 u" s! h" b5 c& L  m6 l9 e2 w3、临时表数据导入到原表。
4 d3 S7 R0 g7 d% \9 d! L6 s4、删除临时表。
mysql> select * from student;
  M: }+ u/ P1 P: v5 \+----+------+
| ID | NAME |
+----+------+
| 11 | aa |
| 12 | aa |
| 13 | bb |
| 14 | bb |
% v! U; w, R5 {6 c' Q; L, I- }| 15 | bb |
| 16 | cc |
+----+------+
. L" W2 Z9 H4 c1 M2 M: }6 rows in set

mysql> create temporary table temp as select min(id),name from student group by name;
Query OK, 3 rows affected
" G  p3 n2 `3 }! C: dRecords: 3 Duplicates: 0 Warnings: 0

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

mysql> insert into student select * from temp;
5 G3 c+ @  b1 m. `: ]: a. P" YQuery OK, 3 rows affected
Records: 3 Duplicates: 0 Warnings: 0

mysql> select * from student;
+----+------+
# z2 p) V8 N  I4 b1 C# w$ u/ u| ID | NAME |
3 w  P1 j. b- V! c+ v% R* g+----+------+
* R; e. P/ o* k4 J- M, P| 11 | aa |
( G, t  a; ^4 }3 E| 13 | bb |
, S) a4 [, O' F! z8 y( d| 16 | cc |
+----+------+
3 rows in set

mysql> drop temporary table temp;
+ S8 e7 n1 H9 w8 lQuery OK, 0 rows affected
% w( S) [5 _* K  q: c! j这个方法，显然存在效率问题。

---

方法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
Records: 3 Duplicates: 0 Warnings: 0

mysql> delete from student where id not in (select minid from temp);
( C$ `" b, Q8 H: k! P  W9 [7 k0 m2 KQuery OK, 3 rows affected

mysql> select * from student;
+----+------+
| ID | NAME |
# Z! o7 h. \3 R/ |9 W( x( {+----+------+
/ |6 G5 d$ |+ V' W3 {" _| 11 | aa |
. Y/ S0 ]5 [7 g9 R| 13 | bb |
| 16 | cc |
+----+------+
3 rows in set

---

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

mysql> delete from student where id not in (select min(id) from student group by name);
执行报错：1093 - You can't specify target table 'student' for update in FROM clause
- U. p: R) H/ a: ?原因是：更新数据时使用了查询，而查询的数据又做了更新的条件，mysql不支持这种方式。
怎么规避这个问题？
- X) Q$ F2 X8 A! ~: ?( n再加一层封装，如下：
mysql> delete from student where id not in (select minid from (select min(id) as minid from student group by name) b);
9 w, U& N* _* f3 Z2 AQuery OK, 3 rows affected

mysql> select * from student;
+----+------+
| ID | NAME |
+----+------+
5 P  G  ~# P$ w- m" N| 11 | aa |
7 L9 H) H4 Z5 e. L- [% ^* B| 13 | bb |
| 16 | cc |
, x; L+ R2 W5 _+----+------+
* Q0 a$ D9 [6 `; `4 H- H% i! G3 rows in set

1 k4 _8 u7 D4 S1 X方法三例如：delete from bugdata where id not in (select minid from (select min(id) as minid from bugdata group by qxbh) b);

; w1 i+ ?/ L0 w) ?* R
' x  c9 Q; B& }