DROP PROCEDURE IF EXISTS FillDateTable;
delimiter //
CREATE PROCEDURE FillDateTable()
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
drop table if exists datetable;
create table datetable (thedate datetime primary key, isweekday smallint);
SET @x := date('2000-01-01');
REPEAT
insert into datetable (thedate, isweekday) SELECT @x, case when dayofweek(@x) in (1,7) then 0 else 1 end;
SET @x := date_add(@x, interval 1 day);
UNTIL @x >= '2030-12-31' END REPEAT;
END//
delimiter ;
CALL FillDateTable;
-- 可用于查询缺失的日期数据
