中国网络渗透测试联盟
标题: mysql删除重复记录，保存Id最小的一条 [打印本页]
作者: admin 时间: 2016-8-23 20:58
标题: mysql删除重复记录，保存Id最小的一条
方法1：
4 E6 r9 r1 I7 T' }, C" S' l1、创建一个临时表，选取需要的数据。
; `# ^5 R6 `& z8 V6 |2、清空原表。4 |1 a* A: A4 U# L. I5 V- m
3、临时表数据导入到原表。1 x0 y8 M$ s3 b7 C, c! o" B% s
4、删除临时表。+ Y2 D, a4 L$ g2 W5 G" r3 J) z
mysql> select * from student;
! [' k5 d* l$ T! c Y. v' T+----+------+6 T0 p/ [' x! w+ l# ^* z0 w
| ID | NAME |) \3 `; o( D/ @6 |$ ~. X0 c
+----+------+4 r3 J# ?% l5 u* Y k0 L
| 11 | aa |
^$ W% q0 N+ _0 G| 12 | aa |
3 n% x7 n0 I; t8 J; l| 13 | bb |2 Q& B9 k" {1 X2 a; m# v0 c
| 14 | bb |* I X6 |! Z; N* S+ t" D
| 15 | bb |
. R' v) N* Q; N1 G- x| 16 | cc |
" p/ `* Z' l5 p: m# f$ W+----+------+1 x: E) o( p3 I [6 R0 S5 b, t
6 rows in set
mysql> create temporary table temp as select min(id),name from student group by name;8 ]5 A7 V1 o1 R# O$ u$ z3 V) b1 q
Query OK, 3 rows affected' l% c& R1 u M6 J
Records: 3 Duplicates: 0 Warnings: 0
mysql> truncate table student;+ X" b' e& N- u
Query OK, 0 rows affected
mysql> insert into student select * from temp;# M' l- O5 R" A1 {+ v5 s2 L
Query OK, 3 rows affected
2 r4 W6 l& H; |Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from student;
0 q# P! h% T# i' P) `' ^0 Z# V- n) r+----+------+
: k( @4 I H" F| ID | NAME |
& N8 y( }5 `$ k. M" Z: l3 [4 C+----+------+7 q2 R" e @7 ]
| 11 | aa |8 R. R( U- `/ \- V1 o" S6 O/ T# z
| 13 | bb |
6 _1 }/ t9 N0 H/ Q: F| 16 | cc |, G+ O1 @$ H' V( v( C
+----+------+( D) A6 G+ |, z) q
3 rows in set
mysql> drop temporary table temp;2 W2 W+ A9 \+ B; j. z* H
Query OK, 0 rows affected
+ {: X, F6 Y) @, S" `这个方法，显然存在效率问题。
方法2：按name分组，把最小的id保存到临时表，删除id不在最小id集合的记录，如下：
+ f6 P2 |3 b6 G% \mysql> create temporary table temp as select min(id) as MINID from student group by name;
9 z, Y, t4 Q- W) UQuery OK, 3 rows affected- F& \. h6 `5 |6 ]
Records: 3 Duplicates: 0 Warnings: 0
mysql> delete from student where id not in (select minid from temp);
8 a# T/ U8 r p( h$ }) |Query OK, 3 rows affected
mysql> select * from student;
8 }! b; B" V* b7 M+----+------+. }) K! P4 z* G' [+ |
| ID | NAME |! R3 D1 e' D+ `
+----+------+* C) v! v4 z- ^0 E# a
| 11 | aa |8 @+ {8 Q, X F3 l Y
| 13 | bb |, _2 G$ m9 |' h
| 16 | cc |" E3 y" |# Q1 x: a, s
+----+------+0 X+ M8 Y {$ B9 ?: q
3 rows in set
方法3：直接在原表上操作，容易想到的sql语句如下：
mysql> delete from student where id not in (select min(id) from student group by name);
# i' _7 F2 G d执行报错：1093 - You can't specify target table 'student' for update in FROM clause- s0 u0 \$ Q( p1 ^3 S
原因是：更新数据时使用了查询，而查询的数据又做了更新的条件，mysql不支持这种方式。" z4 D; B. Y) v2 l) W
怎么规避这个问题？
5 I* L7 n& ]% K$ ~6 p再加一层封装，如下：( u/ ]0 }1 p- t" T1 T! j
mysql> delete from student where id not in (select minid from (select min(id) as minid from student group by name) b);/ R- x% w* `+ v
Query OK, 3 rows affected
mysql> select * from student;
$ Y ~6 z3 h) R+----+------+/ j& u z n4 L
| ID | NAME |
e5 [( ?6 k4 w3 Y8 ^) S+----+------+
# A) T+ U3 L# d& {9 G| 11 | aa |
- f0 d- @) k8 q4 R* b- E! q( @| 13 | bb |
6 H: a' n( L* F& V8 M9 ~( E| 16 | cc |: E4 o8 E8 s# _
+----+------+
+ t$ ]# w2 n; h5 x) @8 m# Z3 rows in set
* x+ o+ A8 U: b' p" p. ?方法三例如：delete from bugdata where id not in (select minid from (select min(id) as minid from bugdata group by qxbh) b);0 A! ~) C# |0 E# {) P( N% T, G! W
, }2 I& Q7 B4 D' m; I* ^0 P: Z" I/ S% W" z# r
| 欢迎光临 中国网络渗透测试联盟 (https://www.cobjon.com/) |
Powered by Discuz! X3.2 |