mysql生成日期表

统计数据时，如果交易数据不是连续的，比如统计每天的销售额，某个日期对应节假日，销售额=0，这样输出的数据是不连续的。为了解决这个问题，我们可以提前生成一个日期表，没有数据的日期填充0即可。
具体步骤如下：

1、创建一个num表，用来存储数字0-9

create table num(i int);

2、在num表生成0-9

insert into num(i) values(0), (1), (2), (3), (4), (5), (6), (7), (8), (9);

3、生成一个存储日期的表，datelist是字段名

create table if not exists calendar(datelist date);

4、插入日期数据

INSERT INTO calendar
    (datelist
    )
SELECT
    adddate((date_format('2015-01-01', '%Y-%m-%d')), numlist.id) AS DATE,
FROM
    (
        SELECT
            n1.i + n10.i * 10 + n100.i * 100 + n1000.i * 1000 + n10000.i * 10000 AS id
        FROM
            num n1
        CROSS JOIN num AS n10
        CROSS JOIN num AS n100
        CROSS JOIN num AS n1000
        CROSS JOIN num AS n10000
    ) AS numlist;

5、最后，添加主键

ALTER TABLE `calendar`
ADD COLUMN `id`  int UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键' FIRST ,
ADD PRIMARY KEY (`id`);

到这里，日期表已经建好了，统计数据的时候关联该表即可。

附：tidb创建日期表语句示例

-- 创建一个num表，用于存储0-9
drop table if EXISTS test.temp_num;
CREATE TABLE test.temp_num (
`i`  int(10) NOT NULL 
)
;
INSERT INTO test.temp_num (i) VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9);
select * from test.temp_num;

-- 创建日期表
drop table if EXISTS test.temp_calendar;
CREATE TABLE test.temp_calendar (
`id`  int(10) NOT NULL PRIMARY KEY AUTO_INCREMENT,
`datelist`  date NULL DEFAULT NULL 
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8mb4 COLLATE=utf8mb4_bin
ROW_FORMAT=COMPACT 
;

-- 插入日期数据
INSERT INTO test.temp_calendar
    (datelist
    )
SELECT
    adddate((date_format('2015-01-01', '%Y-%m-%d')), numlist.id1) AS DATE
FROM
    (
        SELECT
            n1.i + n10.i * 10 + n100.i * 100 + n1000.i * 1000 + n10000.i * 10000 AS id1
        FROM
            test.temp_num n1
        CROSS JOIN test.temp_num AS n10
        CROSS JOIN test.temp_num AS n100
        CROSS JOIN test.temp_num AS n1000
        CROSS JOIN test.temp_num AS n10000
        order by id1 asc 
    ) AS numlist;
select * from test.temp_calendar order by datelist desc limit 10;