[MySQL] 创建间隔5分钟的时间表



间隔5分钟时间表，为了与其他表做联合查询时解决空缺时间点问题

方法一：创建存储过程，一次性插入N年的数据，每笔数据间隔5分钟

创建表

create table tb_date (date_day datetime());

创建存储过程

delimiter $

create procedure p1

begin 
declare i int;
declare j int;
declare time1 datetime;
set i = 3000;
set j = 1;
set time1 = now();
while i > 0
do
#insert into tb_minute_copy select now() - INTERVAL i*5 MINUTE;
insert into tb_minute_copy select time1 - INTERVAL i*5 MINUTE;

set i = i - 1;
end while;
while j < 5000000
do
insert into tb_minute_copy select time1 + INTERVAL j*5 MINUTE;
set j = j + 1;
end while;
end $




执行存储过程，写入数据

call p1();

方法二：用定时器，设定5分钟整数倍分钟时执行，每5分钟执行一次向表中插入当前时间

定时器执行语句

insert into tb_minute select now()

创建日历

创建日历表

CREATE TABLE `dim_date` (  
`date_id` int(11) NOT NULL COMMENT '20110512',  
`date_name` varchar(16) DEFAULT NULL COMMENT '2011-05-12',  
`date_of_month` int(11) DEFAULT NULL COMMENT '12',  
`year_id` int(11) DEFAULT NULL COMMENT '2011',  
`year_name` varchar(16) DEFAULT NULL COMMENT '2011年',  
`quarter_id` int(11) DEFAULT NULL COMMENT '2',  
`quarter_name` varchar(16) DEFAULT NULL COMMENT '2季度',  
`month_id` int(11) DEFAULT NULL COMMENT '5',  
`month_name` varchar(16) DEFAULT NULL COMMENT '5月',  
`month_of_year_name` varchar(16) DEFAULT NULL COMMENT '2011年5月',  
`month_of_year_id` int(11) DEFAULT NULL COMMENT '201105',  
`week_id` int(11) DEFAULT NULL,  
`week_name` varchar(16) DEFAULT NULL,  
`week_of_year_id` int(11) DEFAULT NULL,  
`week_of_year_name` varchar(32) DEFAULT NULL,  
`is_weekend` enum('否','是') DEFAULT NULL COMMENT '是否周末',  
PRIMARY KEY (`date_id`),  
KEY `ix_dim_date_date_name` (`date_name`),  
KEY `ix_dim_date_month_id` (`month_id`),  
KEY `ix_dim_date_year_id` (`year_id`),  
KEY `ix_dim_date_quanter_id` (`quarter_id`),  
KEY `ix_dim_date_week_of_year_id` (`week_of_year_id`,`week_of_year_name`)  
) ENGINE=MyISAM DEFAULT CHARSET=latin1  


创建存储过程
DROP PROCEDURE IF EXISTS pro_dim_date;  
CREATE PROCEDURE pro_dim_date(IN bdate DATE,IN edate DATE)  
BEGIN  
DECLARE var DATE DEFAULT bdate;  
DECLARE evar DATE DEFAULT DATE_ADD(edate,INTERVAL 1 DAY);  
DECLARE bweek DATE;  
DECLARE eweek DATE;  
WHILE var<evar DO  
SET bweek = DATE_ADD(DATE_SUB(var,INTERVAL 1 WEEK),INTERVAL 1 DAY);  
SET eweek = DATE_SUB(DATE_ADD(var,INTERVAL 1 WEEK),INTERVAL 1 DAY);  
INSERT INTO gyyx_report.dim_date   
(  
`date_id`,  
`date_name`,  
`date_of_month`,  
`year_id`,  
`year_name`,  
`quarter_id`,  
`quarter_name`,  
`month_id`,  
`month_name`,  
`month_of_year_name`,  
`month_of_year_id`,  
`week_id`,  
`week_name`,  
`week_of_year_id`,  
`week_of_year_name`,  
`is_weekend`  
)  
VALUES  
(  
DATE_FORMAT(var,'%Y%m%d'),  
DATE_FORMAT(var,'%Y-%m-%d'),  
DAYOFMONTH(var),  
YEAR(var),  
CONCAT(YEAR(var),'年'),  
QUARTER(var),  
CONCAT(QUARTER(var),'季度'),  
DATE_FORMAT(var,'%Y%m'),  
CONCAT(YEAR(var),'年',MONTH(var),'月'),  
CONCAT(MONTH(var),'月'),  
MONTH(var),  
WEEKDAY(var),  
CASE WEEKDAY(var) WHEN 0 THEN '星期一' WHEN 1 THEN '星期二' WHEN 2 THEN '星期三' WHEN 3 THEN '星期四' WHEN 4 THEN '星期五' WHEN 5 THEN '星期六' WHEN 6 THEN '星期日' END, 
WEEKOFYEAR(var),  
CONCAT('第',WEEKOFYEAR(var),'周(',MONTH(bweek),'月',DAY(bweek),'日~',MONTH(eweek),'月',DAY(eweek),'日'), 
CASE WHEN WEEKDAY(var)>4 THEN '是' ELSE '否' END   
);  
SET var=DATE_ADD(var,INTERVAL 1 DAY);  
END WHILE;  
END  

调用存储过程，填入日历表

call p7(NOW()-INTERVAL 31 DAY, NOW() + INTERVAL 10000 DAY)