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

admin

方法1：
- T. L9 r3 I' C9 [3 s& W1、创建一个临时表，选取需要的数据。
) m4 \8 n7 Y3 Y3 k' X2、清空原表。
3、临时表数据导入到原表。
' v. t$ S3 n  a2 f8 y* m& c- Z5 Q4、删除临时表。
mysql> select * from student;
+----+------+
| ID | NAME |
/ ]& f2 ~9 r8 [" z8 y+----+------+
| 11 | aa |
1 j& z! k" [: M| 12 | aa |
| 13 | bb |
" g2 |) ~9 ?) U% e* x) V! F/ A6 L| 14 | bb |
5 w' K, y) S) o) B& v| 15 | bb |
| 16 | cc |
+ [0 Y0 C/ `- m7 U! W' a  |' y+----+------+
6 rows in set

mysql> create temporary table temp as select min(id),name from student group by name;
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;
2 H; r) V( w' T3 H/ ?Query OK, 3 rows affected
Records: 3 Duplicates: 0 Warnings: 0

mysql> select * from student;
6 q: ^+ y: u! V7 O! }6 K+----+------+
| ID | NAME |
+----+------+
' Z2 s- t0 C6 C* p# ?5 a, X9 c6 a2 m| 11 | aa |
| 13 | bb |
8 y, d* l! T. s8 D0 q6 x  L& Y) {5 y| 16 | cc |
+----+------+
3 rows in set

mysql> drop temporary table temp;
* e' @# w  C' I5 V- ^! H- H% L; hQuery OK, 0 rows affected
这个方法，显然存在效率问题。

---

方法2：按name分组，把最小的id保存到临时表，删除id不在最小id集合的记录，如下：
1 ]) l, Y0 q- H* T( V! U$ U: cmysql> create temporary table temp as select min(id) as MINID from student group by name;
Query OK, 3 rows affected
Records: 3 Duplicates: 0 Warnings: 0

mysql> delete from student where id not in (select minid from temp);
! I3 a6 v! {6 n( `5 NQuery OK, 3 rows affected

mysql> select * from student;
% c( O/ o: a8 ~2 o  L+----+------+
: T3 r$ C4 l/ S| ID | NAME |
+----+------+
| 11 | aa |
| 13 | bb |
/ l# x" \; T+ y3 P| 16 | cc |
: L: M. {& [3 f7 j+----+------+
! [8 O. |# A" z: W' y3 j7 w" b; a( m  e3 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
+ {6 }6 y1 v2 G原因是：更新数据时使用了查询，而查询的数据又做了更新的条件，mysql不支持这种方式。
0 a0 W+ r! Y  A/ C怎么规避这个问题？
再加一层封装，如下：
mysql> delete from student where id not in (select minid from (select min(id) as minid from student group by name) b);
  j' u' X( L6 f% @Query OK, 3 rows affected

mysql> select * from student;
+----+------+
/ O+ j; k* Q$ P: Q) }| ID | NAME |
+----+------+
5 m/ h7 X4 T# ], c| 11 | aa |
| 13 | bb |
| 16 | cc |
' q" I5 o9 q# h8 \* r& t2 C+----+------+
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);
! n* O+ C! M* L- W& W  E! C