MySQL快速生成时间维度表

MySQL快速生成时间维度表：

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

原文地址：http://blog.youkuaiyun.com/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表选择多行数据，同时生成对应的日期字段。

本文转自 lirulei90 51CTO博客，原文链接：http://blog.51cto.com/lee90/2053143，如需转载请自行联系原作者