中国网络渗透测试联盟

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

---

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

方法1：
1、创建一个临时表，选取需要的数据。
% C- V6 q$ X: X; f2、清空原表。
3、临时表数据导入到原表。
4、删除临时表。
mysql> select * from student;
: J5 K! L. G# @8 E. ]6 }% V: q" }+----+------+
; ]0 c6 H1 F+ H, a| ID | NAME |
( L% Q: z- B+ Y' N" `( Q+ p* u+----+------+
| 11 | aa |
+ _4 Q. H+ X/ \( t4 U; D' b+ p| 12 | aa |
| 13 | bb |
/ x& f! e$ y. {| 14 | bb |
5 k1 F& V: q) C( e1 l4 X| 15 | bb |
| 16 | cc |
+----+------+
8 F- e) N( U2 e6 rows in set

mysql> create temporary table temp as select min(id),name from student group by name;
9 z' N$ A! E: D  ^Query OK, 3 rows affected
, J# X; q7 K$ f1 s( ~" NRecords: 3 Duplicates: 0 Warnings: 0

mysql> truncate table student;
Query OK, 0 rows affected

mysql> insert into student select * from temp;
" ^, z5 n$ X2 G7 ?* l3 e* Y. kQuery OK, 3 rows affected
Records: 3 Duplicates: 0 Warnings: 0

mysql> select * from student;
) a2 u* h+ x$ |, ^; N9 h# i+----+------+
| ID | NAME |
, G9 i4 `  R/ ~3 y; a4 _1 L) A( m+----+------+
| 11 | aa |
- N9 I  K( x* y. @! P5 Y8 ?| 13 | bb |
| 16 | cc |
+----+------+
3 rows in set

mysql> drop temporary table temp;
. Y' w+ b% o; {Query OK, 0 rows affected
& P/ a$ Q) _4 p* ^' i这个方法，显然存在效率问题。

---

方法2：按name分组，把最小的id保存到临时表，删除id不在最小id集合的记录，如下：
, q6 I; m3 m0 }7 A8 _! tmysql> 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 |
1 Y* Z5 g  x+ o' I+----+------+
' ^0 ]' ^) ~5 u| 11 | aa |
| 13 | bb |
$ @4 G1 O5 N. j4 _# m0 }| 16 | cc |
$ G. u6 j5 Q  L" J3 s. T# F  g+----+------+
" e1 Y8 W! ^" w/ E4 l9 N3 rows in set

---

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

mysql> delete from student where id not in (select min(id) from student group by name);
9 k; d) y( s5 h4 j执行报错：1093 - You can't specify target table 'student' for update in FROM clause
原因是：更新数据时使用了查询，而查询的数据又做了更新的条件，mysql不支持这种方式。
, V; m$ k. K* ]' J1 ]' Z" F, v  w: U怎么规避这个问题？
& G7 P2 f: m7 Z: F再加一层封装，如下：
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;
4 C, F3 ?) s: m" L7 q) w+----+------+
| ID | NAME |
0 W. J- u  j8 |1 z. g+----+------+
) n& U3 i: [  Q| 11 | aa |
| 13 | bb |
6 V( ^, f$ v- Z, e| 16 | cc |
+----+------+
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);
1 t% f4 e6 ]: ^' D% j% K

---

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

Powered by Discuz! X3.2