MySQL快速生成日期维度表

网上的例子大多都要用到存储过程，感觉有点麻烦，找到一个例子，参考了一下。

创建日期维度表

-- DROP TABLE dim_date;
CREATE TABLE `dim_date` (
  `id` int(8) NOT NULL COMMENT '主键(日期)',
  `date` date DEFAULT NULL COMMENT '日期',
  `date_text` varchar(20) DEFAULT NULL COMMENT '日期(文字)',
  `year` int(4) DEFAULT NULL COMMENT '年',
  `year_text` varchar(20) DEFAULT NULL COMMENT '年(文字)',
  `quarter` int(1) DEFAULT NULL COMMENT '季度',
  `quarter_text` varchar(20) DEFAULT NULL COMMENT '季度(文字)',
  `month` int(2) DEFAULT NULL COMMENT '月',
  `month_text` varchar(20) DEFAULT NULL COMMENT '月(文字)',
  `week` int(2) DEFAULT NULL COMMENT '周',
  `week_text` varchar(20) DEFAULT NULL COMMENT '周(文字)',
  `year_day` int(3) DEFAULT NULL COMMENT '年的某一日',
  `month_day` int(2) DEFAULT NULL COMMENT '月的某一日',
  `week_day` int(1) DEFAULT NULL COMMENT '周的某一日',
  `week_day_text` varchar(20) DEFAULT NULL COMMENT '周的某一日(文字)',
  `year_week` int(6) DEFAULT NULL COMMENT '某年的某一周',
  `year_week_text` varchar(20) DEFAULT NULL COMMENT '某年的某一周(文字)',
  `year_month` int(6) DEFAULT NULL COMMENT '某年的某一月',
  `year_month_text` varchar(20) DEFAULT NULL COMMENT '某年的某一月(文字)',
  `year_quarter` int(5) DEFAULT NULL COMMENT '某年的某一季度',
  `year_quarter_text` varchar(20) DEFAULT NULL COMMENT '某年的某一季度(文字)',
  `sync_time` datetime NOT NULL COMMENT '同步时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

插入数据

反复多执行几次就好了，主要看表mysql.help_topic的数据量(我的有701行)，2023-01-01到2050-12-31，重复执行16次就好了

原博主建议随便找张大表就行

-- 开始日期
SET @date_begin = '2023-01-01';
-- 结束日期
SET @date_end = '2050-12-31';
-- 临时遍历日期
SET @DATE := DATE_SUB( @date_begin, INTERVAL 1 DAY );
-- 获得已插入的最后日期
SELECT MAX( `date` ), (CASE WHEN MAX( `date` ) IS NOT NULL THEN @DATE := MAX( `date` ) END) FROM dim_date;
-- 查看最大日期
SELECT @DATE FROM DUAL;
-- 插入数据
INSERT INTO `dim_date` (`date`, `id`, `date_text`, `year`, `year_text`, `quarter`, `quarter_text`, `month`, `month_text`, `week`, `week_text`, `year_day`, `month_day`, `week_day`, `week_day_text`, `year_week`, `year_week_text`, `year_month`, `year_month_text`, `year_quarter`, `year_quarter_text`, `sync_time`) 
SELECT
		@DATE := DATE_ADD( @DATE, INTERVAL 1 DAY ) AS `date`,
		DATE_FORMAT( @DATE, '%Y%m%d' ) AS id,
		DATE_FORMAT( @DATE, '%Y年%m月%d日' ) AS date_text,
		YEAR ( @DATE ) AS `year`,
		DATE_FORMAT( @DATE, '%Y年' ) AS year_text,
		QUARTER ( @DATE ) AS `quarter`,
		CONCAT( QUARTER ( @DATE ), '季度' ) AS quarter_text,
		MONTH ( @DATE ) AS `month`,
		DATE_FORMAT( @DATE, '%c月' ) AS month_text,
		(WEEK ( @DATE, 5 ) - WEEK ( DATE_SUB( @DATE, INTERVAL DAYOFMONTH( @DATE ) - 1 DAY ), 5 ) + 1 ) AS `week`,
		CONCAT((WEEK ( @DATE, 5 ) - WEEK ( DATE_SUB( @DATE, INTERVAL DAYOFMONTH( @DATE ) - 1 DAY ), 5 ) + 1 ), '周' ) AS week_text,
		DAYOFYEAR( @DATE ) AS year_day,
		DAYOFMONTH( @DATE ) AS month_day,
		WEEKDAY( @DATE ) + 1 AS week_day,
	CASE
			WEEKDAY( @DATE ) + 1 
			WHEN 1 THEN
			'星期一' 
			WHEN 2 THEN
			'星期二' 
			WHEN 3 THEN
			'星期三' 
			WHEN 4 THEN
			'星期四' 
			WHEN 5 THEN
			'星期五' 
			WHEN 6 THEN
			'星期六' 
			WHEN 7 THEN
			'星期天' 
		END AS week_day_text,
		DATE_FORMAT( @DATE, '%x%v' ) AS year_week,
		DATE_FORMAT( @DATE, '%x年%v周' ) AS year_week_text,
		DATE_FORMAT( @DATE, '%Y%m' ) AS `year_month`,
		DATE_FORMAT( @DATE, '%Y年%m月' ) AS year_month_text,
		CONCAT(YEAR ( @DATE ), QUARTER ( @DATE )) AS year_quarter,
		CONCAT( DATE_FORMAT( @DATE, '%Y年' ), QUARTER ( @DATE ), '季度' ) AS year_quarter_text,
		NOW() sync_time 
	FROM
		mysql.help_topic 
	WHERE
		DATE_ADD( @DATE, INTERVAL 1 DAY ) <= @date_end 
ORDER BY
	DATE;

参考

• Mysql 快速生成日期时间维度表