插入具有日期间隔的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;

-that将生成N行(此处为N=10)。若要插入行，只需使用INSERT .. SELECT语法。小提琴是这里。在样例中，start_date也被设置为CURDATE() --当然，您可以在查询中轻松地调整它。