mysql 天表数据合并

INSERT INTO table_merged (id, data, valid_from, valid_to)
SELECT 
    COALESCE(table_A.id, table_B.id) AS id,
    COALESCE(table_A.data, table_B.data) AS data,
    GREATEST(table_A.valid_from, table_B.valid_from) AS valid_from,
    LEAST(table_A.valid_to, table_B.valid_to) AS valid_to
FROM 
    table_A
FULL OUTER JOIN 
    table_B
ON 
    table_A.id = table_B.id
    AND table_A.valid_from <= table_B.valid_to
    AND table_A.valid_to >= table_B.valid_from;

UPDATE table_merged
SET valid_to = LEAST(table_A.valid_to, table_B.valid_to)
WHERE 
    id IN (
        SELECT id 
        FROM table_A 
        FULL OUTER JOIN table_B 
        ON table_A.id = table_B.id 
        AND table_A.valid_from <= table_B.valid_to 
        AND table_A.valid_to >= table_B.valid_from
    );

-- 创建索引
CREATE INDEX idx_valid_from ON table_A(valid_from);
CREATE INDEX idx_valid_to ON table_B(valid_to);

-- 分批处理
SET @batch_size = 1000;
SET @offset = 0;

WHILE @offset < (SELECT COUNT(*) FROM table_A FULL OUTER JOIN table_B ON table_A.id = table_B.id) DO
    INSERT INTO table_merged (id, data, valid_from, valid_to)
    SELECT 
        COALESCE(table_A.id, table_B.id) AS id,
        COALESCE(table_A.data, table_B.data) AS data,
        GREATEST(table_A.valid_from, table_B.valid_from) AS valid_from,
        LEAST(table_A.valid_to, table_B.valid_to) AS valid_to
    FROM 
        table_A
    FULL OUTER JOIN 
        table_B
    ON 
        table_A.id = table_B.id
        AND table_A.valid_from <= table_B.valid_to
        AND table_A.valid_to >= table_B.valid_from
    LIMIT @batch_size OFFSET @offset;
    
    SET @offset = @offset + @batch_size;
END WHILE;