MySql如何从间隔生成完整的日历？

i'm new to mysql procedures. I ask your help for generate an calendar with some kind of procedure (generate a row for every day in an interval)... on * i found this function:

我是mysql程序的新手。我请求你帮助生成一个带有某种程序的日历(在一个间隔中每天生成一行)...在*上我发现这个函数:

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');

But gives me an error near INSERT INTO mytable (mydate) VALUES (dateStart); I use MySql 5.5.35-33 and i will run the query on PhpMyAdmin. I tried the query alone but it work as it should. An php solution it's ok too... also some kind of generator of .sql file to import etc thanks :D

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

The result must be something like that table:

结果必须类似于该表:

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

If you know how to generate this kind of table i will be very grateful.

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

Others mysql infos

Others 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

1 个解决方案

#1

Assuming your table has got at least this CREATE TABLE statement:

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

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

Then you could insert rows for every day of the year with following statement:

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

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

Explanation

The subselect creates a temporary table with a row for every day between start ('2014-01-01') and end ('2014-12-31'). So MySQL has only to do one single INSERT and this is much faster than a row by row insert.

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

If you only want the dates, omit the columns ID and Price that I took from your example:

如果您只想要日期,请省略我从您的示例中获取的列ID和价格:

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');

#1