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

admin

方法1：
# N, w2 e3 ^/ ?) R1、创建一个临时表，选取需要的数据。
2、清空原表。
3、临时表数据导入到原表。
; V* ^# L2 E3 A# F9 A, x( ^4、删除临时表。
mysql> select * from student;
& j& j- \) v( j0 ^$ @4 z% N  g+----+------+
| ID | NAME |
+----+------+
1 t; U5 f# L. [6 J% {| 11 | aa |
0 B4 F" p: w1 b| 12 | aa |
" Y/ j4 f' N& R6 p; a| 13 | bb |
| 14 | bb |
, j$ t" ~; A: j5 o| 15 | bb |
2 a" U! A, G, T| 16 | cc |
7 l- Z* f* ^  |; \5 k' i+ p3 U+----+------+
6 rows in set

mysql> create temporary table temp as select min(id),name from student group by name;
Query OK, 3 rows affected
$ ?5 a. [8 s, n2 n% d9 [Records: 3 Duplicates: 0 Warnings: 0

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

mysql> insert into student select * from temp;
2 C5 w- w! j! d3 h$ WQuery OK, 3 rows affected
Records: 3 Duplicates: 0 Warnings: 0

mysql> select * from student;
+----+------+
5 b' {- q% Y; y| ID | NAME |
+----+------+
5 s+ l1 I. N5 E) K) q) V5 \| 11 | aa |
$ H( O. Y. {2 H2 ?| 13 | bb |
. n2 j! `- d; L. q8 w' {$ V| 16 | cc |
+----+------+
- ~5 u! g( ?& H7 ~3 rows in set

mysql> drop temporary table temp;
Query OK, 0 rows affected
6 m4 j1 x5 F" c+ t/ N. a$ m) p这个方法，显然存在效率问题。

---

方法2：按name分组，把最小的id保存到临时表，删除id不在最小id集合的记录，如下：
mysql> create temporary table temp as select min(id) as MINID from student group by name;
8 P; I2 ]* X0 ]. D- sQuery OK, 3 rows affected
Records: 3 Duplicates: 0 Warnings: 0

mysql> delete from student where id not in (select minid from temp);
2 R5 K( _0 o4 S' _( xQuery OK, 3 rows affected

mysql> select * from student;
; v7 B4 i2 D' e5 {0 d4 [: }& |$ `: o+----+------+
6 ?" s2 r- Y, b$ f3 `9 W# }8 ^| ID | NAME |
- ~, Z* F" W6 _- B" S: h1 N+----+------+
' h; a; K$ s" _/ H0 v( X| 11 | aa |
/ {. M8 N  O. s4 G2 u* h$ b8 r| 13 | bb |
1 a  {- [2 F9 {. d3 m6 H) x| 16 | cc |
+----+------+
+ o7 j; Y) M1 e0 u3 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不支持这种方式。
' ^% r2 m, Q& u& Y- B怎么规避这个问题？
  ~: n( [3 d) t' [- B& r8 @1 Y" V. @再加一层封装，如下：
mysql> delete from student where id not in (select minid from (select min(id) as minid from student group by name) b);
' l! O" Z' B' O& L9 _3 {5 yQuery OK, 3 rows affected

mysql> select * from student;
* N1 ^3 o. P3 D+----+------+
| ID | NAME |
+----+------+
, w% r1 L2 j+ d/ I| 11 | aa |
8 P+ E1 c4 ]2 S  [+ H| 13 | bb |
| 16 | cc |
! S: j4 g) \7 \+ w+ p+----+------+
3 rows in set

& V+ ^* X- Z7 z( C! B" {6 }+ k方法三例如：delete from bugdata where id not in (select minid from (select min(id) as minid from bugdata group by qxbh) b);