中国网络渗透测试联盟
标题: mysql删除重复记录，保存Id最小的一条 [打印本页]
作者: admin 时间: 2016-8-23 20:58
标题: mysql删除重复记录，保存Id最小的一条
方法1：
( G5 x3 n! m% Y' `( i* B. e7 R8 ^1、创建一个临时表，选取需要的数据。* j& |4 `# _+ e
2、清空原表。+ ]/ b- }$ f% Y. [( T
3、临时表数据导入到原表。. r, R. g& V) Z3 ~) w( G) S
4、删除临时表。
" ], ~4 ?! }+ M2 x) ~. bmysql> select * from student;
( w$ d/ V: ^ P) E: `/ ?8 o+----+------+3 l+ \" k* l6 n. \: V- n5 G
| ID | NAME |0 W# J8 Z" `" o0 D# {
+----+------+
* ]/ ?; ^/ j, s$ `8 t| 11 | aa |
1 u+ X/ u. G1 q5 Z2 O; y" m2 v| 12 | aa |+ A" h. _( u8 \1 M# N
| 13 | bb |
J7 K7 Y3 n1 j' z| 14 | bb |
1 K% m% u4 }5 q. k! S| 15 | bb |% E% U7 x" m) p9 {+ I
| 16 | cc |1 _: V3 r: |1 b8 j
+----+------+
8 a- |# Q6 o/ \, ]. {$ o6 rows in set
mysql> create temporary table temp as select min(id),name from student group by name;
$ u& R3 _% O4 ^3 E5 ]6 i6 ?. cQuery OK, 3 rows affected
0 P1 o1 ^: u4 e7 l! S1 X( BRecords: 3 Duplicates: 0 Warnings: 0
mysql> truncate table student;
# N4 f! t, S7 n; i- ^Query OK, 0 rows affected
mysql> insert into student select * from temp;: _2 A& }% u) p5 X4 b# V
Query OK, 3 rows affected1 S0 h& S* e$ [7 V
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from student;
, X( a0 p, B! a+----+------+: S) Q, P) G4 _' B
| ID | NAME |
% N" \5 b5 e! E! R4 J1 r! W+----+------+! y" j6 j& \6 y
| 11 | aa |
0 C0 y$ t* i" Z, @: I| 13 | bb |
/ C$ j. ]* [! {| 16 | cc |
7 u' |5 ~2 ?& X& \: M' ^6 V+----+------+
) H3 |, A- G( i; y( O1 k. |9 A3 rows in set
mysql> drop temporary table temp;
f( g# a/ e- U9 ]+ }Query OK, 0 rows affected5 ]- l' y0 ^. d% ~9 G" d
这个方法，显然存在效率问题。
方法2：按name分组，把最小的id保存到临时表，删除id不在最小id集合的记录，如下：
& g0 V- J- M$ P: a) c( @$ Rmysql> create temporary table temp as select min(id) as MINID from student group by name;5 X) N y! ? t8 y* I$ w
Query OK, 3 rows affected$ j8 d9 r$ k# J) v( b" l' G2 f
Records: 3 Duplicates: 0 Warnings: 0
mysql> delete from student where id not in (select minid from temp);/ P% S0 k- x1 D$ f1 y9 {& K8 O' j7 l* Q
Query OK, 3 rows affected
mysql> select * from student;- I8 Z" E4 T% `. ?4 h/ t' f
+----+------+1 {4 f& a* Y& Z! n$ g. T" z
| ID | NAME |6 d+ M" G; v1 F, i0 z
+----+------+8 `+ t5 G2 w2 d2 f2 u
| 11 | aa |
# I) _" J. i" e* O& j% @| 13 | bb |
# W3 v) ^ _' e/ b& R2 o2 }3 P| 16 | cc |, u6 z! k# x; W
+----+------+$ d+ p5 o7 d& I* o# c4 h" ]. Q
3 rows in set
方法3：直接在原表上操作，容易想到的sql语句如下：
mysql> delete from student where id not in (select min(id) from student group by name);; G3 Y. X( [7 F% Q6 T7 l* N! x+ V6 `
执行报错：1093 - You can't specify target table 'student' for update in FROM clause. {+ T2 G% H2 T5 z! T$ j
原因是：更新数据时使用了查询，而查询的数据又做了更新的条件，mysql不支持这种方式。4 ?& C" D6 B) p* G' O- T5 Y
怎么规避这个问题？) P/ q3 m: Q- K- I, P, t
再加一层封装，如下：( o5 g& E. G0 T
mysql> delete from student where id not in (select minid from (select min(id) as minid from student group by name) b);
* O: f) ]+ X# a5 Z) ?Query OK, 3 rows affected
mysql> select * from student;8 F2 @+ P( X, } H( C
+----+------+3 j& x5 M7 `8 |% F/ G+ r$ T
| ID | NAME |- U3 B: ?+ J/ i1 W
+----+------+; p) o6 Q5 J" q0 \6 ^' {
| 11 | aa |5 |: m* O1 c5 h! ]
| 13 | bb |: W6 o6 O0 l+ |7 K) z; l, o
| 16 | cc |1 X$ K8 ?6 k& m/ J/ ]
+----+------+
: R+ x, k1 }7 f3 rows in set
4 T* h5 m- Q, o6 e" i方法三例如：delete from bugdata where id not in (select minid from (select min(id) as minid from bugdata group by qxbh) b);
$ q9 U5 G. Y/ f2 _
* D( z/ A# x; Y$ E, O, P; Z1 l0 t* |- z4 M Z. a2 T7 ]
| 欢迎光临 中国网络渗透测试联盟 (https://cobjon.com/) |
Powered by Discuz! X3.2 |