Это хорошая идея, чтобы генерировать эти даты на лету. Тем не менее, я не чувствую себя комфортно для этого с довольно большим диапазоном, поэтому я получил следующее решение:
CREATE TABLE DatesNumbers (
i MEDIUMINT NOT NULL,
PRIMARY KEY (i)
)
COMMENT='Used by Dates view'
;
INSERT INTO DatesNumbers
SELECT
a.i + (10 * b.i) + (100 * c.i) + (1000 * d.i) + (10000 * e.i) - 59999 AS i
FROM
(SELECT 0 AS i 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) AS a
, (SELECT 0 AS i 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) AS b
, (SELECT 0 AS i 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) AS c
, (SELECT 0 AS i 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) AS d
, (SELECT 0 AS i 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) AS e
;
SELECT
i
, CURRENT_DATE() + INTERVAL i DAY AS Date
FROM
DatesNumbers
Вот и все.
WHERE i < 0
или WHERE i > 0
(PK)