中国网络渗透测试联盟
标题: mysql删除重复记录，保存Id最小的一条 [打印本页]
作者: admin 时间: 2016-8-23 20:58
标题: mysql删除重复记录，保存Id最小的一条
方法1：
; |1 N& c0 Y. J1、创建一个临时表，选取需要的数据。
1 S9 C. `$ K+ M" s& y8 R2、清空原表。" i1 Q; y0 s5 ^
3、临时表数据导入到原表。5 X4 Q% u* h+ } l' B
4、删除临时表。 v6 \2 J" s% P5 A
mysql> select * from student;
. P7 Z# q- [& K$ E+----+------+# {: R* Z! U8 z0 V
| ID | NAME |
' ^7 N; h- A8 ]+----+------+
$ e* s/ h6 ~2 n+ ~) S/ n+ Y1 P| 11 | aa |3 y! ^) ?3 O* y9 S- |' d) M, M
| 12 | aa |
, ]5 R( O0 f% x8 g| 13 | bb |
3 j1 J9 g% J& N& v2 U4 D| 14 | bb |( c5 G: h! O3 ]! v* X) { V/ j. ~* o
| 15 | bb |$ E. y I' `9 R7 p1 U0 D7 l" W
| 16 | cc |, g! E9 N$ x5 @( x S
+----+------+' v8 g# {% W9 U5 o6 g; X: S1 O
6 rows in set
mysql> create temporary table temp as select min(id),name from student group by name;
0 ^, O. f+ I0 o2 X7 yQuery OK, 3 rows affected5 j$ |! K% H9 W/ ]: A, n
Records: 3 Duplicates: 0 Warnings: 0
mysql> truncate table student;. e; L& Q- k2 k9 U- r
Query OK, 0 rows affected
mysql> insert into student select * from temp;. M( N# q, f3 Z/ Z: M6 e
Query OK, 3 rows affected
, g! U; E7 a" I6 {# y! SRecords: 3 Duplicates: 0 Warnings: 0
mysql> select * from student;
% i7 [$ H, n2 B$ X+----+------+
" L" s7 T* \# L$ F/ {3 |/ C6 ^| ID | NAME |
6 z, I5 r H/ K6 I% M8 m4 ^+----+------+
7 H2 C6 D, x3 o; R; S& Q4 I| 11 | aa |, l# @: s. X6 ]! z- X6 v6 ~; l* H
| 13 | bb |, q9 t2 P: c X, S) u$ v
| 16 | cc |/ Z* Y& x, ?! d; \% ]5 ?3 h
+----+------+
, ^, r( I* F- w3 rows in set
mysql> drop temporary table temp;2 k& @! I* E( ]7 ]) H
Query OK, 0 rows affected! r {5 Z' v- _- _4 M! Q
这个方法，显然存在效率问题。
方法2：按name分组，把最小的id保存到临时表，删除id不在最小id集合的记录，如下：$ C* I$ b3 o& d- v
mysql> create temporary table temp as select min(id) as MINID from student group by name;
?3 H3 f. m* Q" {Query OK, 3 rows affected
% s# L) [( w) ] k9 LRecords: 3 Duplicates: 0 Warnings: 0
mysql> delete from student where id not in (select minid from temp);
$ L: T- h; g8 {4 }9 U$ ]Query OK, 3 rows affected
mysql> select * from student;
3 n0 h6 t2 [& ~" V, p3 B! }6 L+----+------+( X5 @% @: J" q# p# |
| ID | NAME |; G/ q# q9 N- m9 k: p- e
+----+------+
7 k& i3 l2 i' w+ x& q( q| 11 | aa |
3 C, P+ l# G; D+ _| 13 | bb |' P( ^+ ~( l" U9 T6 ~7 `
| 16 | cc |
" R5 D6 F* w5 i+----+------+' V) H& F6 \% `+ w5 T
3 rows in set
方法3：直接在原表上操作，容易想到的sql语句如下：
mysql> delete from student where id not in (select min(id) from student group by name);6 J% o0 d D' L9 ?: i: I- L* r1 |
执行报错：1093 - You can't specify target table 'student' for update in FROM clause" S$ t, t+ c! o, b7 h( L
原因是：更新数据时使用了查询，而查询的数据又做了更新的条件，mysql不支持这种方式。
$ ~% {! r3 ~5 s5 m怎么规避这个问题？
4 z, t( N7 j" n, ]再加一层封装，如下：
( O! G& D* v) T2 smysql> delete from student where id not in (select minid from (select min(id) as minid from student group by name) b);
" ]# o: _7 r6 z) WQuery OK, 3 rows affected
mysql> select * from student;
. {; N. {% i9 {% W0 s+ W+----+------+7 {( J0 P" Q; e0 K' q, C; D
| ID | NAME |
/ q: d! q% A2 Y5 ]) o. {" F+----+------+
& J4 ~9 ?. U1 W3 ?+ m( C| 11 | aa |
8 m* l0 f1 Q5 [: q' M3 T+ o9 G| 13 | bb |, T) o l" A& K( Q
| 16 | cc |
% e$ N4 w2 L3 x5 U+----+------+
4 z6 C/ _! D' K5 h# `3 rows in set
/ b+ a( R9 J% K) }* h' |9 R' S
方法三例如：delete from bugdata where id not in (select minid from (select min(id) as minid from bugdata group by qxbh) b);
8 R+ `4 D3 n' ^5 {5 y6 m& T U5 ~+ Y7 A& R
( d; c: n2 [# F) m3 ~* Z2 t* s
| 欢迎光临 中国网络渗透测试联盟 (https://www.cobjon.com/) |
Powered by Discuz! X3.2 |