如何使用游标在MariaDB中合并同组时间区间实体

针对你用游标处理时间区间合并时出现重复记录的问题，核心原因是每次处理新记录时未先检查临时结果中是否已有可合并的区间，直接插入导致重复。以下是修正后的游标处理方案，基于MariaDB 10.4.12编写：

步骤说明 #

1. 创建临时表存储合并结果：避免直接操作原表，同时方便逐行校验合并条件
2. 按分组+时间排序游标：保证同一组内按时间顺序处理，便于合并相邻或包含的区间
3. 逐行校验合并条件：处理每条记录时，先在临时表中查找同组可合并的区间，优先更新而非插入

完整存储过程代码 #

DELIMITER //

CREATE PROCEDURE merge_time_intervals()
BEGIN
    -- 声明变量存储游标取出的字段值
    DECLARE current_grp INT;
    DECLARE current_start DATE;
    DECLARE current_stop DATE;
    -- 声明游标结束标志
    DECLARE done INT DEFAULT FALSE;
    
    -- 声明游标：按分组和开始时间升序排序
    DECLARE interval_cursor CURSOR FOR
        SELECT grp, start_dt, stop_dt
        FROM your_table_name
        ORDER BY grp, start_dt;
    
    -- 绑定游标结束事件
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    
    -- 创建临时表存储合并后的结果（结构与原表一致）
    DROP TEMPORARY TABLE IF EXISTS merged_intervals;
    CREATE TEMPORARY TABLE merged_intervals (
        grp INT,
        start_dt DATE,
        stop_dt DATE,
        PRIMARY KEY (grp, start_dt) -- 加唯一键避免重复插入
    );
    
    -- 打开游标
    OPEN interval_cursor;
    
    -- 循环处理每条记录
    read_loop: LOOP
        FETCH interval_cursor INTO current_grp, current_start, current_stop;
        
        IF done THEN
            LEAVE read_loop;
        END IF;
        
        -- 检查同组中是否存在可合并的区间：要么当前start在已有区间内，要么已有stop+1天=当前start
        IF EXISTS (
            SELECT 1 FROM merged_intervals
            WHERE grp = current_grp
            AND (
                (current_start BETWEEN start_dt AND stop_dt)
                OR DATE_ADD(stop_dt, INTERVAL 1 DAY) = current_start
            )
        ) THEN
            -- 更新已有区间的stop_dt为两者的最大值
            UPDATE merged_intervals
            SET stop_dt = GREATEST(stop_dt, current_stop)
            WHERE grp = current_grp
            AND (
                (current_start BETWEEN start_dt AND stop_dt)
                OR DATE_ADD(stop_dt, INTERVAL 1 DAY) = current_start
            );
        ELSE
            -- 无匹配区间，插入新记录
            INSERT INTO merged_intervals (grp, start_dt, stop_dt)
            VALUES (current_grp, current_start, current_stop);
        END IF;
        
    END LOOP;
    
    -- 关闭游标
    CLOSE interval_cursor;
    
    -- 可选：将合并结果覆盖原表（根据需求调整）
    -- TRUNCATE TABLE your_table_name;
    -- INSERT INTO your_table_name SELECT * FROM merged_intervals;
    
    -- 输出合并结果（可选）
    SELECT * FROM merged_intervals;
END //

DELIMITER ;

关键细节说明 #

• 游标排序：ORDER BY grp, start_dt确保同一组内按时间顺序处理，后续的区间要么在已有区间之后，要么被包含，避免漏合并
• 唯一键约束：临时表的PRIMARY KEY (grp, start_dt)可以进一步防止重复插入同一区间
• 合并逻辑：先用EXISTS判断是否有可合并的记录，有则更新最大结束时间，无则插入，从根源避免重复记录
• 临时表使用：临时表仅在会话中存在，不会污染原数据，测试完成后再决定是否覆盖原表

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