中国网络渗透测试联盟
标题: mysql删除重复记录，保存Id最小的一条 [打印本页]
作者: admin 时间: 2016-8-23 20:58
标题: mysql删除重复记录，保存Id最小的一条
方法1：" l! l: X$ N& P6 z
1、创建一个临时表，选取需要的数据。9 s6 x) A1 N) x; ^9 z
2、清空原表。
7 r6 i7 v/ N. g5 M: \6 T; Y; p3、临时表数据导入到原表。# `- h$ i+ ?1 k2 `5 n) H1 O& e" q
4、删除临时表。1 O" k, R' j( g+ T* F+ h
mysql> select * from student;3 M" T1 q+ M) q. n, `- j
+----+------+
* ?% V3 h; f, _! j( H| ID | NAME |0 D# o4 C! r. W. B e0 Y
+----+------+
4 C3 o+ t Z& || 11 | aa |9 H) G* A, B4 D( O8 z
| 12 | aa |4 Y/ W# z( |2 [/ G9 d- H
| 13 | bb |
+ F6 |3 h, `7 v# f% G7 i3 X| 14 | bb |$ V& U- ^% k5 I3 }2 n9 H, r9 N
| 15 | bb |8 u [5 ~+ S- v# Q
| 16 | cc |
1 q& w' E3 a. S8 y6 [+ R, }7 i& c+----+------+# m, @ F8 e0 G1 n9 m; P
6 rows in set
mysql> create temporary table temp as select min(id),name from student group by name;
& c( T2 r5 n) N6 u; D5 ~+ E/ TQuery OK, 3 rows affected) X1 f' F/ j; d
Records: 3 Duplicates: 0 Warnings: 0
mysql> truncate table student;
! T/ K r# k9 S: Z7 h# JQuery OK, 0 rows affected
mysql> insert into student select * from temp;
- k4 j! W( Y9 C/ UQuery OK, 3 rows affected& {1 |: t, x) u, T" u8 L0 S
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from student;
. U' A% y8 P; a2 t1 h3 x6 U! c+----+------++ ` K I t% _8 ?6 f& D# o
| ID | NAME |$ o. D1 p1 w$ S! @5 J) G }* _
+----+------+/ w: p; G# y- t7 D+ Z: r' y
| 11 | aa |2 o( H! ^/ \2 j! W
| 13 | bb |2 j9 ?: b& @" p e: i: h
| 16 | cc |
, @6 q, {- Q/ p2 V# @& w+----+------+9 u! e3 J! F! ]' D* o! h9 r
3 rows in set
mysql> drop temporary table temp;, T* {( h$ Z( h4 m1 m6 x2 k
Query OK, 0 rows affected- _. I* w* |$ t; Z4 e& `! F, B6 N
这个方法，显然存在效率问题。
方法2：按name分组，把最小的id保存到临时表，删除id不在最小id集合的记录，如下：
( H7 |; M( T# w2 Omysql> create temporary table temp as select min(id) as MINID from student group by name;( n, u& G6 v9 k9 m! z
Query OK, 3 rows affected
8 @- M* I& \, jRecords: 3 Duplicates: 0 Warnings: 0
mysql> delete from student where id not in (select minid from temp);# K/ s0 N% s1 o" G) U5 O% c
Query OK, 3 rows affected
mysql> select * from student;
! y. n; h3 V# F' p/ X+----+------+; H: l* `8 I7 I* I7 s* l
| ID | NAME |
9 t' U( w4 `7 N" O' ]( a I; R+----+------+
% W7 Y0 e2 l; z o# B| 11 | aa |9 M O/ m- _. g3 h7 z4 O2 ]
| 13 | bb |' s, h: B* R/ }3 d
| 16 | cc |
$ i. N0 k) I) P. e8 L+----+------+
+ R- f; n9 N$ B) d4 c- `- H3 rows in set
方法3：直接在原表上操作，容易想到的sql语句如下：
mysql> delete from student where id not in (select min(id) from student group by name);
8 H' B+ H8 D! f+ |. l/ @, j执行报错：1093 - You can't specify target table 'student' for update in FROM clause
" R" J) L2 m- S) y7 b0 u: ]; @原因是：更新数据时使用了查询，而查询的数据又做了更新的条件，mysql不支持这种方式。7 |' ~7 G) A7 w1 |+ Z
怎么规避这个问题？
: W8 G4 W2 a( _7 ]# K' b4 q/ u再加一层封装，如下：, C' e5 c2 j: H' V" L6 j
mysql> delete from student where id not in (select minid from (select min(id) as minid from student group by name) b);
6 q& J) Q2 c- a" A d0 `1 vQuery OK, 3 rows affected
mysql> select * from student;
2 |2 L4 U. T0 J+----+------+
; V+ n( x O' f P+ e% D% c" U. z| ID | NAME |
0 {/ u; {# c) A: z4 J8 g: R+----+------+
% a/ U! s) z4 U( N| 11 | aa |7 n: e" N5 @+ R( A: K
| 13 | bb |
& z' X/ B/ c+ E! q- k| 16 | cc |" K6 e+ u2 m: h6 N
+----+------+
7 X( j% {! I9 A( G1 r3 rows in set
; Z4 f+ F/ Z: B4 |1 z
方法三例如：delete from bugdata where id not in (select minid from (select min(id) as minid from bugdata group by qxbh) b);4 T. n% g% ?/ k& S/ {9 ^
) K% Z5 G5 {0 N# J/ y
- U7 n6 H& x5 q! i$ @
| 欢迎光临 中国网络渗透测试联盟 (https://www.cobjon.com/) |
Powered by Discuz! X3.2 |