中国网络渗透测试联盟

标题: mysql删除重复记录，保存Id最小的一条 [打印本页]

---

作者: admin    时间: 2016-8-23 20:58
标题: mysql删除重复记录，保存Id最小的一条

方法1：
9 s) }& T( Q. t0 d: {1、创建一个临时表，选取需要的数据。
2、清空原表。
2 }. I3 ?( e5 k1 ?+ L3 s) }0 ~4 o3、临时表数据导入到原表。
5 L1 d, @& B+ g, M7 g4、删除临时表。
# G0 G4 S5 n* o; b: I, `9 e# M5 rmysql> select * from student;
* }6 s+ d4 H8 o; J$ ~9 \; G2 F+----+------+
| ID | NAME |
+----+------+
| 11 | aa |
% U8 R% a- T! _| 12 | aa |
| 13 | bb |
| 14 | bb |
' y# z! o8 o) }" h| 15 | bb |
| 16 | cc |
+----+------+
4 a* S' X2 t% ^& V& X& k/ K6 rows in set

mysql> create temporary table temp as select min(id),name from student group by name;
& {2 I$ I& G% U6 Q. \- h  UQuery OK, 3 rows affected
Records: 3 Duplicates: 0 Warnings: 0

mysql> truncate table student;
. P8 d8 X, P; i- T' gQuery OK, 0 rows affected

mysql> insert into student select * from temp;
' R; X, V1 V5 l" m& v7 `+ XQuery OK, 3 rows affected
Records: 3 Duplicates: 0 Warnings: 0

mysql> select * from student;
# Q: ^$ o2 Q7 z+----+------+
8 d/ ?: ~6 F# f* H9 v. e, i| ID | NAME |
+----+------+
' i; W* U% W0 `" t- D; G3 T| 11 | aa |
! B7 }4 R4 d1 T: `- G5 C/ x| 13 | bb |
: U) e. p1 Z% p5 {' D4 _2 q1 a| 16 | cc |
2 S6 d- b6 B# L. Z# q+----+------+
$ e; I; i7 D/ C3 rows in set

mysql> drop temporary table temp;
* Z6 w% e# |2 X* rQuery OK, 0 rows affected
& _% s# u; C/ u' y2 u: |/ 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
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;
+----+------+
| ID | NAME |
+----+------+
| 11 | aa |
| 13 | bb |
+ k3 S. v* S+ R8 G| 16 | cc |
* r* h' w# O2 ?0 L+----+------+
8 b4 ^+ L5 r* c( O3 rows in set

---

方法3：直接在原表上操作，容易想到的sql语句如下：

mysql> delete from student where id not in (select min(id) from student group by name);
$ L% K3 e5 v7 u0 `7 f3 ]4 P执行报错：1093 - You can't specify target table 'student' for update in FROM clause
原因是：更新数据时使用了查询，而查询的数据又做了更新的条件，mysql不支持这种方式。
怎么规避这个问题？
( e# s, ~! \, E; [5 o再加一层封装，如下：
1 k; f& a9 a& y; Hmysql> delete from student where id not in (select minid from (select min(id) as minid from student group by name) b);
! U2 f7 a+ M8 x6 q  rQuery OK, 3 rows affected

mysql> select * from student;
+----+------+
| ID | NAME |
# c" _0 m4 p8 Z5 M! S. k% q. S3 U+----+------+
| 11 | aa |
& x+ R1 i  ^9 ?" B| 13 | bb |
| 16 | cc |
" o( |) v: T( S7 [& g+----+------+
3 rows in set

/ s& p; @) ?+ n9 d( `方法三例如：delete from bugdata where id not in (select minid from (select min(id) as minid from bugdata group by qxbh) b);

---

欢迎光临 中国网络渗透测试联盟 (https://www.cobjon.com/)

Powered by Discuz! X3.2