Mysql重复写入同样的数据怎么做清理？

Mysql重复写入同样的数据怎么做清理？

发生背景

因为不小心写了个BUG，导致在对一批数据做完预处理插入到数据库（mysql）的过程中，插入的工作重复执行了2/次，每批数据大概有20多行，所以数据库中就会有20/40行的脏数据，这些脏数据除了自增的主键（id）和记录插入数据的时间字段（created_at）以外的其他字段的值都是一样的。

还原背景的代码：

import pymysql
from datetime import datetime
import time

# 数据库连接配置
db_config = {
    'host': 'localhost',
    'user': 'your_name',
    'password': 'your_passwd',
    'database': 'mytest'
}

# 更新数据的函数
def insert_data(connection):
    for _ in range(2):
        with () as cursor:
            insert_time = ()
            sql = """
                ISERT ITO del_data_test (name, msg, created_at) VALUES (%s, %s, %s)
            """
            # 使用 executemany 批量插入
            data = [
                ("xiaoming", "I'm fine.", insert_time),
                ("laowang", "good", insert_time),
                ("A", "A to A", insert_time)
            ]
            (sql, data)
            connectionmit()
    time.sleep(0.5)
    for _ in range():
        with () as cursor:
            insert_time = ()
            sql = """
                ISERT ITO del_data_test (name, msg, created_at) VALUES (%s, %s, %s)
            """
            # 使用 executemany 批量插入
            data = [
                ("zhangsan", "I'm bad.", insert_time),
                ("mazi", "I'm back", insert_time)
            ]
            (sql, data)
            connectionmit()

# 主函数
def main():
    try:
        connection = (**db_config)
        insert_data(connection)
    finally:
        ()  # 确保连接被关闭

if __name__ == "__main__":
    main()

数据展示：

解下来我们需要将重复次数的脏数据都清理掉，每批数据只保留一次。

解决方案

通过GROUP BY和MI()到每组重复数据中的最小id，然后删除不在这个集合中的记录。

DELETE FROM del_data_test
WHERE id OT I (
    SELECT MI(id)
    FROM del_data_test
    GROUP BY name, msg
);

但是在执行的时候我发现报了一个奇怪的错误！

ERROR 109 (HY000): You can't specify target table 'del_data_test' for update in FROM clause

这个错误是由于 MySQL 的限制：在DELETE或UPDATE语句中，不能直接引用正在操作的表（即不能在子查询中直接使用del_data_test表），所以我们需要绕过这个限制。我这里提供两种绕开的方式。

解决方案 1：使用临时表

将子查询的结果存储到一个临时表中，然后在 DELETE 语句中引用临时表。

-- 创建临时表，存储每组中的最小 id
CREATE TEMPORARY TABLE temp_table AS
SELECT MI(id) AS id
FROM del_data_test
GROUP BY name, msg;

-- 删除不在临时表中的记录
DELETE FROM del_data_test
WHERE id OT I (SELECT id FROM temp_table);

-- 删除临时表
DROP TEMPORARY TABLE temp_table;

解决方案 2：使用嵌套子查询

将子查询再嵌套一层，使其不直接引用正在操作的表。

DELETE FROM del_data_test
WHERE id OT I (
    SELECT id
    FROM (
        SELECT MI(id) AS id
        FROM del_data_test
        GROUP BY name, msg
    ) AS temp
);

我使用的是第二种方案绕开限制，执行结果 如下：

我们先将表中的数据恢复至最初的状态，然后使用方法2来进行去除重复数据的操纵。

在数据库中执行以下命令：

truncate table your_table_name;

使用 TRUCATE 语句可以快速清空表中的所有数据。TRUCATE 不仅会删除所有行，还会重置自增主键的计数器。

然后重复运行脚本，得到脏数据。

接下来我们来验证使用方法2是否能有效的删除重复的数据。

方法2是通过自连接到每组重复数据中的最小id，然后删除其他记录。sql语句如下：

DELETE w1
FROM del_data_test w1
JOI del_data_test w2
O  = 
AD  = 
AD w1.id > w2.id;

执行结果如下：

结果：success！

结尾

如果你遇到的情况更加的复杂，你的数据库表设计更加复杂，建议使用窗口函数来删除脏数据，不过需要注意的是窗口函数只在8.0版本以上支持，如果你使用的是低版本的mysql更建议以上的方式。

当然还有更多的方法可以达到清理脏数据的目的，欢迎在评论区分享你的方法和你遇到的问题！