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

admin

方法1：
1、创建一个临时表，选取需要的数据。
2、清空原表。
3、临时表数据导入到原表。
+ {- ?$ \" v7 M5 O0 Q' ~+ u" e- U, r4、删除临时表。
- Z0 x5 x, G, c6 emysql> select * from student;
+----+------+
' u1 O, k6 R. U4 Y2 Q) a  c$ f| ID | NAME |
+----+------+
| 11 | aa |
| 12 | aa |
| 13 | bb |
; v* p2 w8 m% f| 14 | bb |
| 15 | bb |
( c& |) w8 G4 ~& u4 `| 16 | cc |
& G  o9 S' F" n8 p- @3 e$ O4 f8 b0 b+----+------+
- T$ b* `% j% `, v6 rows in set

mysql> create temporary table temp as select min(id),name from student group by name;
Query OK, 3 rows affected
8 w9 {! L" f$ r1 P: C2 y) m" fRecords: 3 Duplicates: 0 Warnings: 0

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

mysql> insert into student select * from temp;
( P- z- g5 s! b6 ?; g. B* V6 nQuery OK, 3 rows affected
# Z' n6 }0 C/ Y+ }Records: 3 Duplicates: 0 Warnings: 0

mysql> select * from student;
+----+------+
| ID | NAME |
) t) p' B% o5 e& t( [) N+----+------+
8 a4 U6 Z/ J& n) j0 b| 11 | aa |
$ o( }: B+ C" O" q| 13 | bb |
' B$ _6 A3 c0 \8 J* Q* W7 Z) L| 16 | cc |
+----+------+
: X0 x! T; c9 n3 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
9 M) t6 k! N' {' C  o. ERecords: 3 Duplicates: 0 Warnings: 0

mysql> delete from student where id not in (select minid from temp);
+ b2 S6 l. }  PQuery OK, 3 rows affected

mysql> select * from student;
" ^3 h$ e4 r7 L" W% f* X  {+----+------+
| ID | NAME |
+----+------+
| 11 | aa |
0 i! S" A; \5 H. O* y| 13 | bb |
; M1 Y5 Q! \& ^  t. u! O! g  _& m| 16 | cc |
+----+------+
8 M6 ]0 u; ^$ T8 d' h$ m3 rows in set

---

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

mysql> delete from student where id not in (select min(id) from student group by name);
3 V9 ~. k& @' V5 o% e1 e0 u: j  c5 y执行报错：1093 - You can't specify target table 'student' for update in FROM clause
, M9 H' g( r6 ^9 `$ ]原因是：更新数据时使用了查询，而查询的数据又做了更新的条件，mysql不支持这种方式。
怎么规避这个问题？
  h, {0 n! f1 R& @/ ]6 C9 j8 v5 Q* u再加一层封装，如下：
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;
& a8 {4 M) f. y; N: o+----+------+
| ID | NAME |
9 ?! ^, o  [8 b6 m+----+------+
| 11 | aa |
/ _- l) I7 l5 q/ E| 13 | bb |
| 16 | cc |
+----+------+
) J1 s1 A  J, m3 y3 rows in set

- V- @8 X- o+ L6 d- i方法三例如：delete from bugdata where id not in (select minid from (select min(id) as minid from bugdata group by qxbh) b);
/ X. f% \' p0 s

; ~  }4 e9 t+ e( ^9 H# u