中国网络渗透测试联盟
标题: mysql删除重复记录，保存Id最小的一条 [打印本页]
作者: admin 时间: 2016-8-23 20:58
标题: mysql删除重复记录，保存Id最小的一条
方法1：
; Z. r) m% R: u- e+ T$ G* `1、创建一个临时表，选取需要的数据。) [; R; F$ q5 m6 r' ^5 u. Y- T+ p. v' X
2、清空原表。) f: F( l, j8 ]7 x
3、临时表数据导入到原表。
& }5 \3 r% H2 C5 Y% M4、删除临时表。
- a, R# i u, s- M; r7 b$ A7 emysql> select * from student;7 m) N9 y8 M+ b
+----+------+
- o/ j0 N( ]; H& ~% Q, W4 `( y/ u| ID | NAME |6 W# S; x; o3 m L7 W% Z3 {
+----+------+
/ N3 ? P3 J7 l/ z| 11 | aa |2 N+ F/ L) Z7 S, j, Z. p( S
| 12 | aa |
8 ]0 L- I! H6 |- J0 y$ W; B| 13 | bb |
2 ~, F/ Z4 @7 b8 v8 Y7 u7 a6 A| 14 | bb |
) P6 ^, T! Z3 Z: {( ^' c* F, P| 15 | bb |
6 x7 {1 w2 J, k8 L. N| 16 | cc |
+ }+ I# H- |- u0 U4 o! P+----+------+
9 S4 E: p3 K5 J% f4 H0 ?/ d3 j6 rows in set
mysql> create temporary table temp as select min(id),name from student group by name;2 o- v0 |; {/ E
Query OK, 3 rows affected
- O2 W# A8 s" D6 R( K3 mRecords: 3 Duplicates: 0 Warnings: 0
mysql> truncate table student;; } S" [/ y0 f$ V+ F3 _1 X/ { G
Query OK, 0 rows affected
mysql> insert into student select * from temp;
# a- `( V$ O% ^1 CQuery OK, 3 rows affected
+ F9 X. E4 ]+ L6 ?$ R5 N% S' X2 YRecords: 3 Duplicates: 0 Warnings: 0
mysql> select * from student;
# \7 S/ G @$ A9 v1 j5 T, G+----+------+
/ G1 Z+ H7 }/ i4 B( l| ID | NAME |
( g( f4 F4 o8 v0 b* W+----+------+) p; e' r. O/ V3 T+ q5 a
| 11 | aa |
' }; `4 A% c7 I8 y9 X| 13 | bb |- s2 d& M. k3 m+ I' z$ ?% s2 c Q5 H! E
| 16 | cc |/ U. K% w! p1 s$ o
+----+------+
, B L$ Y* v. e- M g+ d3 S3 rows in set
mysql> drop temporary table temp;
1 T9 A+ C* n U' b1 u! ^2 Q H. cQuery OK, 0 rows affected
" M9 b# G5 y0 j5 d+ j, L这个方法，显然存在效率问题。
方法2：按name分组，把最小的id保存到临时表，删除id不在最小id集合的记录，如下：
* W \; N1 z8 T# R: f" ~5 dmysql> create temporary table temp as select min(id) as MINID from student group by name;
3 y1 }1 N+ b H; tQuery OK, 3 rows affected2 z' s! k: y% L. P
Records: 3 Duplicates: 0 Warnings: 0
mysql> delete from student where id not in (select minid from temp);
* _. e# U8 b: {9 bQuery OK, 3 rows affected
mysql> select * from student;1 L, W2 E- c' U/ x0 C7 E' F) I
+----+------+, g% |6 c5 s* t" S: _- N6 X" v; Q! {
| ID | NAME |
( v! t; m; i% v" J6 f+----+------++ `0 l0 D, {% n' }
| 11 | aa | ^& U+ A0 o- r2 p3 y G
| 13 | bb |2 X2 v- {8 n& O- o' R
| 16 | cc |
- ]; [3 r) t; J1 k( N+----+------+ l' `% a f, Y/ z
3 rows in set
方法3：直接在原表上操作，容易想到的sql语句如下：
mysql> delete from student where id not in (select min(id) from student group by name);) G" N0 u$ A$ ~+ y" ]' W# _
执行报错：1093 - You can't specify target table 'student' for update in FROM clause* Z: B" V- j9 h3 K# b
原因是：更新数据时使用了查询，而查询的数据又做了更新的条件，mysql不支持这种方式。! D. V2 J& U- \2 \% K) q
怎么规避这个问题？4 q) n, i0 r1 M; A, C4 C. b1 e& l- }
再加一层封装，如下：/ W# \/ R/ ?; L; O" e
mysql> delete from student where id not in (select minid from (select min(id) as minid from student group by name) b);2 [% T) I9 R7 }& A$ k5 `
Query OK, 3 rows affected
mysql> select * from student;
. q# G+ j. v- l6 _6 A+----+------+4 v2 R' P V) T& u: Y
| ID | NAME |) V% V7 x0 z( y4 g
+----+------+
5 ~5 ^6 L6 J6 Y" D$ `5 v| 11 | aa |
0 w) a4 l$ g; A0 D; Z4 ^3 Q| 13 | bb |
9 x. d$ `8 D' w t0 x| 16 | cc |% z3 B( z3 r1 m
+----+------+
# u$ O# k; X2 V& O1 ~& S- a3 rows in set
5 A1 ^+ u9 u" Y0 m J( A方法三例如：delete from bugdata where id not in (select minid from (select min(id) as minid from bugdata group by qxbh) b);
$ P5 ?* S7 Z" K0 g" A2 E8 E: D, j; g
* f! V6 U# g h4 P8 ?
| 欢迎光临 中国网络渗透测试联盟 (https://cobjon.com/) |
Powered by Discuz! X3.2 |