中国网络渗透测试联盟
标题: mysql删除重复记录，保存Id最小的一条 [打印本页]
作者: admin 时间: 2016-8-23 20:58
标题: mysql删除重复记录，保存Id最小的一条
方法1：
5 u2 N# x/ d) m( p& ~& n0 T% F1、创建一个临时表，选取需要的数据。" H) D4 r1 {' X9 g0 K/ F
2、清空原表。
% W: s* ^5 L d- h9 V0 H9 o7 X3、临时表数据导入到原表。
1 T2 o' ?5 ?, g& q( c5 \4、删除临时表。+ K" J6 m: |' H6 w" | F
mysql> select * from student;
# ?5 P* E/ L& E/ E( H) O; _# J+----+------+" Y1 T8 Z. e: Y8 Q7 ?! s6 L
| ID | NAME |. s) M3 |/ C8 A3 O
+----+------+
- W4 y2 n& X; Y. K. e| 11 | aa |0 @5 G' b2 C, e: T* i- v
| 12 | aa |) r) P) v5 n) S3 f. ^
| 13 | bb |8 R* K/ G9 e: p6 C
| 14 | bb |
+ N+ J ?# C4 M5 a& I, d8 e* y| 15 | bb |
. v2 s% [2 k2 G| 16 | cc |
7 z' f5 b8 G' }/ [! o; U: R+----+------+7 I6 v" N& s* b( I- m
6 rows in set
mysql> create temporary table temp as select min(id),name from student group by name;
2 q) |# k& S: ~- x6 ^% i6 V6 CQuery OK, 3 rows affected4 y9 k s& _: J, R
Records: 3 Duplicates: 0 Warnings: 0
mysql> truncate table student;. R- b0 X, @4 R ]/ ]$ [ E) S
Query OK, 0 rows affected
mysql> insert into student select * from temp;, o- F3 q$ y( z' z' }$ `
Query OK, 3 rows affected
" F2 I U1 g7 X% B3 N' S7 \7 \% [Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from student;; u* E7 ^' s* W6 W& N5 M2 v
+----+------+
* H1 B1 [- ?: D1 t' u+ \| ID | NAME |
- v' c3 c8 A) d( A3 r/ b3 J5 B7 |+----+------+
C5 m+ Q! K+ K| 11 | aa |
' L% R5 Y/ C; B* S| 13 | bb |
6 Y3 }- N: R" K5 c+ [| 16 | cc |
* u$ p/ G" O( X# D1 R! m5 x5 a+----+------+
/ C( X/ j( _: Z6 F3 rows in set
mysql> drop temporary table temp;* s" k# t% f* |2 G4 B
Query OK, 0 rows affected
- g7 O: b+ ^. W7 o这个方法，显然存在效率问题。
方法2：按name分组，把最小的id保存到临时表，删除id不在最小id集合的记录，如下：. J5 D$ u* ` L5 ~) @9 G% \9 Z3 I8 q
mysql> create temporary table temp as select min(id) as MINID from student group by name;, |; i& n& p, S. ]$ ?
Query OK, 3 rows affected
) A# i1 F6 e7 O1 FRecords: 3 Duplicates: 0 Warnings: 0
mysql> delete from student where id not in (select minid from temp);
' k& I# a& Q" q2 \9 D' i& HQuery OK, 3 rows affected
mysql> select * from student;8 g8 t- z( p0 k0 \4 k' v$ z
+----+------+4 d2 Q* j2 Z t! [6 K7 U
| ID | NAME |" J7 v5 \( t" V. S
+----+------+
2 @% s3 y3 I* i6 M| 11 | aa |
- l1 [7 ^. `( q `& U| 13 | bb |
* K/ r {' m) ?2 R| 16 | cc |# l+ x: u: U: s6 M" @% \& o, c
+----+------+
+ c: Y4 r; s) ~+ v/ Y3 rows in set
方法3：直接在原表上操作，容易想到的sql语句如下：
mysql> delete from student where id not in (select min(id) from student group by name);( ^% |. k4 a% y9 F% O3 ?; k
执行报错：1093 - You can't specify target table 'student' for update in FROM clause# l6 S' ?# I* J
原因是：更新数据时使用了查询，而查询的数据又做了更新的条件，mysql不支持这种方式。& Z0 a) Z5 K |) ?# v+ O
怎么规避这个问题？0 w6 b' U5 }7 l
再加一层封装，如下：/ [' N2 C7 G+ v& A% Y0 a
mysql> delete from student where id not in (select minid from (select min(id) as minid from student group by name) b);, v( ^7 E5 E2 T+ \0 V/ k3 Z" F
Query OK, 3 rows affected
mysql> select * from student;
X% l0 k+ i' v5 ?4 q H2 n: b+----+------+# a, ]* f: d! q: M
| ID | NAME |
2 s. g7 X, {- D( c2 Y8 i, i+----+------+
3 v9 W6 j& u, V/ Y6 i. \4 T: q6 Z. s; M| 11 | aa |
; r: D4 H; o! Q| 13 | bb |
# C) H" ?) }; ^7 z2 v, R8 Z/ F| 16 | cc |
% N c2 v3 q4 O" a+----+------+$ D8 s( ?/ @9 s9 _$ r6 ~
3 rows in set
0 ?$ Z9 }* U7 [4 D方法三例如：delete from bugdata where id not in (select minid from (select min(id) as minid from bugdata group by qxbh) b);
; ~1 y0 k7 c- p
4 J7 E! L' ^( y9 P7 n
( Y; J" x. V1 g3 A5 ~8 Q! y
| 欢迎光临 中国网络渗透测试联盟 (https://cobjon.com/) |
Powered by Discuz! X3.2 |