记一次mysql表重复数据清理与唯一索引添加

背景：

mysql新建数据表没有添加唯一索引，程序发版后，产生了很多脏数据，且在持续产生中，不能中断程序；

 

解决方案：

1. 创建临时表

-- 第1步：创建辅助表
CREATE TABLE charging_power_status_backup LIKE charging_power_status;
CREATE TABLE charging_power_status_temp LIKE charging_power_status;

3. 给表加 写锁，防止变更器件源表数据篡改

-- 第2步：锁定所有表（物理表不加别名）
LOCK TABLES 
    charging_power_status WRITE, 
    charging_power_status_backup WRITE, 
    charging_power_status_temp WRITE;

注意：

　　1）加锁时没有用别名，则后续执行sql时都不可用别名；

　　2）若连续执行两次lock tables，则MySQL 中，执行第二条 LOCK TABLES 语句会自动释放第一条持有的所有表锁。

4. 备份数据

truncate table charging_power_status_backup;

INSERT INTO charging_power_status_backup SELECT * FROM charging_power_status;

5. 去重插入，确认temp表是空的，被锁表不可使用别名；

TRUNCATE TABLE charging_power_status_temp;

DESC charging_power_status;　　# 确认有哪些列，便于后续插入

INSERT INTO charging_power_status_temp
SELECT host_id, power_id, is_deleted, update_time, status, create_time   -- 替换为你的实际列名
FROM (
    SELECT *,
           ROW_NUMBER() OVER (
               PARTITION BY host_id, power_id, is_deleted 
               ORDER BY update_time DESC
           ) AS rn
    FROM charging_power_status
) t
WHERE t.rn = 1;

6. 清空原表并回填

TRUNCATE TABLE charging_power_status;
INSERT INTO charging_power_status SELECT * FROM charging_power_status_temp;

7. 添加唯一索引

ALTER TABLE charging_power_status ADD UNIQUE INDEX idx_unique (host_id, power_id, is_deleted);

8. 释放锁

UNLOCK TABLES;

9. 清理临时表

DROP TABLE charging_power_status_temp;