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

admin

方法1：
1、创建一个临时表，选取需要的数据。
2、清空原表。
3、临时表数据导入到原表。
4、删除临时表。
( C% `) @/ g' G" l" F4 Q/ Pmysql> select * from student;
+----+------+
| ID | NAME |
6 C0 f1 w. z0 I. y) i( p+----+------+
| 11 | aa |
| 12 | aa |
| 13 | bb |
| 14 | bb |
| 15 | bb |
2 o$ |' Q  N( T4 w. l% I| 16 | cc |
+----+------+
6 rows in set

mysql> create temporary table temp as select min(id),name from student group by name;
Query 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
Records: 3 Duplicates: 0 Warnings: 0

mysql> select * from student;
5 c: g& G, `$ R. z* f9 A  S2 P+----+------+
| ID | NAME |
+----+------+
+ q" B; N6 s4 d$ X| 11 | aa |
| 13 | bb |
; o2 K3 ]# ?# c3 N" X| 16 | cc |
+----+------+
3 rows in set

mysql> drop temporary table temp;
; s4 r. j% ~$ `3 Z, E' ^7 WQuery OK, 0 rows affected
5 E- U7 c6 [2 g0 p/ @8 ]% r0 ^这个方法，显然存在效率问题。

---

方法2：按name分组，把最小的id保存到临时表，删除id不在最小id集合的记录，如下：
1 ]  G  V* M9 x6 B( Dmysql> 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);
& a' y0 G, ?4 F5 m1 F. ?Query OK, 3 rows affected

mysql> select * from student;
+----+------+
3 \5 x% d  l' {/ [% |8 }" O| ID | NAME |
, N+ Q! ?0 F( C0 E+----+------+
! x2 `' o, J) D! \8 J- \| 11 | aa |
4 I0 X& x) P+ p| 13 | bb |
| 16 | cc |
+----+------+
8 p9 n/ u; j$ Y! p# ~3 rows in set

---

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

mysql> delete from student where id not in (select min(id) from student group by name);
( b, Q. f9 q4 ~执行报错：1093 - You can't specify target table 'student' for update in FROM clause
, n& H. a. n" j+ g# y$ `原因是：更新数据时使用了查询，而查询的数据又做了更新的条件，mysql不支持这种方式。
怎么规避这个问题？
& B" R# A0 V) Q$ D再加一层封装，如下：
  t* V; L/ Y, Q! y: X8 I% a9 [mysql> delete from student where id not in (select minid from (select min(id) as minid from student group by name) b);
" m2 B' C0 g; E. d! fQuery OK, 3 rows affected

mysql> select * from student;
4 Q+ n$ N* J9 B9 `+----+------+
: z6 s+ }5 w$ S& f| ID | NAME |
- C3 V5 ~3 u8 r0 L! o: _6 F+----+------+
| 11 | aa |
' i# j$ p( j/ l; h9 H; ]9 o; N| 13 | bb |
6 ^' c! A: d# ^| 16 | cc |
! _' r& L! `1 T3 Z! g" U( R" P+----+------+
3 rows in set

% o/ C2 l, c- R$ Z6 U8 b方法三例如：delete from bugdata where id not in (select minid from (select min(id) as minid from bugdata group by qxbh) b);


$ M# H9 _, q- F3 s! v, g, S