MySQL如何创建视图按时间间隔累加拆分单行为多行

以下方案默认你的原表名为time_interval，可根据实际业务表名替换

MySQL 8.0+ 版本实现（无需额外建表） #

直接通过递归CTE创建视图即可，不需要依赖额外辅助表：

CREATE VIEW v_interval_expand AS
WITH RECURSIVE time_iter AS (
    -- 初始行：直接取原表记录
    SELECT
        id,
        `start` AS current_time,
        `end`,
        interval1,
        interval2,
        (interval1 + interval2) * 60 AS step_seconds
    FROM time_interval
    UNION ALL
    -- 递归累加步长，直到到达结束时间
    SELECT
        id,
        DATE_ADD(current_time, INTERVAL step_seconds SECOND),
        `end`,
        interval1,
        interval2,
        step_seconds
    FROM time_iter
    WHERE current_time < `end`
)
SELECT
    id,
    current_time AS `start`,
    `end`,
    interval1,
    interval2
FROM time_iter;

使用时直接按id过滤即可：

SELECT * FROM v_interval_expand WHERE id = 1;

返回结果和你给出的预期完全匹配。

注意事项 #

• 时间计算统一转成秒累加，避免跨小时、跨天场景下的计算误差
• MySQL默认递归最大深度为1000，如果你的时间跨度大、步长小，单条记录生成行数可能超过1000，查询前执行SET SESSION cte_max_recursion_depth = 10000;调整上限即可
• 字段名start、end是MySQL保留字，建表和写查询时建议用反引号包裹，避免语法报错

MySQL 5.x 版本兼容实现 #

5.x版本不支持递归CTE，需要提前创建一张连续数字辅助表实现序列生成：

-- 创建数字辅助表，插入0-999的连续整数，可根据需求扩展更大范围
CREATE TABLE num_helper (n INT PRIMARY KEY);
INSERT INTO num_helper(n)
SELECT h.i + t.i*10 + h2.i*100
FROM
    (SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) h,
    (SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t,
    (SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) h2;

基于辅助表创建视图：

CREATE VIEW v_interval_expand AS
SELECT
    t.id,
    DATE_ADD(t.`start`, INTERVAL n.n * (t.interval1 + t.interval2)*60 SECOND) AS `start`,
    t.`end`,
    t.interval1,
    t.interval2
FROM time_interval t
INNER JOIN num_helper n
ON DATE_ADD(t.`start`, INTERVAL n.n * (t.interval1 + t.interval2)*60 SECOND) <= t.`end`;

查询方式和8.0版本完全一致。

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