Mysql实战之快速填充序列维度表

Mysql实战案例五之快速填充序列维度表

1.需求

2.代码

3.执行效果

4.

create table nums(
a int UNSIGNED not null primary key
)engine = INNODB;

create procedure createNums(cnt int UNSIGNED)
begin
    declare s int UNSIGNED DEFAULT 1;
    truncate table nums;
    while s <= cnt 
    do
    begin
        insert into nums select s;
        set s=s+1;
    end;
    end while;
end;


call createNums(100)


drop PROCEDURE if EXISTS createNums;
create procedure createNums(cnt int UNSIGNED)
begin
    declare s int UNSIGNED DEFAULT 1;
    truncate table nums;
    insert into nums select s;
    while s *2 <= cnt 
    do
    begin
        insert into nums select a+s from nums;
        set s=s*2;
    end;
    end while;
end;
call createNums(200000)

select count(*) from nums; 

-- 选择一个从start -> end 的递增日期
create procedure createDimTime(start Date,end Date)
BEGIN
select DATE_ADD(start,INTERVAL a-1 day)
from nums where a<=DATEDIFF(end,start)+1;
end;

call createDimTime('2018-09-02','2018-09-10')