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

admin

方法1：
1、创建一个临时表，选取需要的数据。
2 y% X$ \8 K& q7 p! Z2、清空原表。
& _; Z7 j/ _- ^9 }* U3、临时表数据导入到原表。
* j4 i- f6 _( _; s, ?. D4、删除临时表。
mysql> select * from student;
8 ?2 p" D& U5 `+----+------+
| ID | NAME |
+----+------+
3 \) q9 N3 [# M! R9 k| 11 | aa |
- A3 j0 M3 E+ V( \| 12 | aa |
" U0 h- T, z2 ^5 S: \  H: i) ?| 13 | bb |
) d' e5 x  R3 }+ e3 L& `) ^/ n| 14 | bb |
| 15 | bb |
| 16 | cc |
. x) G, i$ ^% A+ }+----+------+
6 rows in set

mysql> create temporary table temp as select min(id),name from student group by name;
' N# }( u' y  ]0 g+ ]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
, B2 t) z" }' X: u5 aRecords: 3 Duplicates: 0 Warnings: 0

mysql> select * from student;
  ?# E' S9 [0 {+----+------+
| ID | NAME |
, A2 m2 E% h4 h$ j3 w+----+------+
7 R4 g  L; ~' t5 g% [| 11 | aa |
| 13 | bb |
| 16 | cc |
0 Y) F7 H3 t+ J9 f1 H+----+------+
3 rows in set

mysql> drop temporary table temp;
Query OK, 0 rows affected
* O+ c' l& y. ^这个方法，显然存在效率问题。

---

方法2：按name分组，把最小的id保存到临时表，删除id不在最小id集合的记录，如下：
4 f9 \6 {& v. l# Emysql> create temporary table temp as select min(id) as MINID from student group by name;
Query OK, 3 rows affected
2 |, D3 _0 O- y  [2 f+ ~Records: 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;
% u: V" y+ [, g+----+------+
' B+ Q7 U- ]- H. || ID | NAME |
+----+------+
| 11 | aa |
' L; u! r8 ]& g4 \% t( A+ F| 13 | bb |
6 X  L; |: V* Q, u) @3 j9 }| 16 | cc |
+----+------+
3 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
. n" D, o5 R+ ?/ h" r! I3 p) i原因是：更新数据时使用了查询，而查询的数据又做了更新的条件，mysql不支持这种方式。
怎么规避这个问题？
再加一层封装，如下：
" ^# }# O1 u( Kmysql> 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;
+----+------+
8 d- c5 j% J/ z- e) q' p| ID | NAME |
! `9 M1 j. P' P! Y+----+------+
| 11 | aa |
* g0 ]- j9 x4 `6 s| 13 | bb |
% A3 b: b, P# S4 i| 16 | cc |
+----+------+
3 rows in set

7 z0 }% }% C$ M. T, ~; V方法三例如：delete from bugdata where id not in (select minid from (select min(id) as minid from bugdata group by qxbh) b);