在实际应用中,很可能会碰到一些需要删除某些字段的重复记录,我现在把我能想到的写下来,望高手们补充。
1、
具体实现如下:
Table Create Table
------------ --------------------------------------------------------
users_groups CREATE TABLE `users_groups` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`uid` int(11) NOT NULL,
`gid` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8
users_groups.txt内容:
1,11,502
2,107,502
3,100,503
4,110,501
5,112,501
6,104,502
7,100,502
8,100,501
9,102,501
10,104,502
11,100,502
12,100,501
13,102,501
14,110,501
mysql> load data infile 'c:\\users_groups.txt' into table users_groups fields
terminated by ',' lines terminated by '\n';
Query OK, 14 rows affected (0.05 sec)
Records: 14 Deleted: 0 Skipped: 0 Warnings: 0
mysql> select * from users_groups;
query result(14 records)
id |
uid |
gid |
1 |
11 |
502 |
2 |
107 |
502 |
3 |
100 |
503 |
4 |
110 |
501 |
5 |
112 |
501 |
6 |
104 |
502 |
7 |
100 |
502 |
8 |
100 |
501 |
9 |
102 |
501 |
10 |
104 |
502 |
11 |
100 |
502 |
12 |
100 |
501 |
13 |
102 |
501 |
14 |
110 |
501 |
14 rows in set (0.00 sec)
根据一位兄弟的建议修改。mysql> create temporary table tmp_wrap select * from users_groups group by uid having count(1) >= 1;Query OK, 7 rows affected (0.11 sec)
Records: 7 Duplicates: 0 Warnings: 0
mysql> truncate table users_groups;Query OK, 14 rows affected (0.03 sec)
mysql> insert into users_groups select * from tmp_wrap;Query OK, 7 rows affected (0.03 sec)
Records: 7 Duplicates: 0 Warnings: 0
mysql> select * from users_groups;
query result(7 records)
id |
uid |
gid |
1 |
11 |
502 |
2 |
107 |
502 |
3 |
100 |
503 |
4 |
110 |
501 |
5 |
112 |
501 |
6 |
104 |
502 |
9 |
102 |
501 |
mysql> drop table tmp_wrap;Query OK, 0 rows affected (0.05 sec)
2、还有一个很精简的办法。
查找重复的,并且除掉最小的那个。
delete users_groups as a from users_groups as a,
(
select *,min(id) from users_groups group by uid having count(1) > 1
) as b
where a.uid = b.uid and a.id > b.id;
(7 row(s)affected)
(0 ms taken)
query result(7 records)
id |
uid |
gid |
1 |
11 |
502 |
2 |
107 |
502 |
3 |
100 |
503 |
4 |
110 |
501 |
5 |
112 |
501 |
6 |
104 |
502 |
9 |
102 |
501 |
3、现在来看一下这两个办法的效率。
运行一下以下SQL 语句
create index f_uid on users_groups(uid);
explain select * from users_groups group by uid having count(1) > 1 union all
select * from users_groups group by uid having count(1) = 1;
explain select * from users_groups as a,
(
select *,min(id) from users_groups group by uid having count(1) > 1
) as b
where a.uid = b.uid and a.id > b.id;
query result(3 records)
id |
select_type |
table |
type |
possible_keys |
key |
key_len |
ref |
rows |
Extra |
1 |
PRIMARY |
users_groups |
index |
(NULL) |
f_uid |
4 |
(NULL) |
14 |
|
2 |
UNION |
users_groups |
index |
(NULL) |
f_uid |
4 |
(NULL) |
14 |
|
(NULL) |
UNION RESULT |
<union1,2> |
ALL |
(NULL) |
(NULL) |
(NULL) |
(NULL) |
(NULL) |
|
query result(3 records)
id |
select_type |
table |
type |
possible_keys |
key |
key_len |
ref |
rows |
Extra |
1 |
PRIMARY |
<derived2> |
ALL |
(NULL) |
(NULL) |
(NULL) |
(NULL) |
4 |
|
1 |
PRIMARY |
a |
ref |
PRIMARY,f_uid |
f_uid |
4 |
b.uid |
1 |
Using where |
2 |
DERIVED |
users_groups |
index |
(NULL) |
f_uid |
4 |
(NULL) |
14 |
|
很明显的第二个比第一个扫描的函数要少。
分享到:
相关推荐
MySQL删除重复记录方法。
本文主要给大家介绍了关于MySQL中查询、删除重复记录的方法,分享出来供大家参考学习,下面来看看详细的介绍: 查找所有重复标题的记录: select title,count(*) as count from user_table group by title having ...
删除重复记录 删除数据表中的重复记录,包括MySQL、SQL Server、Oracle等
mysql彻底理解删除重复记录sql脚本,只保留一条记录
表结构: mysql> desc demo; +——-+——————+——+—–+———+—————-+ | Field | Type | Null | Key | Default | Extra | +——-+——————+——+—–+———+—————-+ | id | int(11) unsigned |...
通过存储过程,删除Mysql数据库表中的重复记录,只保留一条;
查询及删除重复记录的SQL语句,虽然有点乱,但内容还是不错的。
MYSQL 查询和删除重复记录的方法很多,下面为您介绍几种常用的 MYSQL 查询和删除重复记录的方法,希望对您查询和删除重复数据方面能有所帮助。 SQL重复记录查询的几种方法: 1. 查找表中多余的重复记录,重复...
采用的是下面的方法可删除,假设重复的是test数据库中的title字段 代码如下:create table bak as (select * from test group by title having count(*)=1); insert into bak (select * from test...
本文实例讲述了mysql优化小技巧之去除重复项实现方法。分享给大家供大家参考,具体如下: 说到这个去重,脑仁不禁得一疼,尤其是出具量比较大的时候。毕竟咱不是专业的DB,所以嘞,只能自己弄一下适合自己去重方法了...
今天我们就来谈谈如何查找 MySQL 表中的重复数据以及如何删除这些重复的记录。 创建示例表 首先创建一个示例表 people 并生成一些数据: drop table if exists people; create table people ( id int auto_...
有些 MySQL 数据表中可能存在重复的记录,有些...让我们尝试一个实例:下表中无索引及主键,所以该表允许出现多条重复记录。 CREATE TABLE person_tbl ( first_name CHAR(20), last_name CHAR(20), sex CHAR(10) )
代码如下:CREATETABLE`users`(`id`int(10)NOTNULLAUTO_...原因是mysql删除动作不能带有本表的查询动作,意思是你删除users表的东西不能以users表的信息为条件所以这个语句会报错,执行不了。只要通过创建临时表作
MySQL数据库中查询重复数据 select * from employee group by emp_name having count (*)>1; Mysql 查询可以删除的重复数据 select t1.* from employee t1 where (t1.emp_name) in (select t4.emp_name from ...
注:此处“重复”非完全重复,意为某字段数据重复 HZT表结构 ID int Title nvarchar(50) AddDate datetime 数据 一. 查找重复记录 1. 查找全部重复记录 Select * From 表 Where 重复字段 In (Select ... 删除重复记录
我想删除表event中sid重复的记录,请问有没有这样SQL语句?或是通过其它方法? 代码如下:delete from event as e where id != (select min(id) from event where sid=e.sid); or 代码如下:delete ...
如下sql,找出重复的记录,和重复记录中ID值最小的记录(表中ID为自增长) select MIN(ID) as id, StructSN ,Date,UserID,StarCount,COUNT(StructSN) as c from T_Dor_StructStar where Date >= '20160919' group ...