我是 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,这比逐行插入要快得多。
如果您只想要日期,请省略我从您的示例中获取的
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');
关于php - MySql 如何从一个区间生成一个完整的日历?,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/25493942/