我是 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

最佳答案

假设你的表至少有这个 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

说明

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

如果您只想要日期,请省略我从您的示例中获取的 IDPrice 列:
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');

关于php - MySql 如何从一个区间生成一个完整的日历?,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/25493942/

10-14 15:09
查看更多