日付があるテーブルを一つ準備して置くと有用に使える。
USE analysis;
CREATE TABLE tComDay(
Idx int primary key NOT NULL,
ComDay datetime NOT NULL
);
ALTER TABLE tComDay ADD INDEX ix_tComDay_ComDay (ComDay);
truncate table tComDay;
drop procedure if exists pInsertComday;
delimiter $$
CREATE PROCEDURE pInsertComday()
BEGIN
DECLARE sn int;
SET sn =1;
WHILE sn <= 36525 DO
INSERT INTO tComDay (Idx, ComDay)
SELECT sn, DATE_ADD('1999-12-31', INTERVAL + sn DAY);
SET sn = sn + 1;
END WHILE;
END $$
delimiter ;
call pInsertComday;
## SP実行時間：約40秒
select * from tComDay limit 10;
drop procedure if exists pInsertComday;