MySQL创建生成日期列表的临时表方法及现有代码错误修正咨询

原代码错误点 #

• 使用了中文全角单引号，需替换为英文半角单引号
• CREATE TEMPORARY TABLE 语句末尾缺少分号结束标记
• MySQL 没有 DATEADD(DAY, 1, xxx) 语法，正确的日期累加函数写法为 DATE_ADD(@start_date, INTERVAL 1 DAY)
• 直接在SQL控制台运行WHILE循环需要先修改语句分隔符，避免循环内的分号被提前识别为语句结束
• WHILE循环语法错误，缺少DO关键字启动循环体，也没有对应的END WHILE结束标记
• 变量赋值写法不规范，MySQL会话变量赋值需使用SET @变量 = 值或SELECT @变量 := 值格式
• INSERT插入、变量赋值语句末尾均缺少分号结束标记

修正后可用代码 #

方案1：兼容MySQL 5.x/8.0的循环实现 #

-- 临时修改语句分隔符为//，避免循环内的分号提前截断语句
DELIMITER //

SET @start_date = '2014-07-29';
SET @end_date = '2016-07-29';

-- 创建临时表
CREATE TEMPORARY TABLE Dates (
  d DATE,
  PRIMARY KEY (d)
);

-- 循环插入日期
WHILE (@start_date < @end_date) DO
  INSERT INTO Dates (d) VALUES (@start_date);
  SET @start_date = DATE_ADD(@start_date, INTERVAL 1 DAY);
END WHILE //

-- 改回默认分号作为分隔符
DELIMITER ;

-- 验证生成的日期列表
SELECT * FROM Dates;

方案2：MySQL 8.0+ 更简洁的递归CTE实现 #

无需修改分隔符，代码更精简：

SET @start_date = '2014-07-29';
SET @end_date = '2016-07-29';

-- 直接用递归CTE生成日期列表并写入临时表
CREATE TEMPORARY TABLE Dates AS
WITH RECURSIVE date_list AS (
    SELECT @start_date AS d
    UNION ALL
    SELECT DATE_ADD(d, INTERVAL 1 DAY) 
    FROM date_list 
    WHERE d < DATE_SUB(@end_date, INTERVAL 1 DAY)
)
SELECT * FROM date_list;

-- 追加主键约束
ALTER TABLE Dates ADD PRIMARY KEY (d);

-- 验证生成的日期列表
SELECT * FROM Dates;

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