xml地图|网站地图|网站标签 [设为首页] [加入收藏]
当前位置: www8029com > 澳门新葡8522最新网站 > 正文

MariaDB删除重复记录,三种方法

时间:2019-11-25 19:13来源:澳门新葡8522最新网站
不管是程序BUG,还是业务变更,重复数据这个老生常谈的问题,总是会出现。以下是我在MariaDB或是MySQL下处理的一些经验。在SQLServer中,使用窗口函数是很容易实现的。不过听说MySQL

不管是程序BUG,还是业务变更,重复数据这个老生常谈的问题,总是会出现。以下是我在MariaDB或是MySQL下处理的一些经验。在SQL Server中,使用窗口函数是很容易实现的。不过听说MySQL 8.0和MariaDB 10.2以上均支持窗口函数了。等有机会再来测试使用窗口函数来删除重复记录。

本文介绍了Sql Server数据库中删除数据表中重复记录的方法。

背景

表t_record中的数据fromUserId, toUserId两个字段组合作为唯一的标识,删除重复记录,只留下最大id(或最新时间)的记录。id为自增无重复的主键。

表t_record的id作为自增的主键。

表t_record大概有6万多的数据。以下测试均在资源很差的主机上,t_record没有在使用的情况下的结果。

[项目]
数据库中users表,包含u_name,u_pwd两个字段,其中u_name存在重复项,现在要实现把重复的项删除!
[分析]
1、生成一张临时表new_users,表结构与users表一样;
2、对users表按id做一个循环,每从users表中读出一个条记录,判断new_users中是否存在有相同的u_name,如果没有,则把它插入新表;如果已经有了相同的项,则忽略此条记录;
3、把users表改为其它的名称,把new_users表改名为users,实现我们的需要。
[程序]

方法1

查询重复的记录

SELECT fromUserId, toUserId, count(*)
FROM t_record as tr
GROUP BY fromUserId, toUserId
HAVING count(*) > 1;

把重复记录的两个字段放到临时表_tmp1中

CREATE TABLE _tmp1
SELECT fromUserId, toUserId
FROM t_record as tr
GROUP BY fromUserId, toUserId
HAVING count(*) > 1;

把应该删除的id查询出来,放到临时表_tmp2中

CREATE TABLE _tmp2
SELECT id
FROM t_record as a
WHERE (a.fromUserId, a.toUserId) in (

SELECT fromUserId, toUserId from _tmp1
)
and a.id not in (
SELECT MAX(id)
FROM t_record as tr
GROUP BY fromUserId, toUserId
HAVING count(*) > 1
);

删除原表的记录

DELETE from t_record
where id in (
    SELECT id from _tmp2
);

通过以前3个步骤,没有删除数据大概需要23秒左右。

declare @id int,@u_name varchar(50),@u_pwd varchar(50)
set @id=1
while @id<1000
begin
if exists (select u_name from users where u_id=@id) 
begin
select @u_name=u_name,@u_pwd=u_pwd from users where u_id=@id --获取源数据
if not exists (select u_name from new_users where u_name=@u_name) -- 判断是否有重复的U-name项
begin
insert into new_users(u_name,u_pwd) values(@u_name,@u_pwd)
end
end
set @id=@id 1
end
select * from new_users

方法2

如果表中没有主键,也没有可以标识唯一记录的字段。只能是把原表的数据分表后,插入到另一张临时表,删除原表数据,把临时表的数据导回来。

这种方法也适用合于有主键或有唯一标识的表,但操作过程中会影响在线的业务,需要中断业务。否则可能会造成数据丢失或数据不一致。
数据量大的表,导两次数据,过程会很慢,同时也需要注意硬盘空间是否足够。

[方法二]

方法3

测试mysql不支持以下这种delete语法来删除数据。改为select id 存到临时表,查询非常慢。

DELETE a 
FROM table_nam a
WHERE EXISTS (SELECT 1 FROM table_nam b
                 WHERE b.userid = a.userid AND b.CreateDate > a.CreateDate);

假设Users表中有相同的name项,id为主键标识字段。现在要求去掉Users中重复的name项。
1、把不重复的ID保存在一个tmp1表里面。
select min([id]) as [id] into tmp1 from Users group by [name]

方法4

在mariadb 10.1.19下测试,60多秒。
这个方法简单,只需要一条语句,速度还行。

DELETE 
from t_record 
where id not in (
        select maxid from 
                (select max(id) as  maxid from  t_record
                        group by fromUserId,toUserId                
                ) b
);

现对方法4的进行改造,再测试下。11.5秒。快好几倍了。

CREATE OR REPLACE TABLE _tmp3
SELECT id
FROM t_record 
WHERE id NOT IN (
        SELECT maxid FROM 
                (SELECT max(id) AS  maxid FROM t_record
                        GROUP BY fromUserId,toUserId                
                ) b
);

DELETE FROM t_record
WHERE id IN (
        SELECT id from _tmp3
);

对临时表创建主键,再测试下。1.2秒!WOW!!!

CREATE OR REPLACE TABLE _tmp3 (id INT NOT NULL PRIMARY KEY);

INSERT INTO _tmp3 (id)
SELECT id
FROM t_record 
WHERE id NOT IN (
        SELECT maxid FROM 
                (SELECT max(id) AS  maxid FROM t_record
                        GROUP BY fromUserId,toUserId                
                ) b
);

DELETE FROM t_record 
        WHERE id IN (
                SELECT id from _tmp3
);

还以不能再快呢?改IN子句为JOIN,再测试下。1秒!

CREATE OR REPLACE TABLE _tmp3 (id INT NOT NULL PRIMARY KEY);

INSERT INTO _tmp3 (id)
SELECT id
FROM t_record 
WHERE id NOT IN (
        SELECT maxid FROM 
                (SELECT max(id) AS  maxid FROM t_record
                        GROUP BY fromUserId,toUserId                
                ) b
);

DELETE a FROM t_record as a INNER JOIN _tmp3 as b on b.id = a.id;

难道删除的那个语句的执行计划是不同的吗?由于mysql只支持select的执行查询,所以把删除的语句修改为查询语句。

root@localhost [db1]EXPLAIN SELECT id FROM t_record WHERE id IN ( SELECT id from _tmp3 );
 ------ ------------- ------------------- -------- --------------- --------- --------- -------------------- ------ -------------------------- 
| id   | select_type | table             | type   | possible_keys | key     | key_len | ref                | rows | Extra                    |
 ------ ------------- ------------------- -------- --------------- --------- --------- -------------------- ------ -------------------------- 
|    1 | PRIMARY     | _tmp3             | index  | PRIMARY       | PRIMARY | 4       | NULL               |  452 | Using index              |
|    1 | PRIMARY     | t_record          | eq_ref | PRIMARY       | PRIMARY | 8       | testdb._tmp3.id |    1 | Using where; Using index |
 ------ ------------- ------------------- -------- --------------- --------- --------- -------------------- ------ -------------------------- 
2 rows in set (0.00 sec)

root@localhost [db1]EXPLAIN SELECT a.id FROM t_record as a INNER JOIN _tmp3 as b on b.id = a.id;
 ------ ------------- ------- -------- --------------- --------- --------- ---------------- ------ -------------------------- 
| id   | select_type | table | type   | possible_keys | key     | key_len | ref            | rows | Extra                    |
 ------ ------------- ------- -------- --------------- --------- --------- ---------------- ------ -------------------------- 
|    1 | SIMPLE      | b     | index  | PRIMARY       | PRIMARY | 4       | NULL           |  452 | Using index              |
|    1 | SIMPLE      | a     | eq_ref | PRIMARY       | PRIMARY | 8       | testdb.b.id    |    1 | Using where; Using index |
 ------ ------------- ------- -------- --------------- --------- --------- ---------------- ------ -------------------------- 
2 rows in set (0.01 sec)

对比执行计划,是一样的。但测试多次,用JOIN方式速度还是快些。为什么呢?

2、从Users表中选取tmp1表中的id项,将相应id的数据写入表tmp2
澳门新葡8522最新网站,select * into tmp2 from Users where [id] in( select [id] from tmp1)

3、把Users、tmp1两张表Drop掉
drop table Users
drop table tmp1

4、把tmp2表改名为User表
[注]如果没有主键标识id,可以增加一个标识字段,方法如下:
select identity(int,1,1) as autoID, * into tmp0 from Users
[情况三](脚本学堂 www.jbxue.com)
假设有一个User表,id为主键标识字段,但有一些完全重复的项。现在要求去掉Users中这些完全重复的项,只保留一条。
1、把不重复的数据保存在tmp1表中
select distinct * into tmp1 from Users

2、把Users表删除
drop table Users

3、把tmp1表中的数据导入到Users表
select * into Users from tmp1

4、把tmp1表删除
drop table tmp1

参考链接:

  • sql server中distinct筛选重复记录的用法举例
  • sql server 查询重复记录的多种方法
  • sql server 重复记录的取最新一笔的实现方法
  • 如何在SQL Server2008中删除重复记录
  • sql server删除重复记录且只余一条的例子
  • 一条sql语句删除表中重复记录

编辑:澳门新葡8522最新网站 本文来源:MariaDB删除重复记录,三种方法

关键词: www8029com

  • 上一篇:没有了
  • 下一篇:没有了