mysql 8.0 时间维度表生成（可运行）

CREATE TABLE `dim_time` (
  `time_key` int NOT NULL COMMENT '唯一的时间键，表示一天中的秒数 (0 - 86399)',
  `time_value` time DEFAULT NULL COMMENT '一天中的具体时间值，格式为HH:MM:SS',
  `hour24` tinyint DEFAULT NULL COMMENT '24小时制的小时数 (0 - 23)',
  `hour12` tinyint DEFAULT NULL COMMENT '12小时制的小时数 (1 - 12)',
  `minutes` tinyint DEFAULT NULL COMMENT '分钟 (0 - 59)',
  `seconds` tinyint DEFAULT NULL COMMENT '秒数 (0 - 59)',
  `am_pm` char(2) DEFAULT NULL COMMENT '时间的上午/下午标识 (AM/PM)',
  PRIMARY KEY (`time_key`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='时间维度表，记录一天中每秒的时间信息';

```sql
CREATE PROCEDURE generate_dim_time()
begin
  -- SET SESSION cte_max_recursion_depth = 100000;
    -- 清空表，确保数据为全新的
    DELETE FROM dim_time;
   
   -- 使用递归CTE生成一天中的秒数(0 到 86399)
   
   INSERT INTO dim_time (time_key, time_value, hour24, hour12, minutes, seconds, am_pm)
   select time_key, time_value, hour24, hour12, minutes, seconds, am_pm from  (
WITH RECURSIVE time_cte AS (
    SELECT 0 AS seconds_of_day
    UNION ALL
    SELECT seconds_of_day + 1
    FROM time_cte
    WHERE seconds_of_day < 86399  -- 一天 24 小时有 86400 秒（0-86399）
)
SELECT 
    seconds_of_day AS time_key,                     -- time_key为秒数
    SEC_TO_TIME(seconds_of_day) AS time_value,      -- 使用SEC_TO_TIME函数将秒数转为时间
    HOUR(SEC_TO_TIME(seconds_of_day)) AS hour24,    -- 24小时制的小时数
    IF(HOUR(SEC_TO_TIME(seconds_of_day)) = 0 OR HOUR(SEC_TO_TIME(seconds_of_day)) = 12, 12, HOUR(SEC_TO_TIME(seconds_of_day)) % 12) AS hour12,  -- 12小时制的小时数
    MINUTE(SEC_TO_TIME(seconds_of_day)) AS minutes, -- 分钟
    SECOND(SEC_TO_TIME(seconds_of_day)) AS seconds, -- 秒
    IF(HOUR(SEC_TO_TIME(seconds_of_day)) < 12, 'AM', 'PM') AS am_pm  -- AM/PM
FROM time_cte ) time_cte;

END

SET SESSION cte_max_recursion_depth = 100000;

//如果从1开始 
WITH RECURSIVE time_cte AS (
    SELECT 1 AS seconds_of_day
    UNION ALL
    SELECT seconds_of_day + 1
    FROM time_cte
    WHERE seconds_of_day < 86400  -- 一天 24 小时有 86400 秒（0-86399）
)

ENGINE=MyISAM