DELIMITER $$
CREATE PROCEDURE dateDim(startDate DATE, endDate DATE)  
BEGIN  
    DECLARE currentDate DATE;  
    SET currentDate = startDate;  
    WHILE currentDate <= endDate DO  
        INSERT INTO sys_calendar (stats_day) VALUES (currentDate);  
        SET currentDate = DATE_ADD(currentDate, INTERVAL 1 DAY);  
    END WHILE;  
END $$
DELIMITER ;  
CALL dateDim('2022-01-01', '2030-12-31');

WITH RECURSIVE date_series AS (  
  SELECT '2023-01-01' AS date_value  
  UNION ALL  
  SELECT DATE_ADD(date_value, INTERVAL 1 DAY)  
  FROM date_series  
  WHERE date_value < '2023-12-31'  
)  
INSERT INTO date_dim (date_value)  
SELECT date_value FROM date_series;

CREATE TABLE `dim_date` (
  `id` int(11) NOT NULL COMMENT '日期鍵 20000101',
  `date` date DEFAULT NULL COMMENT '日期 2000-01-01',
  `year` smallint(6) DEFAULT NULL COMMENT '年 2000',
  `month` smallint(6) DEFAULT NULL COMMENT '月 01',
  `day` smallint(6) DEFAULT NULL COMMENT '日 01',
  `quarter` smallint(6) DEFAULT NULL COMMENT '當(dāng)年所屬季度',
  `year_of_week` smallint(6) DEFAULT NULL COMMENT '當(dāng)前周所屬年份',
  `week` smallint(6) DEFAULT NULL COMMENT '當(dāng)年所屬周',
  `day_of_week` smallint(6) DEFAULT NULL COMMENT '星期幾',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

SET @d0 = '2000-01-01';
SET @d1 = '2100-12-31';
SET @date = DATE_SUB( @d0, INTERVAL 1 DAY );
INSERT INTO `dim_date`(`date`, `id`,  `year`, `month`, `day`, `quarter`, `year_of_week`, `week`, `day_of_week`) 
SELECT
@date := DATE_ADD( @date, INTERVAL 1 DAY ) AS DATE,
DATE_FORMAT( @date, '%Y%m%d' ) AS id,
YEAR ( @date ) AS Y,
MONTH ( @date ) AS m,
DAY ( @date ) AS d,
QUARTER ( @date ) AS q,
DATE_FORMAT( @date, '%x' ) AS yw,
WEEK ( @date, 3 ) AS w,
WEEKDAY( @date ) + 1 AS wd
FROM
	[某張行數(shù)比日期差大的表]
WHERE
	DATE_ADD( @date, INTERVAL 1 DAY ) <= @d1 
ORDER BY
	DATE;