MySQL：查询缺失的5分钟间隔记录及插入异表记录方法

没问题，我来帮你搞定这两个需求——找出缺失的5分钟间隔记录，再把这些缺失项插入表中。咱们分步骤来：

核心思路是：先生成一个覆盖你现有数据时间范围的连续5分钟时间序列，再把这个序列和你的表做左连接，找不到匹配的就是缺失的间隔。

方法1：MySQL 8.0+ 用递归CTE（推荐） #

递归CTE可以轻松生成连续的时间序列，代码如下：

WITH RECURSIVE time_intervals AS (
    -- 起始时间：取表中最早的记录时间，向下对齐到最近的5分钟整点
    SELECT 
        FROM_UNIXTIME(FLOOR(UNIX_TIMESTAMP(MIN(fecha))/(5*60))*(5*60)) AS interval_start
    FROM your_table
    UNION ALL
    -- 每次累加5分钟，直到超过表中最晚的记录时间
    SELECT interval_start + INTERVAL 5 MINUTE
    FROM time_intervals
    WHERE interval_start + INTERVAL 5 MINUTE <= (SELECT MAX(fecha) FROM your_table)
)
-- 左连接原表，筛选出没有匹配的时间间隔
SELECT ti.interval_start AS missing_datetime
FROM time_intervals ti
LEFT JOIN your_table t 
    ON FROM_UNIXTIME(FLOOR(UNIX_TIMESTAMP(t.fecha)/(5*60))*(5*60)) = ti.interval_start
WHERE t.id IS NULL;

这里用UNIX_TIMESTAMP转成时间戳再取整的方式，能精准处理带秒数的时间（如果你的fecha字段有秒的话），避免格式化字符串可能带来的误差。

方法2：MySQL 5.x 用临时数字表 #

如果你的MySQL版本不支持CTE，可以用一个手动生成的数字表来构造时间序列：

-- 生成足够多的数字（这里生成了0-6，覆盖30分钟范围，不够的话可以加更多UNION ALL）
SELECT 
    FROM_UNIXTIME(FLOOR(UNIX_TIMESTAMP(MIN(fecha))/(5*60))*(5*60)) + INTERVAL (n*5) MINUTE AS missing_datetime
FROM your_table,
(SELECT 0 n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6) nums
WHERE FROM_UNIXTIME(FLOOR(UNIX_TIMESTAMP(MIN(fecha))/(5*60))*(5*60)) + INTERVAL (n*5) MINUTE <= MAX(fecha)
GROUP BY missing_datetime
LEFT JOIN your_table t 
    ON FROM_UNIXTIME(FLOOR(UNIX_TIMESTAMP(t.fecha)/(5*60))*(5*60)) = missing_datetime
WHERE t.id IS NULL;

如果你的时间范围超过30分钟，只要在nums子查询里多加几个数字就行。

知道了缺失的时间，直接用INSERT ... SELECT语法就能批量插入，不用手动一条一条加。

方法1：MySQL 8.0+ 基于CTE插入 #

WITH RECURSIVE time_intervals AS (
    SELECT 
        FROM_UNIXTIME(FLOOR(UNIX_TIMESTAMP(MIN(fecha))/(5*60))*(5*60)) AS interval_start
    FROM your_table
    UNION ALL
    SELECT interval_start + INTERVAL 5 MINUTE
    FROM time_intervals
    WHERE interval_start + INTERVAL 5 MINUTE <= (SELECT MAX(fecha) FROM your_table)
)
INSERT INTO your_table (fecha) -- 如果有其他必填字段，记得加上对应的列名
SELECT ti.interval_start
FROM time_intervals ti
LEFT JOIN your_table t 
    ON FROM_UNIXTIME(FLOOR(UNIX_TIMESTAMP(t.fecha)/(5*60))*(5*60)) = ti.interval_start
WHERE t.id IS NULL;

方法2：MySQL 5.x 基于数字表插入 #

INSERT INTO your_table (fecha)
SELECT 
    FROM_UNIXTIME(FLOOR(UNIX_TIMESTAMP(MIN(fecha))/(5*60))*(5*60)) + INTERVAL (n*5) MINUTE AS interval_start
FROM your_table,
(SELECT 0 n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6) nums
WHERE FROM_UNIXTIME(FLOOR(UNIX_TIMESTAMP(MIN(fecha))/(5*60))*(5*60)) + INTERVAL (n*5) MINUTE <= MAX(fecha)
GROUP BY interval_start
LEFT JOIN your_table t 
    ON FROM_UNIXTIME(FLOOR(UNIX_TIMESTAMP(t.fecha)/(5*60))*(5*60)) = interval_start
WHERE t.id IS NULL;

注意：如果你的表还有其他必填字段（比如value、status之类的），一定要在INSERT和SELECT中补充对应的字段值。比如默认值为0的话，就写成INSERT INTO your_table (fecha, value) SELECT ti.interval_start, 0 ...

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