中国网络渗透测试联盟
标题: mysql删除重复记录，保存Id最小的一条 [打印本页]
作者: admin 时间: 2016-8-23 20:58
标题: mysql删除重复记录，保存Id最小的一条
方法1：% q' T2 o6 P8 E# H
1、创建一个临时表，选取需要的数据。$ ]+ q7 R$ q+ ~# C
2、清空原表。
$ g% d+ n' e) P3 k3、临时表数据导入到原表。
* d. ~6 p) d5 V! l3 C- {7 f4、删除临时表。
, V- T1 J6 K; |+ X# u+ d; B# S$ Hmysql> select * from student;
( b# N* f( b v5 V0 ?5 l+----+------+( r: p/ q6 ?* ]' ]
| ID | NAME |/ X- M% ]. O6 { ]0 G
+----+------+
! ^6 F2 c( i& S0 O3 J' Q7 i| 11 | aa |$ ?# \7 O4 k2 [ F+ r8 H+ K/ i; l
| 12 | aa |
. e L: f: \( d, L' Y% ~- _, D| 13 | bb |
- V7 O& F1 n5 J| 14 | bb |+ r. \3 A0 m" M& i# H6 {
| 15 | bb |
2 k2 ~1 l7 V: M' a7 x7 \% n7 ?| 16 | cc |
6 I3 Y e4 S/ h! B+----+------+1 e! F" Q3 S) V5 t: L C, v
6 rows in set
mysql> create temporary table temp as select min(id),name from student group by name;& N, N; z5 o0 A4 v
Query OK, 3 rows affected
6 \2 d: J3 g9 gRecords: 3 Duplicates: 0 Warnings: 0
mysql> truncate table student; D1 b9 J- s& Y4 w. |* \
Query OK, 0 rows affected
mysql> insert into student select * from temp;/ f, ^7 `2 z8 q: E3 o9 e
Query OK, 3 rows affected
" l/ _0 \& Y# {. C T1 zRecords: 3 Duplicates: 0 Warnings: 0
mysql> select * from student;! G8 _3 {+ P8 `! u
+----+------+
6 @% x( y5 c$ l5 L" c3 q" ]2 w" @, ^| ID | NAME |
6 I5 x% X! _* T" B1 B: U$ N ~+----+------+0 a h0 K" m& R6 m) t
| 11 | aa |# z( f0 `0 s. ]; z# {& T: I/ A4 x4 l
| 13 | bb |
( m) m" w: t1 A4 o1 ?8 O| 16 | cc |' y4 `! L0 E4 x+ M$ i; i4 |: Z# m
+----+------+ |. b$ ]7 s& [3 Z$ X$ @/ A
3 rows in set
mysql> drop temporary table temp;
$ w, g# a3 x, m7 {- c, aQuery OK, 0 rows affected3 F4 _, |% t0 z K/ e
这个方法，显然存在效率问题。
方法2：按name分组，把最小的id保存到临时表，删除id不在最小id集合的记录，如下：
7 r6 K) H+ r: g xmysql> create temporary table temp as select min(id) as MINID from student group by name;
1 i" g& N! i2 cQuery OK, 3 rows affected
+ P8 h8 ~+ x3 U) n3 H# n! YRecords: 3 Duplicates: 0 Warnings: 0
mysql> delete from student where id not in (select minid from temp);
- E% y; a ?* G0 WQuery OK, 3 rows affected
mysql> select * from student;2 O) c I6 C9 d
+----+------+; S0 P% a/ N$ ]+ y
| ID | NAME |, q5 K# j O+ D
+----+------+! O9 o; M4 W, ]0 D+ j. A2 H' b! |
| 11 | aa |
& H7 `* A& [5 m# D# N( Q| 13 | bb |! \( y* N: V# y& Z0 g' ^( T
| 16 | cc |
( Y) }) M2 w' y2 b+----+------+
: X$ V% ]+ m7 h" r" y& m3 rows in set
方法3：直接在原表上操作，容易想到的sql语句如下：
mysql> delete from student where id not in (select min(id) from student group by name);6 X S% |( z& e, Y
执行报错：1093 - You can't specify target table 'student' for update in FROM clause
: L% _5 c* j7 w( ?2 ]: q m# l# G/ H原因是：更新数据时使用了查询，而查询的数据又做了更新的条件，mysql不支持这种方式。
8 W3 r1 e) @- B8 t6 W9 G( K' m. y9 r怎么规避这个问题？9 S7 i6 d4 X/ }) W8 o$ N* n* t
再加一层封装，如下：0 N$ t$ f' h$ A# d7 _" {0 p; s
mysql> delete from student where id not in (select minid from (select min(id) as minid from student group by name) b);
: y: ^& }5 p [! H' p/ q8 ~+ iQuery OK, 3 rows affected
mysql> select * from student;0 l9 P4 N7 f0 B. V0 n. ?& l
+----+------+9 q& m" L$ S W% R- V' d
| ID | NAME |# ]% ^% O, Z4 O6 A1 r3 K K) Z
+----+------+# X) i; \1 v. X: q$ f. t, }
| 11 | aa |
* F( S: l2 F( n# Z& s| 13 | bb |
: v' p2 M: e! Q5 t: |6 j| 16 | cc |; }2 o1 x( V! L1 y* M) I
+----+------+
8 n! P1 ~# X: v4 S- y3 rows in set
/ C0 p) G, l+ T0 u# L# k0 x方法三例如：delete from bugdata where id not in (select minid from (select min(id) as minid from bugdata group by qxbh) b);
& `6 w1 A7 k! ]: @% A4 l) s2 Z/ v9 a0 q4 B! e: p% S
$ ?$ h1 w! z8 c& t
| 欢迎光临 中国网络渗透测试联盟 (https://www.cobjon.com/) |
Powered by Discuz! X3.2 |