php - MySql如何从一个时间间隔生成一个完整的日历？

Question

我是 mysql 程序的新手。我请你帮忙用某种程序生成一个日历(在一个时间间隔内为每一天生成一行)...在 stackoverflow 上我发现了这个函数:

CREATE PROCEDURE filldates(dateStart DATE, dateEnd DATE)
    BEGIN

          WHILE dateStart <= dateEnd DO
            INSERT INTO mytable (mydate) VALUES (dateStart);
            SET dateStart = date_add(dateStart, INTERVAL 1 DAY);
          END WHILE;
        END;


    CALL filldates('2014-01-01','2014-12-31');

但是在 INSERT INTO mytable (mydate) VALUES (dateStart); 附近给我一个错误 我使用 MySql 5.5.35-33 我将在 PhpMyAdmin 上运行查询.我单独尝试了查询，但它可以正常工作。一个 php 解决方案也可以...还有某种要导入的 .sql 文件生成器等，谢谢 :D

结果必须类似于该表:

  ID | Date       | Price | ..
______________________________
   0 | 2014-01-01 |   40  | ..
   1 | 2014-01-02 |   40  | ..
   2 | 2014-01-03 |   40  | ..
 ... |    ...     |  ...  |
 365 | 2014-12-31 |   40  | ..

如果您知道如何生成这种表格，我将非常感激。

其他mysql信息

innodb_version : 5.5.35-rel33.0
protocol_version : 10
slave_type_conversions 
version : 5.5.35-33.0-log
version_comment: Percona Server (GPL), Release rel33.0, Revision 61...
version_compile_machine : x86_64
version_compile_os : Linux

Answer 1

假设您的表至少有这个 CREATE TABLE 语句:

CREATE mytable(
  ID INT,
  date DATE,
  Price INT
);

然后您可以使用以下语句为一年中的每一天插入行:

INSERT INTO mytable (ID, date, Price)
SELECT
    t.n, 
    DATE_ADD('2014-01-01', INTERVAL t.n DAY),
    40
FROM (
    SELECT 
        a.N + b.N * 10 + c.N * 100 AS n
    FROM
        (SELECT 0 AS N 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) a
       ,(SELECT 0 AS N 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) b
       ,(SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4) c
    ORDER BY n
) t   
WHERE
    t.n <= TIMESTAMPDIFF(DAY, '2014-01-01', '2014-12-31');

Demo

解释

subselect 创建一个临时表，在开始 ('2014-01-01') 和结束 ('2014-12-31') 之间的每一天都有一行。所以 MySQL 只需执行一次 INSERT，这比逐行插入要快得多。

如果您只需要日期，请省略我从您的示例中获取的列 ID 和 Price:

INSERT INTO mytable (date)
SELECT
    DATE_ADD('2014-01-01', INTERVAL t.n DAY)
FROM (
    SELECT 
        a.N + b.N * 10 + c.N * 100 AS n
    FROM
        (SELECT 0 AS N 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) a
       ,(SELECT 0 AS N 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) b
       ,(SELECT 0 AS N 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) c
    ORDER BY n
) t   
WHERE
    t.n <= TIMESTAMPDIFF(DAY, '2014-01-01', '2014-12-31');