MySQL使用SQL语句查重去重原创

# 前言

由于唯一索引调整，导致程序重复写入数据。需要使用SQL语句进行去重并删除重复数据。

# 模板

点击查看SQL模板

SELECT * FROM
  table_name AS ta
WHERE
  ta.唯一键 <> (
    SELECT
      max(tb.唯一键)
    FROM
      table_name AS tb
    WHERE
      ta.判断重复的列 = tb.判断重复的列
  );

1
2
3
4
5
6
7
8
9
10
11

# 实例

点击查看SQL实例

SELECT * FROM
  my_test_table_name
WHERE
  id IN (
    SELECT id
    FROM (
      SELECT *
      FROM my_test_table_name
      WHERE updated_at BETWEEN '2022-03-15 18:20:00' AND '2022-03-15 19:30:00'
    ) AS ta
    WHERE ta.id <> (
      SELECT max(tb.id)
      FROM (
        SELECT *
        FROM my_test_table_name
        WHERE updated_at BETWEEN '2022-03-15 18:20:00' AND '2022-03-15 19:30:00'
      ) AS tb
      WHERE
        ta.tran_start_time = tb.tran_start_time
        AND ta.tran_end_time = tb.tran_end_time
        AND ta.member_account = tb.member_account
    )
  );

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

# 删除重复数据

将查询语句换成DELETE语句，可以直接删除重复数据。

点击查看删除语句

DELETE FROM
  my_test_table_name
WHERE
  id IN (
    SELECT id
    FROM (
      SELECT *
      FROM my_test_table_name
      WHERE updated_at BETWEEN '2022-03-15 18:20:00' AND '2022-03-15 19:30:00'
    ) AS ta
    WHERE ta.id <> (
      SELECT max(tb.id)
      FROM (
        SELECT *
        FROM my_test_table_name
        WHERE updated_at BETWEEN '2022-03-15 18:20:00' AND '2022-03-15 19:30:00'
      ) AS tb
      WHERE
        ta.tran_start_time = tb.tran_start_time
        AND ta.tran_end_time = tb.tran_end_time
        AND ta.member_account = tb.member_account
    )
  );

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

通过使用上述SQL语句，可以有效地查找并删除重复数据，确保数据的唯一性和完整性。

#重复数据处理 #SQL #数据清理

上次更新: 9/14/2024

← MySQL死锁问题 MySQLdump逻辑备份→