mysql 日期辅助表
时间：2021-07-01 10:21:17
帮助过：21人阅读
、创建一个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、生成一个存储日期的表，datalist是字段名
CREATE TABLE if not exists calendar(datelist date);
#4、生成并插入日期数据
INSERT INTO calendar(datelist)
SELECT
adddate(
( -- 这里的起始日期，你可以换成当前日期
DATE_FORMAT("
2016-1-1",
‘%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`);
mysql 日期辅助表
标签：calendar sts value tab for comment oss null fir
