mysql生成一列日期 |mysql中自增怎么写

SELECT DATE_ADD('2022-01-01', INTERVAL seq.seq DAY) AS date
FROM (
SELECT 0 AS seq UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL
SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL
SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL
SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL
SELECT 12 UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL
SELECT 15 UNION ALL SELECT 16 UNION ALL SELECT 17 UNION ALL
SELECT 18 UNION ALL SELECT 19 UNION ALL SELECT 20 UNION ALL
SELECT 21 UNION ALL SELECT 22 UNION ALL SELECT 23 UNION ALL
SELECT 24 UNION ALL SELECT 25 UNION ALL SELECT 26 UNION ALL
SELECT 27 UNION ALL SELECT 28 UNION ALL SELECT 29 UNION ALL
SELECT 30 UNION ALL SELECT 31
) AS seq
WHERE seq.seq<= DATEDIFF('2022-01-31', '2022-01-01');

这段代码基于 MySQL 中的 DATE_ADD 函数和 DATEDIFF 函数。首先，大家将指定的日期（在本例中是 ‘2022-01-01’）加上一个指定的天数（在本例中是 seq.seq）。生成序列的代码使用了一个常用的技巧：使用 UNION ALL 运算符将多个查询结果合并成一个查询结果。

在这个例子中，大家使用了一个名为 seq 的虚拟表。seq 表有一个名为 seq 的列，这个列包含了一个从 0 到 31 的连续数字序列。大家通过 WHERE 子句，限制了只返回日期序列中介于指定日期范围内的日期值。

这个日期序列可以用来填充表中的日期列，例如：

CREATE TABLE my_table (
id INT PRIMARY KEY,
date_val DATE
);
INSERT INTO my_table (id, date_val)
SELECT id, date
FROM (
SELECT 1 AS id, DATE_ADD('2022-01-01', INTERVAL seq.seq DAY) AS date
FROM (
SELECT 0 AS seq UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL
SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL
SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL
SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL
SELECT 12 UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL
SELECT 15 UNION ALL SELECT 16 UNION ALL SELECT 17 UNION ALL
SELECT 18 UNION ALL SELECT 19 UNION ALL SELECT 20 UNION ALL
SELECT 21 UNION ALL SELECT 22 UNION ALL SELECT 23 UNION ALL
SELECT 24 UNION ALL SELECT 25 UNION ALL SELECT 26 UNION ALL
SELECT 27 UNION ALL SELECT 28 UNION ALL SELECT 29 UNION ALL
SELECT 30 UNION ALL SELECT 31
) AS seq
WHERE seq.seq<= DATEDIFF('2022-01-31', '2022-01-01')
) AS dates;

这段代码将日期序列值插入到 my_table 表的 date_val 列中。