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

admin

方法1：
9 v, S; w( Z3 J3 R1、创建一个临时表，选取需要的数据。
2、清空原表。
; N1 ~4 C- ~3 n% w3、临时表数据导入到原表。
! X6 ?, v/ [/ T) ^4、删除临时表。
mysql> select * from student;
+----+------+
| ID | NAME |
6 O( X0 N6 N0 G2 Z# E+----+------+
  [$ t- e. Y. Z+ u: o| 11 | aa |
| 12 | aa |
% }) C# t7 |5 z0 p# u( @( ^| 13 | bb |
| 14 | bb |
| 15 | bb |
| 16 | cc |
0 T5 |7 q0 N( g. N4 [5 _1 }/ N+----+------+
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 T% b3 m9 z! I9 m3 ?- xRecords: 3 Duplicates: 0 Warnings: 0

mysql> truncate table student;
' h6 q9 n- y: q# z/ o5 X3 gQuery OK, 0 rows affected

mysql> insert into student select * from temp;
Query OK, 3 rows affected
- k) m8 S  B, H+ [' SRecords: 3 Duplicates: 0 Warnings: 0

mysql> select * from student;
5 `6 s0 A" @  t8 v+ G+----+------+
% [, p' a2 {' X" H& n| ID | NAME |
7 a; @1 J7 [5 b. `0 I+----+------+
4 h3 D  r+ z6 Y. }8 H; G& K| 11 | aa |
| 13 | bb |
| 16 | cc |
+----+------+
0 K( q, l6 v, v2 a& N9 D) r" h3 rows in set

mysql> drop temporary table temp;
Query OK, 0 rows affected
% a5 K# r" S: S5 V/ ?) e' k这个方法，显然存在效率问题。

---

方法2：按name分组，把最小的id保存到临时表，删除id不在最小id集合的记录，如下：
mysql> create temporary table temp as select min(id) as MINID from student group by name;
6 t7 m6 f. S+ V0 pQuery OK, 3 rows affected
* S% K4 W' N7 JRecords: 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 |
1 B. z3 F$ ~( I9 M% }  Q/ X+ }+----+------+
, Z: d2 O; ^$ v" M  }; h; a) r| 11 | aa |
: s- m8 S! l- s* y| 13 | bb |
| 16 | cc |
+----+------+
1 _% T8 B; V9 {/ o/ G$ q+ U, e, p3 rows in set

---

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

mysql> delete from student where id not in (select min(id) from student group by name);
) J& Z$ S! @/ o执行报错：1093 - You can't specify target table 'student' for update in FROM clause
原因是：更新数据时使用了查询，而查询的数据又做了更新的条件，mysql不支持这种方式。
怎么规避这个问题？
' s% h+ T3 G5 D再加一层封装，如下：
) G# u. Y3 \1 q) vmysql> 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;
, x: d- G, B/ q2 x+ _9 t+----+------+
| ID | NAME |
+----+------+
| 11 | aa |
, z" j; w2 W4 ?, w# W% x# O! K' q| 13 | bb |
$ `2 A8 V' R. ]) s| 16 | cc |
; A7 p4 l' S  _2 {3 o: h3 ~+----+------+
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);