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

admin

方法1：
1、创建一个临时表，选取需要的数据。
2、清空原表。
2 Y1 Q2 `1 _! M6 P3、临时表数据导入到原表。
5 M, k! o7 C0 a3 i# w. X8 O4、删除临时表。
mysql> select * from student;
+----+------+
| ID | NAME |
( g; H) ?1 E4 B9 c3 z0 N2 A+----+------+
| 11 | aa |
; _7 B  _9 B5 x; {2 p5 ^/ ]5 n| 12 | aa |
| 13 | bb |
. k5 z7 v# |. J  H$ F| 14 | bb |
| 15 | bb |
. [, o5 i  C; x/ `9 J| 16 | cc |
. n  S3 N" c0 m% i, Q+----+------+
6 rows in set

mysql> create temporary table temp as select min(id),name from student group by name;
Query OK, 3 rows affected
. M$ a* w- |4 N9 E& WRecords: 3 Duplicates: 0 Warnings: 0

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

mysql> insert into student select * from temp;
- f. ~: g  y  d1 g& uQuery OK, 3 rows affected
" T# L" x2 z0 w- S* B5 hRecords: 3 Duplicates: 0 Warnings: 0

mysql> select * from student;
7 G# ?$ K* R0 D+----+------+
| ID | NAME |
- z4 ?; x& x1 [1 D+----+------+
# P) F9 b" l9 M* @  Y| 11 | aa |
+ A0 }2 }' Z8 P| 13 | bb |
2 B4 [# P& x5 @9 w' c3 r, Q  {| 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
Records: 3 Duplicates: 0 Warnings: 0

mysql> delete from student where id not in (select minid from temp);
4 L1 N$ w" }. D9 g: a8 W9 \; A* MQuery OK, 3 rows affected

mysql> select * from student;
+----+------+
7 e1 c6 u& T$ V- x$ f" \/ H| ID | NAME |
+----+------+
0 M* X+ I9 |3 i6 `| 11 | aa |
| 13 | bb |
4 r8 Q5 ]8 p% g1 P/ {/ N: y( f| 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不支持这种方式。
) I, C, b' L& P$ I" u1 G6 p, p; v怎么规避这个问题？
再加一层封装，如下：
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;
+----+------+
6 ^. u1 c! Q  k" m& q$ X| ID | NAME |
4 t0 i  ^8 K- u+----+------+
| 11 | aa |
| 13 | bb |
| 16 | cc |
" ~; w. Q" O5 ]$ b" A- T+----+------+
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);
! F0 ~. `5 B$ b9 T& Q

+ t8 J2 l  E! c2 t1 i) r& a7 c