中国网络渗透测试联盟
标题: mysql删除重复记录，保存Id最小的一条 [打印本页]
作者: admin 时间: 2016-8-23 20:58
标题: mysql删除重复记录，保存Id最小的一条
方法1：
; I# H# B" ?1 e4 S$ N. n1、创建一个临时表，选取需要的数据。3 K: `3 X5 {# X; n6 r( P
2、清空原表。8 S# j% U0 s/ b, v( l- f* X9 j# J* K
3、临时表数据导入到原表。7 e- T, [" o9 W* S$ d
4、删除临时表。
0 ~! v; H% g: B; o1 l3 U& Dmysql> select * from student;- }: \1 E8 t1 {! Q" v9 q+ f
+----+------+
8 S7 y( Y. S0 f& q* r9 ]3 Y- c2 K| ID | NAME |
" G8 ~5 K8 m3 m' L# U; q# ^+----+------+
* @# [% U: B. Z0 c' l| 11 | aa |# D5 J9 j. q7 D o) E3 k9 O
| 12 | aa |
8 a8 j |3 z* z1 H| 13 | bb |' Z/ r" z" b/ p
| 14 | bb |5 @* I5 s% ]/ Z6 y" M
| 15 | bb |8 _ ]! b, b2 v7 h" E
| 16 | cc |2 _' w$ U4 u5 I: x
+----+------+
7 m% A3 C- T S% c& Z9 Y$ R6 rows in set
mysql> create temporary table temp as select min(id),name from student group by name;* G3 p7 `: ]2 Z( m& U
Query OK, 3 rows affected& K' |/ s7 z5 ?" _$ T" W! N
Records: 3 Duplicates: 0 Warnings: 0
mysql> truncate table student;
$ B; N2 G2 C" t- u" rQuery OK, 0 rows affected
mysql> insert into student select * from temp;
# `) |) E4 Z# H5 U* u, ^Query OK, 3 rows affected4 V; r# k: v- @. {* r3 s) t
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from student;
# f- f( t6 a$ X; K$ v+----+------+1 X; x- ^! g- s0 ~2 y& D
| ID | NAME |7 }2 V- r; n0 ]; m+ A+ {9 z$ J
+----+------+
% Q) E% D& J7 ~: a| 11 | aa |
3 n3 n0 v2 V0 O3 O9 W1 S5 B" s, y| 13 | bb |# o! V; c- W9 G. s, Q
| 16 | cc |0 F# h" Z* v0 Y2 t
+----+------+* G J# b: |3 \) |. \
3 rows in set
mysql> drop temporary table temp;4 p; w4 w+ o$ T& |! j" U
Query OK, 0 rows affected" E+ X/ k- {7 R& c# @& c! B5 m4 _1 y
这个方法，显然存在效率问题。
方法2：按name分组，把最小的id保存到临时表，删除id不在最小id集合的记录，如下：# A5 T2 p# j" H3 X% `4 U( L5 \, `/ A6 G
mysql> create temporary table temp as select min(id) as MINID from student group by name;3 ^, n" O' B6 k; q$ A
Query OK, 3 rows affected
- o! z( T* s( RRecords: 3 Duplicates: 0 Warnings: 0
mysql> delete from student where id not in (select minid from temp);
. C1 H+ Z; U3 h+ q/ p2 `/ ], y( ]Query OK, 3 rows affected
mysql> select * from student;
2 U, h* h; `/ ^- r& [, z% C+----+------+& B+ G% Z2 y3 z: }- u: q
| ID | NAME |
6 J) Z+ Q |. z, n9 L+----+------+1 h. z8 p, h Q5 O+ x6 u
| 11 | aa |9 L" s' D7 p8 Q! F
| 13 | bb |
. t6 f7 D" I# O9 y$ F8 _8 L! g| 16 | cc |" K& w1 q5 S, S
+----+------+
$ Z* T% ]* P2 f# W) \7 u6 r3 rows in set
方法3：直接在原表上操作，容易想到的sql语句如下：
mysql> delete from student where id not in (select min(id) from student group by name);3 T: {/ S9 }7 q" U$ b" v
执行报错：1093 - You can't specify target table 'student' for update in FROM clause
3 V2 k' i) m' j1 b9 b原因是：更新数据时使用了查询，而查询的数据又做了更新的条件，mysql不支持这种方式。! t$ M @& C i' v& q' r V
怎么规避这个问题？6 X; W% f" \6 f" B! O- {
再加一层封装，如下：
6 I ?9 z) p5 L( b: J( Emysql> delete from student where id not in (select minid from (select min(id) as minid from student group by name) b);5 H' [; D% K( r
Query OK, 3 rows affected
mysql> select * from student;* }5 V, K: n2 I" K% z
+----+------+% B8 x6 h! s |- z- W+ [/ |8 C
| ID | NAME |
3 \4 Y9 I" w! ~, s5 K& B5 I+----+------+
; U3 D$ ~3 C) R* ?| 11 | aa |4 M* y& T5 i4 H y% _
| 13 | bb |: P1 l( u) _1 y8 W& t3 M" |8 L& k
| 16 | cc | i# @( x, D0 L
+----+------+
; Z- ~+ p0 \ C/ _ E/ ^3 rows in set
3 T6 @0 H3 \3 S% u& U. m$ P9 l# Y
方法三例如：delete from bugdata where id not in (select minid from (select min(id) as minid from bugdata group by qxbh) b);
3 h0 X$ @' X) p f) }. z6 U$ b% O
, i$ R- g3 N- q- b8 ?7 Z) D- S t( H; {+ T- p
| 欢迎光临 中国网络渗透测试联盟 (https://www.cobjon.com/) |
Powered by Discuz! X3.2 |