MySQL数据库中快速生成日期维度表

创建日期维度表

 1-- DROP TABLE dim_date;
 2CREATE TABLE `dim_date` (
 3  `id` int(8) NOT NULL COMMENT '主键(日期)',
 4  `date` date DEFAULT NULL COMMENT '日期',
 5  `date_text` varchar(20) DEFAULT NULL COMMENT '日期(文字)',
 6  `year` int(4) DEFAULT NULL COMMENT '年',
 7  `year_text` varchar(20) DEFAULT NULL COMMENT '年(文字)',
 8  `quarter` int(1) DEFAULT NULL COMMENT '季度',
 9  `quarter_text` varchar(20) DEFAULT NULL COMMENT '季度(文字)',
10  `month` int(2) DEFAULT NULL COMMENT '月',
11  `month_text` varchar(20) DEFAULT NULL COMMENT '月(文字)',
12  `week` int(2) DEFAULT NULL COMMENT '周',
13  `week_text` varchar(20) DEFAULT NULL COMMENT '周(文字)',
14  `year_day` int(3) DEFAULT NULL COMMENT '年的某一日',
15  `month_day` int(2) DEFAULT NULL COMMENT '月的某一日',
16  `week_day` int(1) DEFAULT NULL COMMENT '周的某一日',
17  `week_day_text` varchar(20) DEFAULT NULL COMMENT '周的某一日(文字)',
18  `year_week` int(6) DEFAULT NULL COMMENT '某年的某一周',
19  `year_week_text` varchar(20) DEFAULT NULL COMMENT '某年的某一周(文字)',
20  `year_month` int(6) DEFAULT NULL COMMENT '某年的某一月',
21  `year_month_text` varchar(20) DEFAULT NULL COMMENT '某年的某一月(文字)',
22  `year_quarter` int(5) DEFAULT NULL COMMENT '某年的某一季度',
23  `year_quarter_text` varchar(20) DEFAULT NULL COMMENT '某年的某一季度(文字)',
24  `sync_time` datetime NOT NULL COMMENT '同步时间',
25  PRIMARY KEY (`id`)
26) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

插入数据

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

 1-- 开始日期
 2SET @date_begin = '2023-01-01';
 3-- 结束日期
 4SET @date_end = '2050-12-31';
 5-- 临时遍历日期
 6SET @DATE := DATE_SUB( @date_begin, INTERVAL 1 DAY );
 7-- 获得已插入的最后日期
 8SELECT MAX( `date` ), (CASE WHEN MAX( `date` ) IS NOT NULL THEN @DATE := MAX( `date` ) END) FROM dim_date;
 9-- 查看最大日期
10SELECT @DATE FROM DUAL;
11-- 插入数据
12INSERT 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`) 
13SELECT
14    @DATE := DATE_ADD( @DATE, INTERVAL 1 DAY ) AS `date`,
15    DATE_FORMAT( @DATE, '%Y%m%d' ) AS id,
16    DATE_FORMAT( @DATE, '%Y年%m月%d日' ) AS date_text,
17    YEAR ( @DATE ) AS `year`,
18    DATE_FORMAT( @DATE, '%Y年' ) AS year_text,
19    QUARTER ( @DATE ) AS `quarter`,
20    CONCAT( QUARTER ( @DATE ), '季度' ) AS quarter_text,
21    MONTH ( @DATE ) AS `month`,
22    DATE_FORMAT( @DATE, '%c月' ) AS month_text,
23    (WEEK ( @DATE, 5 ) - WEEK ( DATE_SUB( @DATE, INTERVAL DAYOFMONTH( @DATE ) - 1 DAY ), 5 ) + 1 ) AS `week`,
24    CONCAT((WEEK ( @DATE, 5 ) - WEEK ( DATE_SUB( @DATE, INTERVAL DAYOFMONTH( @DATE ) - 1 DAY ), 5 ) + 1 ), '周' ) AS week_text,
25    DAYOFYEAR( @DATE ) AS year_day,
26    DAYOFMONTH( @DATE ) AS month_day,
27    WEEKDAY( @DATE ) + 1 AS week_day,
28    CASE
29        WEEKDAY( @DATE ) + 1 
30        WHEN 1 THEN
31        '星期一' 
32        WHEN 2 THEN
33        '星期二' 
34        WHEN 3 THEN
35        '星期三' 
36        WHEN 4 THEN
37        '星期四' 
38        WHEN 5 THEN
39        '星期五' 
40        WHEN 6 THEN
41        '星期六' 
42        WHEN 7 THEN
43        '星期天' 
44    END AS week_day_text,
45    DATE_FORMAT( @DATE, '%x%v' ) AS year_week,
46    DATE_FORMAT( @DATE, '%x年%v周' ) AS year_week_text,
47    DATE_FORMAT( @DATE, '%Y%m' ) AS `year_month`,
48    DATE_FORMAT( @DATE, '%Y年%m月' ) AS year_month_text,
49    CONCAT(YEAR ( @DATE ), QUARTER ( @DATE )) AS year_quarter,
50    CONCAT( DATE_FORMAT( @DATE, '%Y年' ), QUARTER ( @DATE ), '季度' ) AS year_quarter_text,
51    NOW() sync_time 
52FROM mysql.help_topic 
53WHERE
54    DATE_ADD( @DATE, INTERVAL 1 DAY ) <= @date_end 
55ORDER BY DATE;