MySQL5.7 生成日期工具表

-- 创建数字辅助表（存储0-9）
CREATE TABLE tool_num ( i INT );
INSERT INTO tool_num ( i )
VALUES( 0 ),( 1 ),( 2 ),( 3 ),( 4 ),( 5 ),( 6 ),( 7 ),( 8 ),( 9 );-- -- 创建日期存储表 
CREATE TABLE
IFNOT EXISTS tool_calendar ( dates date );-- 生成日期数据
INSERT INTO tool_calendar ( dates ) SELECT
adddate( ( DATE_FORMAT( "2025-01-01", '%Y-%m-%d' ) ), numlist.id ) AS `date` 
FROM(SELECTn1.i + n10.i * 10 + n100.i * 100 + n1000.i * 1000+ n10000.i * 10000 AS id FROMtool_num n1CROSS JOIN tool_num AS n10CROSS JOIN tool_num AS n100CROSS JOIN tool_num AS n1000CROSS JOIN tool_num AS n10000 ) AS numlist;

实际执行效果如下：

实际执行效果如下：

1. 生成数字序列

   /* 通过笛卡尔积生成0-99999的数字序列 */
   n1(0-9) + n10(0-90) + n100(0-900) + n1000(0-9000) + n10000(0-90000)
   = 0到99999之间的所有整数

2. 生成日期序列

   /* 以2025-01-01为基准日期 */
   ADDDATE('2025-01-01', 0) => 2025-01-01
   ADDDATE('2025-01-01', 1) => 2025-01-02
   ...
   ADDDATE('2025-01-01', 99999) => 2298-11-29（约274年后）

3. 结果数据示例

   +------------+
   | dates      |
   +------------+
   | 2025-01-01 |
   | 2025-01-02 |
   | ...        |
   | 2298-11-29 | -- 共生成100,000条日期
   +------------+