MySQL快速生成时间维度表-CSDN博客

MySQL快速生成时间维度表：

MySQL里面生成一张时间维度表，用于ETL工具使用。

原文地址：http://blog.csdn.net/neweastsun/article/details/43866599

SET @d0 = "2015-01-01";
SET @d1 = "2019-12-31";

SET @date = date_sub(@d0, interval 1 day);

use test;

CREATE TABLE `time_dimension` (
`date` date DEFAULT NULL,
`id` int(11) NOT NULL,
`y` smallint(6) DEFAULT NULL,
`m` smallint(6) DEFAULT NULL,
`d` smallint(6) DEFAULT NULL,
`yw` smallint(6) DEFAULT NULL,
`w` smallint(6) DEFAULT NULL,
`q` smallint(6) DEFAULT NULL,
`wd` smallint(6) DEFAULT NULL,
`m_name` char(10) DEFAULT NULL,
`wd_name` char(10) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

INSERT INTO time_dimension
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,
DATE_FORMAT(@date, "%x") AS yw,
WEEK(@date, 3) AS w,
QUARTER(@date) AS q,
WEEKDAY(@date) + 1 AS wd,
MONTHNAME(@date) AS m_name,
DAYNAME(@date) AS wd_name
FROM
T
WHERE DATE_ADD(@date, INTERVAL 1 DAY) <= @d1
ORDER BY DATE ;

神秘的表T，仅仅需要有多于你需要生成日期的记录数即可。思路是从T表选择多行数据，同时生成对应的日期字段。