mysql - 以日期间隔插入 N 行

Question

我需要将行插入到数据库中，其中每一行都相同，除了日期列，对于每个新行，它的日期应该增加 1 周。所以，基本上是这样的:

  for(n = 0; n<X; n++)
      insert into events (date, title) values (start_date + 7*n, 'static title');

有什么 MySQL 技巧可以用来做到这一点？

Answer 1

您可以使用:

SELECT
 'static_title' AS title,
 DATE_ADD(@start_date, INTERVAL @i:=@i+1 WEEK) AS result_date
FROM
 (SELECT
 (two_1.id + two_2.id + two_4.id + 
 two_8.id + two_16.id) AS id
 FROM
 (SELECT 0 AS id UNION ALL SELECT 1 AS id) AS two_1
 CROSS JOIN (SELECT 0 id UNION ALL SELECT 2 id) AS two_2
 CROSS JOIN (SELECT 0 id UNION ALL SELECT 4 id) AS two_4
 CROSS JOIN (SELECT 0 id UNION ALL SELECT 8 id) AS two_8
 CROSS JOIN (SELECT 0 id UNION ALL SELECT 16 id) AS two_16
 ) AS sequence
 CROSS JOIN
-- @i:=0 for not including current week
 (SELECT @i:=-1, @start_date:=CURDATE()) AS init
WHERE
  sequence.id<10;

- 这将产生 N 行(此处 N=10)。要插入行，只需使用 INSERT .. SELECT 语法。 fiddle 是 here .同样在示例中，start_date 设置为 CURDATE() - 当然，您可以在查询中轻松调整它。