MySQL TIME函数处理空值报错及UPDATE/SELECT差异问题咨询

问题场景 #

当parkings表的active_time（JSON类型）字段存在空字符串时段（例如"Su":""）时，执行用于校验停车时段有效性的UPDATE语句会触发错误：

[22001][1292] Data truncation: Truncated incorrect time value: ''

但执行对应的SELECT语句却能正常运行。该UPDATE的逻辑是：根据停车场的每日时段配置，判断mytable中记录的arrival_time和departure_time是否超出对应日期的有效时段，若超出则将is_valid设为FALSE、status设为'Canceled'。

正常的active_time格式示例：

{"Fr": "08:00-23:45", "Mo": "05:00-18:00", "Sa": "08:00-20:00", "Su": "08:00-20:00", "Th": "05:00-18:00", "Tu": "05:00-18:00", "We": "05:00-18:00"}

报错时的active_time格式示例：

{"Fr": "08:00-23:45", "Mo": "05:00-18:00", "Sa": "08:00-20:00", "Su": "", "Th": "05:00-18:00", "Tu": "05:00-18:00", "We": "05:00-18:00"}

报错原因：UPDATE与SELECT的严格模式差异 #

MySQL默认开启严格SQL模式，在该模式下：

• SELECT操作中，若尝试将空字符串转换为TIME类型，MySQL会返回NULL并仅触发警告（不会中断查询），因此能正常执行。
• UPDATE操作中，严格模式要求数据转换必须完全符合类型定义，不允许无效的截断或转换。当你的UPDATE语句尝试将空字符串时段拆分为开始/结束时间（比如用STR_TO_DATE转换）时，空字符串无法转为合法的TIME类型，直接触发截断错误，导致语句中断。

替代实现方式 #

1. 在UPDATE中添加空值过滤逻辑 #

在解析时段前先判断是否为空字符串，跳过无效转换，同时直接将空时段标记为无效：

UPDATE mytable m
JOIN parkings p ON m.parking_id = p.id
SET m.is_valid = FALSE, m.status = 'Canceled'
WHERE
  -- 空时段直接标记为无效
  NULLIF(JSON_UNQUOTE(JSON_EXTRACT(p.active_time, CONCAT('$.', LEFT(UPPER(DATE_FORMAT(m.arrival_time, '%a')), 2)))), '') IS NULL
  OR
  -- 有效时段判断时间是否超出范围
  (
    TIME(m.arrival_time) < STR_TO_DATE(
      SUBSTRING_INDEX(JSON_UNQUOTE(JSON_EXTRACT(p.active_time, CONCAT('$.', LEFT(UPPER(DATE_FORMAT(m.arrival_time, '%a')), 2)))), '-', 1),
      '%H:%i'
    )
    OR TIME(m.arrival_time) > STR_TO_DATE(
      SUBSTRING_INDEX(JSON_UNQUOTE(JSON_EXTRACT(p.active_time, CONCAT('$.', LEFT(UPPER(DATE_FORMAT(m.arrival_time, '%a')), 2)))), '-', -1),
      '%H:%i'
    )
    OR TIME(m.departure_time) < STR_TO_DATE(
      SUBSTRING_INDEX(JSON_UNQUOTE(JSON_EXTRACT(p.active_time, CONCAT('$.', LEFT(UPPER(DATE_FORMAT(m.departure_time, '%a')), 2)))), '-', 1),
      '%H:%i'
    )
    OR TIME(m.departure_time) > STR_TO_DATE(
      SUBSTRING_INDEX(JSON_UNQUOTE(JSON_EXTRACT(p.active_time, CONCAT('$.', LEFT(UPPER(DATE_FORMAT(m.departure_time, '%a')), 2)))), '-', -1),
      '%H:%i'
    )
  );

2. 使用JSON_VALUE简化解析并安全处理空值 #

用JSON_VALUE替代JSON_EXTRACT，结合NULLIF将空字符串转为NULL，避免无效转换：

UPDATE mytable m
JOIN parkings p ON m.parking_id = p.id
SET m.is_valid = FALSE, m.status = 'Canceled'
WHERE
  -- 获取到达日时段，空字符串转NULL
  (
    @arrival_day_slot := NULLIF(JSON_VALUE(p.active_time, CONCAT('$.', LEFT(UPPER(DATE_FORMAT(m.arrival_time, '%a')), 2))), '')
  ) IS NOT NULL
  AND (
    TIME(m.arrival_time) < STR_TO_DATE(SUBSTRING_INDEX(@arrival_day_slot, '-', 1), '%H:%i')
    OR TIME(m.arrival_time) > STR_TO_DATE(SUBSTRING_INDEX(@arrival_day_slot, '-', -1), '%H:%i')
  )
  OR
  -- 获取离开日时段，空字符串转NULL
  (
    @departure_day_slot := NULLIF(JSON_VALUE(p.active_time, CONCAT('$.', LEFT(UPPER(DATE_FORMAT(m.departure_time, '%a')), 2))), '')
  ) IS NOT NULL
  AND (
    TIME(m.departure_time) < STR_TO_DATE(SUBSTRING_INDEX(@departure_day_slot, '-', 1), '%H:%i')
    OR TIME(m.departure_time) > STR_TO_DATE(SUBSTRING_INDEX(@departure_day_slot, '-', -1), '%H:%i')
  )
  -- 空时段直接标记无效
  OR @arrival_day_slot IS NULL OR @departure_day_slot IS NULL;

3. 优化表结构，弃用JSON存储时段 #

将JSON格式的时段拆分为关联表，更便于数据校验和查询：

1. 创建关联表：

CREATE TABLE parking_active_times (
  parking_id INT NOT NULL,
  day_code VARCHAR(2) NOT NULL, -- 对应Mo/Tu/We/Th/Fr/Sa/Su
  start_time TIME,
  end_time TIME,
  PRIMARY KEY (parking_id, day_code),
  FOREIGN KEY (parking_id) REFERENCES parkings(id)
);

2. 将原有JSON中的时段数据迁移到该表（空字符串时段对应start_time和end_time设为NULL）。
3. 执行UPDATE语句：

UPDATE mytable m
JOIN parking_active_times pat 
  ON m.parking_id = pat.parking_id 
  AND pat.day_code = LEFT(UPPER(DATE_FORMAT(m.arrival_time, '%a')), 2)
SET m.is_valid = FALSE, m.status = 'Canceled'
WHERE
  -- 空时段直接标记无效
  (pat.start_time IS NULL OR pat.end_time IS NULL)
  -- 时间超出有效时段
  OR TIME(m.arrival_time) < pat.start_time
  OR TIME(m.arrival_time) > pat.end_time
  OR TIME(m.departure_time) < pat.start_time
  OR TIME(m.departure_time) > pat.end_time;

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