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

admin

方法1：
8 r1 z) I6 f: u" v! C2 C- Y1、创建一个临时表，选取需要的数据。
; O0 l1 A& Z2 e. Z# C, ^& ]2、清空原表。
9 ?2 A+ {, M6 f/ j( o* ?- D3、临时表数据导入到原表。
. q0 ]2 D) d& w  D$ ~! p4、删除临时表。
mysql> select * from student;
& U/ B6 t7 D9 j$ ]3 I+----+------+
- c& D$ p2 E, A# p/ ~; z| ID | NAME |
+ r3 y2 p3 `; L8 g7 F2 J+----+------+
| 11 | aa |
: M0 x! G" j  r% w| 12 | aa |
| 13 | bb |
| 14 | bb |
8 W* f$ }; r. n; O( P, K4 h; B| 15 | bb |
| 16 | cc |
+----+------+
6 rows in set

mysql> create temporary table temp as select min(id),name from student group by name;
& T8 O9 b# }2 Z  L! E% x( s2 cQuery OK, 3 rows affected
+ p& Z8 g4 c9 n. R0 p( tRecords: 3 Duplicates: 0 Warnings: 0

mysql> truncate table student;
9 T' r/ u8 \1 a1 {1 MQuery OK, 0 rows affected

mysql> insert into student select * from temp;
Query OK, 3 rows affected
6 i: v5 |# C7 F: s  Q( K. u# BRecords: 3 Duplicates: 0 Warnings: 0

mysql> select * from student;
+----+------+
) o3 r3 W1 m) Q6 ?| ID | NAME |
+----+------+
| 11 | aa |
| 13 | bb |
| 16 | cc |
+----+------+
3 rows in set

mysql> drop temporary table temp;
Query OK, 0 rows affected
这个方法，显然存在效率问题。

---

方法2：按name分组，把最小的id保存到临时表，删除id不在最小id集合的记录，如下：
" f* ~, h6 U# _6 D( R% Q: dmysql> create temporary table temp as select min(id) as MINID from student group by name;
Query OK, 3 rows affected
: R5 P8 W6 P7 @% BRecords: 3 Duplicates: 0 Warnings: 0

mysql> delete from student where id not in (select minid from temp);
1 E7 p( W" ?3 }% {0 CQuery OK, 3 rows affected

mysql> select * from student;
3 R9 h' p: O5 v+----+------+
| ID | NAME |
; y  M1 L! E$ A& ?, G9 O+----+------+
( C7 p  `* W) S# J) P+ @6 l, h* x| 11 | aa |
3 g) g0 A  T3 A7 B* B3 Y3 _" B| 13 | bb |
7 Q$ B1 q! _* x, u& L| 16 | cc |
; A& D: |. ^. j2 R5 `0 z) [+----+------+
& l* C. X: I& K" Z* z0 k3 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
" T7 @+ E) S* I* R原因是：更新数据时使用了查询，而查询的数据又做了更新的条件，mysql不支持这种方式。
怎么规避这个问题？
再加一层封装，如下：
  R8 S7 M" A0 d" q( _0 D& ~4 F8 x7 Lmysql> 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;
$ z1 c# ~& n0 X7 Z+----+------+
3 o# t/ C" q' d+ _8 N! R| ID | NAME |
6 b: d3 z9 A+ G+----+------+
| 11 | aa |
| 13 | bb |
# N$ p8 Z( Z- V0 M| 16 | cc |
2 j4 {. _" L' ]. t5 O6 I8 e+----+------+
3 rows in set

) [8 ^/ k( f5 J0 F方法三例如：delete from bugdata where id not in (select minid from (select min(id) as minid from bugdata group by qxbh) b);
: j7 y/ `) b1 P# {$ j9 R