MySQL Tips

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;