MySQL 删除重复记录

MySQL 删除重复记录

1. 问题

一个表中有600多万数据，其中在字段ts_code和trade_date上有索引，根据ts_code和trade_date 综合查询，有17多万的重复记录。
表上没有唯一的ID，不能用于区分相同的数据。

2.处理过程

(1) 复制表

先复制表结构

create table ts_performance_unique like ts_performance ;

增加ID自动增加列

alter table ts_performance_unique add column id int auto_increment primary key;

复制数据到新表

insert into ts_performance_unique select * from ts_performance;

(2) 过渡表

过渡表用于保存需要删除的重复记录的ID 。
创建表

create table unique_id (id int(11));

插入需要删除的重复数据ID

insert into unique_id select max(id) as id from ts_performance_unique t 
	group by concat(t.ts_code,t.trade_date)  having count(*)>1 ;

(3) 删除重复数据

用join inner 删除，效率高。

delete ts_performance_unique from ts_performance_unique  inner join unique_id on ts_performance_unique.id = unique_id.id ;

从600多万数据中删除17万的数据，只用了2秒！！！

千万不能用 in 删除，太慢！！！

delete  from ts_performance_unique where id in 
(
select id from unique_id
) ;

(4) 收尾

删除源表

drop table ts_performance;

把删除重复数据的表，修改为源表名

rename table ts_performance_unique to ts_performance;

删除增加的ID列

alter table ts_performance drop column id;

最后检查一下，索引情况
因为是用like复制的表结构，所以原来的索引都在。

show index in ts_performance;

3. 复制表Statement violates GTID consistency

用简单的表复制

create table ts_performance_unique select * from  ts_performance ;

报错：

> 1786 - Statement violates GTID consistency: CREATE TABLE ... SELECT.

原因：

在使用GTID（Global Transaction Identifier）的环境下，直接使用CREATE TABLE … SELECT语句复制表可能会遇到问题，因为这种操作会生成一个新的事务，而这个事务在复制环境中需要保持一致性。如果源表中包含有GTID信息的列，或者表结构中包含某些特性（如自动递增的ID），这种复制方式可能会影响GTID的一致性。

（1）方法一

使用CREATE TABLE … LIKE和INSERT INTO … SELECT方式复制表。

（1）方法二

如果有足够的权限，并且在复制完成后能够确保GTID的一致性，可以在复制期间暂时禁用GTID。

SET @@GLOBAL.ENFORCE_GTID_CONSISTENCY = OFF;
SET @@GLOBAL.SQL_REQUIRE_GTID = OFF;

create table ts_performance_unique select * from  ts_performance ;

SET @@GLOBAL.ENFORCE_GTID_CONSISTENCY = ON;
SET @@GLOBAL.SQL_REQUIRE_GTID = ON;

注意：
这种方法可能会对整个数据库的GTID状态产生影响，因此应当谨慎使用，尤其是在生产环境中，不推荐。