如何插入365行日期逐天递增数据，及是否可通过视图替代批量插入

问题1：最优实现方案 #

你当前用Java生成365行批量插入的保底方案可以跑通，但不是最优方案。更高效的实现是直接用数据库原生的日期序列生成能力 + UPSERT 语法完成全流程，不需要应用层拼接SQL，性能更高也不会出现SQL超长的问题，同时天然满足主键冲突覆盖的需求。

不同数据库的实现示例如下：

MySQL 实现 #

INSERT INTO my_table (`date`, from_city, to_city, rate_per_mile)
WITH RECURSIVE date_sequence AS (
    SELECT CURDATE() AS dt
    UNION ALL
    SELECT dt + INTERVAL 1 DAY FROM date_sequence WHERE dt < CURDATE() + INTERVAL 364 DAY
)
SELECT dt, 'city1', 'city2', 13.23 FROM date_sequence
ON DUPLICATE KEY UPDATE rate_per_mile = VALUES(rate_per_mile);

PostgreSQL 实现 #

INSERT INTO my_table (date, from_city, to_city, rate_per_mile)
SELECT dt, 'city1', 'city2', 13.23
FROM GENERATE_SERIES(
    CURRENT_DATE,
    CURRENT_DATE + INTERVAL '364 days',
    INTERVAL '1 day'
) AS dt
ON CONFLICT (date, from_city, to_city) DO UPDATE 
SET rate_per_mile = EXCLUDED.rate_per_mile;

问题2：视图方案是否可行 #

完全可以用视图实现，不需要提前批量插入数据，前提是你需要先维护一张城市对最新费率基础表，只存储每个城市对的有效费率，不需要按天冗余存储：

-- 城市对费率基础表结构参考
CREATE TABLE city_pair_rate (
    from_city VARCHAR(50),
    to_city VARCHAR(50),
    rate_per_mile DECIMAL(10,2),
    PRIMARY KEY (from_city, to_city)
);

之后创建视图，自动拼接未来365天的日期和所有城市对的费率，得到你需要的映射结果：

-- MySQL 视图示例
CREATE VIEW city_pair_rate_365 AS
WITH RECURSIVE date_sequence AS (
    SELECT CURDATE() AS dt
    UNION ALL
    SELECT dt + INTERVAL 1 DAY FROM date_sequence WHERE dt < CURDATE() + INTERVAL 364 DAY
)
SELECT 
    d.dt AS `date`,
    r.from_city,
    r.to_city,
    r.rate_per_mile
FROM date_sequence d
CROSS JOIN city_pair_rate r;

方案优缺点 #

• 优点：完全不需要做批量插入操作，只要维护基础表的费率即可，基础表更新后视图数据自动同步，无冗余存储成本
• 缺点：如果城市对数量大，查询视图时的实时计算成本会高于预生成的实表，适合城市对数量不多、查询QPS不高的场景；如果需要支持历史费率查询，只需在基础表增加生效/失效时间字段，对应调整视图逻辑即可。

内容的提问来源于stack exchange，提问作者daltonfury42