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

admin

方法1：
1、创建一个临时表，选取需要的数据。
2、清空原表。
3、临时表数据导入到原表。
+ U% K4 r7 @8 J* [$ @  J4、删除临时表。
$ g. L4 w% S, ~. m8 Omysql> select * from student;
2 i1 \, o% c/ h& B+----+------+
3 I% E) s6 k+ ]% S' [| ID | NAME |
+----+------+
| 11 | aa |
& j, F, j7 }9 P: `| 12 | aa |
| 13 | bb |
, _! D5 X( @( m7 D* o/ || 14 | bb |
| 15 | bb |
| 16 | cc |
$ }+ Y5 t9 C9 H6 h- J" H3 `+----+------+
" g+ L- X  V% r6 rows in set

mysql> create temporary table temp as select min(id),name from student group by name;
! K8 z, b; r& o3 N. XQuery OK, 3 rows affected
Records: 3 Duplicates: 0 Warnings: 0

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

mysql> insert into student select * from temp;
Query OK, 3 rows affected
* ^& l% f% R9 |9 KRecords: 3 Duplicates: 0 Warnings: 0

mysql> select * from student;
" m) J0 R  W7 a7 Y# Q0 f* G+----+------+
: g: D9 i. \1 R1 k| ID | NAME |
) B+ R0 o6 @( L+----+------+
7 `; U& ~1 p. K$ v9 q| 11 | aa |
( j1 W5 G1 c  R5 {7 w/ U| 13 | bb |
+ O$ c* N3 y% a. V4 u; |& r| 16 | cc |
0 x) @7 A: p  H0 c' F! F! ]: m+----+------+
8 c& X1 u  {7 S' V2 S7 n3 rows in set

mysql> drop temporary table temp;
4 \. k& q' R4 u6 e4 xQuery 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);
3 p& l. h: H" QQuery OK, 3 rows affected

mysql> select * from student;
; Y1 r& U3 j% i, s) _+ s4 r+----+------+
0 L! k9 O0 x! ~% R2 f& j: d6 Y" }| ID | NAME |
+----+------+
$ `+ }" I, e9 P% d, Q& b| 11 | aa |
| 13 | bb |
4 r1 N5 ~' ?+ k6 I3 H| 16 | cc |
+----+------+
3 rows in set

---

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

mysql> delete from student where id not in (select min(id) from student group by name);
7 H5 }% u, U2 [$ \& N2 z执行报错：1093 - You can't specify target table 'student' for update in FROM clause
: U9 l  s0 ~: t" _1 U: c2 q原因是：更新数据时使用了查询，而查询的数据又做了更新的条件，mysql不支持这种方式。
怎么规避这个问题？
) z( d9 @3 ~$ p再加一层封装，如下：
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 |
+----+------+
2 N( e9 M7 {( o0 E- f: e4 c- m3 rows in set

# y- j1 p' V9 g6 Y方法三例如：delete from bugdata where id not in (select minid from (select min(id) as minid from bugdata group by qxbh) b);
' c& ~# l' Z* n- l  S5 C
2 w# W' c7 n9 ^# r