中国网络渗透测试联盟

标题: mysql删除重复记录，保存Id最小的一条 [打印本页]

---

作者: admin    时间: 2016-8-23 20:58
标题: mysql删除重复记录，保存Id最小的一条

方法1：
1、创建一个临时表，选取需要的数据。
9 W3 N' J2 a1 F" {2 {0 ?& z2、清空原表。
; p5 s6 Y/ s# D2 w; l0 K; y3、临时表数据导入到原表。
4、删除临时表。
+ h2 r* a+ e8 V7 _. m4 ]1 h1 c3 hmysql> select * from student;
4 h- m: u6 X& q# i( [3 F3 _2 |, z! U+----+------+
! @9 {! V( Z+ C" M! n" g| ID | NAME |
* K# Z, Y) ]1 n1 i+----+------+
4 x$ Y1 R" J  S( X, E" Y) g| 11 | aa |
) m+ H+ ?2 Q7 p! J. o* [  Q" B: a| 12 | aa |
& G7 V' k6 E( E/ R# r7 a3 C9 Y| 13 | bb |
" t! R+ `  n9 ]1 t| 14 | bb |
! g- @8 B5 F, _8 G6 j. A/ `0 y| 15 | bb |
/ Y- k- v  t$ m# e  h| 16 | cc |
+----+------+
6 rows in set

mysql> create temporary table temp as select min(id),name from student group by name;
Query OK, 3 rows affected
0 O* D5 A! ]  ^/ uRecords: 3 Duplicates: 0 Warnings: 0

mysql> truncate table student;
- g4 n$ t( n" nQuery OK, 0 rows affected

mysql> insert into student select * from temp;
& H0 ?. K4 P- F  L+ sQuery OK, 3 rows affected
' }7 Y9 L. H* Y6 ]8 N2 cRecords: 3 Duplicates: 0 Warnings: 0

mysql> select * from student;
+----+------+
" T( k% A5 l" R( w4 `" f1 I| ID | NAME |
: k, ]$ e7 Z1 |0 p' ]+----+------+
| 11 | aa |
# N3 f. P8 P2 J7 B& q0 c| 13 | bb |
| 16 | cc |
4 U  T5 k1 V5 h! [+----+------+
3 rows in set

mysql> drop temporary table temp;
Query OK, 0 rows affected
这个方法，显然存在效率问题。

---

方法2：按name分组，把最小的id保存到临时表，删除id不在最小id集合的记录，如下：
mysql> create temporary table temp as select min(id) as MINID from student group by name;
2 e1 T. a7 X" \; t, ?Query OK, 3 rows affected
* v% H, E5 m' b0 u( Y# q- N' _Records: 3 Duplicates: 0 Warnings: 0

mysql> delete from student where id not in (select minid from temp);
$ D" w  h* M% f% F! o8 ZQuery OK, 3 rows affected

mysql> select * from student;
+----+------+
| ID | NAME |
& ^1 W; g3 m' h+----+------+
| 11 | aa |
4 E/ r, ?. e/ X. I( || 13 | bb |
| 16 | cc |
+ M7 q( G7 y; D4 }  V' h+----+------+
3 rows in set

---

方法3：直接在原表上操作，容易想到的sql语句如下：

mysql> delete from student where id not in (select min(id) from student group by name);
执行报错：1093 - You can't specify target table 'student' for update in FROM clause
原因是：更新数据时使用了查询，而查询的数据又做了更新的条件，mysql不支持这种方式。
怎么规避这个问题？
+ |6 _7 g6 [3 i% d; s- K再加一层封装，如下：
. j. _7 u1 Z! g) W8 F0 f! {* ~mysql> delete from student where id not in (select minid from (select min(id) as minid from student group by name) b);
: ^* s! Q$ C% Y, kQuery OK, 3 rows affected

mysql> select * from student;
. w6 @* t7 P3 Q. {; ], O+----+------+
0 g! l* O! l# O! l. q9 y+ D4 I| ID | NAME |
+----+------+
| 11 | aa |
2 s4 n, {: ?" }  ?) Q6 C| 13 | bb |
| 16 | cc |
9 ~/ h2 C" ?. r+----+------+
3 rows in set

, K6 R( g/ ]* c- x$ @方法三例如：delete from bugdata where id not in (select minid from (select min(id) as minid from bugdata group by qxbh) b);

---

欢迎光临 中国网络渗透测试联盟 (https://cobjon.com/)

Powered by Discuz! X3.2