中国网络渗透测试联盟
标题: mysql删除重复记录，保存Id最小的一条 [打印本页]
作者: admin 时间: 2016-8-23 20:58
标题: mysql删除重复记录，保存Id最小的一条
方法1：2 |6 f0 P6 P5 a# k6 _
1、创建一个临时表，选取需要的数据。
, e6 v0 _% M9 Y8 l2、清空原表。
0 \! c l2 M$ ^1 _/ `/ I2 S3、临时表数据导入到原表。
9 \. }0 ^3 y2 P, L4、删除临时表。
4 J6 K5 ?- L) \ b* _9 Xmysql> select * from student;
& p. W1 T2 {7 Y+----+------+
3 f! o* n0 L0 ]) Z2 @2 k7 a| ID | NAME |! V F5 Z% ?5 Z# \
+----+------+4 D1 _8 K# k0 q* I: O& b/ a
| 11 | aa |
6 x- a& a7 R1 a0 H8 H% S| 12 | aa |3 |$ N; j+ ?" z# ]$ p7 `5 O2 ~
| 13 | bb |0 q2 A! Z1 m+ {0 x4 I. _
| 14 | bb |
/ ~& M- O$ H1 k| 15 | bb |- g/ w) k& [3 Z
| 16 | cc |
$ A$ o$ T" J* _4 L7 M t$ D+----+------+8 b# o7 H, j( n; E( X
6 rows in set
mysql> create temporary table temp as select min(id),name from student group by name;; M8 j8 a. J( M% \! ~/ {0 s ]
Query OK, 3 rows affected8 m7 S6 F& x! \9 S
Records: 3 Duplicates: 0 Warnings: 0
mysql> truncate table student;2 P1 k4 G8 g3 m
Query OK, 0 rows affected
mysql> insert into student select * from temp;0 X! L$ W& y w2 l" v2 r- Q7 c& v
Query OK, 3 rows affected
7 w0 ?! t* {: WRecords: 3 Duplicates: 0 Warnings: 0
mysql> select * from student; v, f9 m1 v( G
+----+------+
* M7 o( \0 T; F V2 Y| ID | NAME |
; `, E- o$ N2 [! W+----+------+
0 R9 q1 ?3 p8 w" I6 e8 w+ g3 u| 11 | aa |
. b( J3 ~+ V$ o Q, I' ]" Y8 _| 13 | bb |/ y2 b; D+ F1 k
| 16 | cc |
+ A9 h' F; V6 Q+----+------+
# _6 y3 E6 t) L' @. |3 rows in set
mysql> drop temporary table temp;
/ a* T6 C& i& H0 Q% j9 aQuery OK, 0 rows affected; L/ C) p* R, d/ D8 ?8 d+ c
这个方法，显然存在效率问题。
方法2：按name分组，把最小的id保存到临时表，删除id不在最小id集合的记录，如下：
7 U( {' u$ P, _& L. Pmysql> create temporary table temp as select min(id) as MINID from student group by name;$ x; {0 n7 D4 r& z1 B+ p' k$ @
Query OK, 3 rows affected
3 g# ]4 L3 J7 d. y1 X, q" j+ @Records: 3 Duplicates: 0 Warnings: 0
mysql> delete from student where id not in (select minid from temp);
/ }% g' X F3 T9 OQuery OK, 3 rows affected
mysql> select * from student;, q1 O R/ \* V5 H- ?
+----+------+' y1 I( z# I3 d/ H$ W
| ID | NAME |
% ~! ?, P0 u+ C4 w- R4 b- ^+----+------+
y4 g, W) C- v/ J| 11 | aa |1 H& T5 U) [/ n( V7 s
| 13 | bb |- [( _9 H, U4 a( `
| 16 | cc |
9 C4 ?& X* @8 \3 t* j' |+----+------+) x7 n4 X# C$ ?
3 rows in set
方法3：直接在原表上操作，容易想到的sql语句如下：
mysql> delete from student where id not in (select min(id) from student group by name);
$ s' T: J: s# ~+ S: k执行报错：1093 - You can't specify target table 'student' for update in FROM clause: \* b1 ^5 e/ b3 V1 t
原因是：更新数据时使用了查询，而查询的数据又做了更新的条件，mysql不支持这种方式。1 s6 H# V7 o! i
怎么规避这个问题？- ?9 K: f+ U$ p. f3 A5 v9 w) Q/ r
再加一层封装，如下：% w% Z! W& R# ]9 ~# N% @
mysql> delete from student where id not in (select minid from (select min(id) as minid from student group by name) b);* c0 o' ?8 @) C2 N) T6 }8 @( R
Query OK, 3 rows affected
mysql> select * from student;
& F: g- w* m- R5 J+----+------+
; T# q. Q7 I7 Z% y' Q| ID | NAME | Z- R8 g9 ^! e+ X, r2 ~
+----+------+! T9 N+ r) p+ @
| 11 | aa |% f4 t# O' P( e# c- n8 Y$ m% a
| 13 | bb |
2 c* ^' n" ^ [4 W0 }| 16 | cc |3 v6 Z) W0 R( t, ]; S( K" X2 }
+----+------+
; S9 T- W3 n# [5 F" e) O3 rows in set
5 i! l9 L. k/ b
方法三例如：delete from bugdata where id not in (select minid from (select min(id) as minid from bugdata group by qxbh) b);* [, T+ j% A& v
& y; T, r2 I, W* }/ w" N5 p) H6 b) y$ ]& I5 H/ b8 @
| 欢迎光临 中国网络渗透测试联盟 (https://www.cobjon.com/) |
Powered by Discuz! X3.2 |