如何在MySQL中查找并插入缺失数据且不生成重复记录

前置要求 #

首先需要为表创建唯一联合索引，用于避免重复插入相同指标、参考日期、指标日期的记录：

ALTER TABLE appweb_ind_exeindicadores ADD UNIQUE INDEX idx_unique_indicator_date (fk_indicador_id, datareferencia, dataindicador);

补数逻辑 #

通过递归CTE生成指定范围内的完整日期序列，和全表所有(fk_indicador_id, datareferencia)组合做笛卡尔积得到所有应该存在的记录，插入时自动跳过已存在的记录即可。

MySQL 8.0+ 实现代码 #

INSERT IGNORE INTO appweb_ind_exeindicadores (fk_indicador_id, datareferencia, dataindicador)
WITH RECURSIVE date_series AS (
    -- 可根据实际需要修改补数的起止日期
    SELECT DATE('2021-09-01') AS ds
    UNION ALL
    SELECT ds + INTERVAL 1 DAY FROM date_series WHERE ds < DATE('2021-09-30')
),
indicator_groups AS (
    SELECT DISTINCT fk_indicador_id, datareferencia 
    FROM appweb_ind_exeindicadores
    -- 如需仅补特定月份的数据，可添加过滤条件，如：
    -- WHERE datareferencia >= '2021-09-01' AND datareferencia < '2021-10-01'
)
SELECT 
    ig.fk_indicador_id, 
    ig.datareferencia, 
    ds.ds AS dataindicador
    -- 如需补其他字段的默认值，可在此处添加对应字段，如 0 AS indicator_value, NOW() AS create_time
FROM indicator_groups ig
CROSS JOIN date_series ds
-- 限定指标日期和参考日期为同一年月，可根据业务规则调整
WHERE MONTH(ds.ds) = MONTH(ig.datareferencia) AND YEAR(ds.ds) = YEAR(ig.datareferencia);

PostgreSQL 实现代码 #

INSERT INTO appweb_ind_exeindicadores (fk_indicador_id, datareferencia, dataindicador)
WITH RECURSIVE date_series AS (
    SELECT '2021-09-01'::DATE AS ds
    UNION ALL
    SELECT ds + INTERVAL '1 day' FROM date_series WHERE ds < '2021-09-30'::DATE
),
indicator_groups AS (
    SELECT DISTINCT fk_indicador_id, datareferencia 
    FROM appweb_ind_exeindicadores
)
SELECT ig.fk_indicador_id, ig.datareferencia, ds.ds AS dataindicador
FROM indicator_groups ig
CROSS JOIN date_series ds
WHERE EXTRACT(MONTH FROM ds.ds) = EXTRACT(MONTH FROM ig.datareferencia) 
AND EXTRACT(YEAR FROM ds.ds) = EXTRACT(YEAR FROM ig.datareferencia)
ON CONFLICT (fk_indicador_id, datareferencia, dataindicador) DO NOTHING;

注意事项 #

• INSERT IGNORE（MySQL）和ON CONFLICT DO NOTHING（PG）会自动跳过唯一索引冲突的记录，已存在的日期数据不会被覆盖或重复插入
• 如果你的业务要求dataindicador的范围不是和datareferencia同月，可自行调整WHERE语句的过滤条件
• 如需补全其他字段的值，只需在INSERT的字段列表和SELECT查询中添加对应字段的默认值即可

内容的提问来源于stack exchange，提问作者GABRIEL MELLO