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

admin

方法1：
8 C: c# ?) Z# K2 ~1、创建一个临时表，选取需要的数据。
2、清空原表。
+ f4 `, m9 o3 X& b* L5 ~& U- n8 e3、临时表数据导入到原表。
4、删除临时表。
mysql> select * from student;
+----+------+
| ID | NAME |
+----+------+
- ]( M6 D" ?' z% t' e+ k| 11 | aa |
| 12 | aa |
| 13 | bb |
4 h) M8 h& z9 M+ @| 14 | bb |
| 15 | bb |
| 16 | cc |
+----+------+
) t! t7 \; K1 o$ W8 Z# f6 rows in set

mysql> create temporary table temp as select min(id),name from student group by name;
/ ]; p2 B6 x1 k9 s% v" ~Query OK, 3 rows affected
+ W4 W3 Y. x" RRecords: 3 Duplicates: 0 Warnings: 0

mysql> truncate table student;
" x7 w$ z- c! K& b( O# KQuery OK, 0 rows affected

mysql> insert into student select * from temp;
9 a# k7 X' C, q4 T6 E, cQuery OK, 3 rows affected
Records: 3 Duplicates: 0 Warnings: 0

mysql> select * from student;
; M& q" R) B9 P+----+------+
| ID | NAME |
8 a$ X8 |" n" M1 ~! q: U8 Z+----+------+
| 11 | aa |
. H6 |+ i& t, \. g9 S| 13 | bb |
| 16 | cc |
+----+------+
3 rows in set

mysql> drop temporary table temp;
: O* g( `8 `4 Y6 c9 T6 n! d+ Q3 jQuery 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
$ z6 y* l) \8 u+ F+ X% L7 aRecords: 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 |
+----+------+
& y3 R; m& Z  t- D) B3 _4 p| 11 | aa |
| 13 | bb |
# t4 x1 q# P+ E! T| 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
原因是：更新数据时使用了查询，而查询的数据又做了更新的条件，mysql不支持这种方式。
怎么规避这个问题？
' k/ j. y+ \3 C! _# w再加一层封装，如下：
: E& ?  v2 h/ D9 ?( `' \( |$ i! j0 ^mysql> delete from student where id not in (select minid from (select min(id) as minid from student group by name) b);
& ~3 V; H! u4 E5 f" h6 k, L9 KQuery OK, 3 rows affected

mysql> select * from student;
+----+------+
| ID | NAME |
4 Q1 h( t& K& \+----+------+
% s4 s1 ^6 B  b. |( F! V. V| 11 | aa |
| 13 | bb |
| 16 | cc |
" H  P9 B. {3 v+ V6 C+----+------+
% \& P/ ~" y. H7 ]9 f7 I* x3 rows in set

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

' B. a0 \6 U; H  ?, z