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

admin

方法1：
1 S3 p0 S- d% C* M1 k1、创建一个临时表，选取需要的数据。
2、清空原表。
3 @6 _/ L. Q  t, l+ }/ `3、临时表数据导入到原表。
4、删除临时表。
mysql> select * from student;
: z. C$ h; h+ J! [2 j/ G+----+------+
- ~. t7 ~; M0 k" V' ^' C& ^| ID | NAME |
+----+------+
| 11 | aa |
% y) G+ @' I, ^7 M9 u9 J| 12 | aa |
8 e& I! m7 Z, U* d4 l( I; L' J| 13 | bb |
( P6 i# i8 d$ l" `! U| 14 | bb |
| 15 | bb |
| 16 | cc |
+----+------+
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;
" ~. ^8 _0 e% _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;
5 f0 D. |: b$ F; P( {+----+------+
| ID | NAME |
+----+------+
| 11 | aa |
| 13 | bb |
| 16 | cc |
+----+------+
3 W5 x, I; R/ K, i) a/ S  g) n8 C3 rows in set

mysql> drop temporary table temp;
' ^9 e1 V. B! z3 VQuery OK, 0 rows affected
! @  Q0 r% C' j0 o  G$ d* {这个方法，显然存在效率问题。

---

方法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
* V- _) r! [2 \2 qRecords: 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;
+----+------+
  L* N* |( G" K* W8 O! O| ID | NAME |
" z( t( X5 T% z0 V  f. [% ?+----+------+
  }+ @/ ]* K0 t: H0 b0 g8 L| 11 | aa |
| 13 | bb |
| 16 | cc |
+----+------+
7 r5 `: I6 Y9 p; z; w3 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不支持这种方式。
* E; T) P! l0 H, Z- N, s怎么规避这个问题？
3 Q  _9 j$ z9 \( O再加一层封装，如下：
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;
+----+------+
| ID | NAME |
+----+------+
) W; T( B3 Q: F7 L+ d( O. L4 e  e4 n| 11 | aa |
7 O( b8 }( ^$ q$ S9 A7 ?) N  K" c+ L9 D| 13 | bb |
| 16 | cc |
+----+------+
9 G$ y$ |! @6 L; r3 P) x3 rows in set

方法三例如：delete from bugdata where id not in (select minid from (select min(id) as minid from bugdata group by qxbh) b);

# _- w' Z5 ?, O/ v! J