mysql - 如何在 MariaDB 中生成日期范围？

Question

我想插入 2019-01-01 到 2019-12-31 整个期间的日期和星期几。但我不能。

我尝试 date_format 函数。

这是我的 table 。表名是food_user，我想在re_date中插入日期和星期，re_day 2019-01-01~2019-12-31

INSERT INTO food_user (NAME,re_date,re_day) VALUES ('Alex' , ? , ?);

Answer 1

如果您使用 MariaDB，请尝试使用此代码来获取年份中的所有日期:

SELECT '2019-01-01' + INTERVAL seq DAY FROM seq_0_to_364;

or 

SELECT '2018-12-31' + INTERVAL seq DAY FROM seq_1_to_365;

为了理解这一点，MariaDB 有一个内置的 SEQUENCE 引擎 - 引用:https://mariadb.com/kb/en/library/sequence-storage-engine/ .

基本上，输入SELECT seq FROM seq_1_to_10;将返回从1到10的数字seq。在这种情况下，全年是365天，因此seq_1_to_365将返回从1到365的数字。如果您从零 (0) 开始计数，那么您需要将最后一个值视为 364，因此将序列更改为 seq_0_to_364，如上面的示例所示。根据您的查询，您可以执行以下操作:

INSERT INTO food_user (NAME,re_date,re_day)
SELECT 'Alex','2019-01-01' + INTERVAL seq DAY,DAYNAME('2019-01-01' + INTERVAL seq DAY) FROM seq_0_to_364;

这是一个更新，几个月前我发现了一种使用递归语句生成日期范围的方法。 序列引擎非常有用，但它仅特定于 MariaDB。 MySQL 和 MariaDB 都支持 WITH RECURSIVE，尽管它也是特定于版本的； MySQL from version 8 & MariaDB from version 10.2.2 。这是查询:

WITH RECURSIVE date_ranges AS (
SELECT '2019-01-01' dt UNION ALL
SELECT dt + INTERVAL 1 DAY FROM date_ranges 
WHERE dt + INTERVAL 1 DAY <= '2019-12-31')

SELECT dt FROM date_ranges;

除了上述原因之外，我添加此选项还有一个简单但重要的原因；这是因为闰年。我们在 2020 年有过一次，所以那一年的总天数实际上是 366 天，而不是 365 天。

Here is a fiddle showing the difference using sequence engine and with recursive.

正如您在 fiddle 中看到的，序列引擎查询(闰年)仍然返回 365 行，并且该年的最后一个日期是 2020-12-30 ..我们缺少一天。这是由我们定义的编号序列约束 (seq_1_to_365) 引起的。因此，除非您只在非闰年使用序列引擎，否则它可能不是使用序列引擎的最佳选择。

这里是根据问题进行INSERT操作的完整查询:

INSERT INTO food_user(NAME,re_date,re_day)
/*recursive statement here*/
  WITH RECURSIVE date_ranges AS (
   SELECT '2019-01-01' dt UNION ALL
   SELECT dt + INTERVAL 1 DAY FROM date_ranges 
   WHERE dt + INTERVAL 1 DAY <= '2019-12-31')
/*select statement here*/
SELECT 'Alex',dt, dayname(dt) FROM date_ranges;

Demo fiddle