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

admin

方法1：
1 D6 c& S& l% @( Q" i) q! H1、创建一个临时表，选取需要的数据。
2、清空原表。
) @7 o: g  t: I8 h3、临时表数据导入到原表。
4、删除临时表。
. U3 @/ a) v) q" d, F& imysql> select * from student;
5 S2 t5 W. N+ {/ T5 A6 m' D  K# N+----+------+
* W3 ^/ n, Q& F" `; |* F| ID | NAME |
+----+------+
| 11 | aa |
0 H* \4 D3 I7 }- C# q+ t! S| 12 | aa |
| 13 | bb |
- e  z' A6 c4 `% G) V( r: v| 14 | bb |
2 t+ i! r9 m9 T0 j| 15 | bb |
| 16 | cc |
+----+------+
* U/ C& U7 I' u# F5 T: W7 y# U6 rows in set

mysql> create temporary table temp as select min(id),name from student group by name;
Query OK, 3 rows affected
+ n. m: R4 F* ?* n" j/ z3 TRecords: 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
Records: 3 Duplicates: 0 Warnings: 0

mysql> select * from student;
$ K: p/ r6 M0 i& r$ L3 x( |+----+------+
* A- j, Z/ u/ P" w| ID | NAME |
+----+------+
. G: D* Q4 s1 J) }! L| 11 | aa |
6 I; U+ s9 k/ ?7 s8 E& H1 }| 13 | bb |
( X: p4 n' x+ Z. I| 16 | cc |
- D: p) p6 e0 d3 T' l; C0 c, O  _+----+------+
3 rows in set

mysql> drop temporary table temp;
Query OK, 0 rows affected
. q5 G. Q4 R/ Q/ D这个方法，显然存在效率问题。

---

方法2：按name分组，把最小的id保存到临时表，删除id不在最小id集合的记录，如下：
mysql> create temporary table temp as select min(id) as MINID from student group by name;
# S& t7 @# ~1 e. k: X0 S  a! DQuery OK, 3 rows affected
" \+ N) V$ ?; l$ O" lRecords: 3 Duplicates: 0 Warnings: 0

mysql> delete from student where id not in (select minid from temp);
, H$ q$ H1 |+ a' j+ [' C0 E. N: bQuery OK, 3 rows affected

mysql> select * from student;
" B4 j" a; S6 [9 D5 a7 S& ?+----+------+
| ID | NAME |
! h' Z! P1 x  O: N- O2 I. p+----+------+
  f. h$ M  }5 G/ p+ d| 11 | aa |
| 13 | bb |
, m% Z0 N/ S" Z; E( c5 Z9 U| 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
原因是：更新数据时使用了查询，而查询的数据又做了更新的条件，mysql不支持这种方式。
* J1 O1 m5 A. E2 V  a怎么规避这个问题？
再加一层封装，如下：
mysql> delete from student where id not in (select minid from (select min(id) as minid from student group by name) b);
2 Q% B& o' B" y9 d5 dQuery OK, 3 rows affected

mysql> select * from student;
4 C7 d" `: M  L0 I9 e- x6 j* I+----+------+
| ID | NAME |
+----+------+
| 11 | aa |
8 u' B9 l+ I8 ~/ \| 13 | bb |
| 16 | cc |
+----+------+
, i0 j+ l' X0 ~$ `6 r2 R3 rows in set

方法三例如：delete from bugdata where id not in (select minid from (select min(id) as minid from bugdata group by qxbh) b);
9 B# \3 s- w( ]. U* _

8 h0 b7 p$ h2 a! E6 @