Laravel按日期查询services表：优先用JSON的date，无则用created_at

针对你的场景，不用批量更新113k条记录，用虚拟列+索引的方案就能完美解决，既满足查询逻辑，又能保证性能。

核心查询逻辑（直接查询版） #

如果只是临时查询，用COALESCE函数结合JSON字段提取即可，不需要修改表结构：

SELECT id, number, meta,
       -- 优先取meta中的date，转成datetime类型；不存在则用created_at
       COALESCE(STR_TO_DATE(meta->>'$.date', '%Y-%m-%d %H:%i'), created_at) AS effective_date
FROM services
-- 用同样的逻辑做日期条件筛选
WHERE COALESCE(STR_TO_DATE(meta->>'$.date', '%Y-%m-%d %H:%i'), created_at) 
      BETWEEN '2024-01-01 00:00:00' AND '2024-12-31 23:59:59';

关键细节： #

• meta->>'$.date'：从JSON列meta中提取date字段的纯字符串（自动去掉JSON的引号）
• STR_TO_DATE(...)：把提取到的字符串转成和created_at一致的DATETIME类型，避免类型不匹配导致的筛选错误
• COALESCE(a, b)：如果a不为空就用a，否则用b，完美实现"优先用meta.date，无则用created_at"的逻辑

性能优化版（虚拟列+索引） #

如果这个查询是高频操作，直接用上面的语句会因为函数调用导致全表扫描（无法使用索引），此时可以给表加一个存储型虚拟列，并给它建索引：

1. 添加虚拟列 #

ALTER TABLE services
ADD COLUMN effective_date DATETIME 
GENERATED ALWAYS AS (
    COALESCE(STR_TO_DATE(meta->>'$.date', '%Y-%m-%d %H:%i'), created_at)
) STORED;

• STORED类型的虚拟列会把计算结果存在表中，插入/更新记录时自动同步，查询时直接读取，性能和普通字段一致
• 不需要手动批量更新历史数据，虚拟列会自动计算所有现有记录的值

2. 给虚拟列建索引 #

CREATE INDEX idx_services_effective_date ON services(effective_date);

3. 优化后的查询 #

之后查询直接用虚拟列，走索引，速度大幅提升：

SELECT id, number, meta, effective_date
FROM services
WHERE effective_date BETWEEN '2024-01-01 00:00:00' AND '2024-12-31 23:59:59';

对比批量更新方案 #

批量更新113k条记录不仅会锁表影响业务，后续新插入的Reservation渠道记录还要手动维护meta.date字段，而虚拟列方案是一劳永逸的，自动适配所有新旧记录，完全不需要额外维护。

适配PostgreSQL的情况 #

如果你的数据库是PostgreSQL，语法稍有不同：

直接查询 #

SELECT id, number, meta,
       COALESCE((meta->>'date')::TIMESTAMP, created_at) AS effective_date
FROM services
WHERE COALESCE((meta->>'date')::TIMESTAMP, created_at) 
      BETWEEN '2024-01-01' AND '2024-12-31';

添加虚拟列并建索引 #

ALTER TABLE services
ADD COLUMN effective_date TIMESTAMP
GENERATED ALWAYS AS (
    COALESCE((meta->>'date')::TIMESTAMP, created_at)
) STORED;

CREATE INDEX idx_services_effective_date ON services(effective_date);

内容的提问来源于stack exchange，提问作者Emad Rashad Muhammed