UPDATE语句遇日期格式错误抛警告而非报错，且全置NULL问题

一、为什么语句不报错仅抛警告？ #

MySQL默认的sql_mode未开启严格模式（如STRICT_TRANS_TABLES、NO_ZERO_DATE等），当str_to_date()无法匹配指定格式解析字符串时，不会终止执行，而是返回NULL并抛出警告。只有开启严格模式后，这类转换错误才会触发报错并中断语句执行。

二、为什么所有日期被置为NULL？ #

当dateColumn的值格式不匹配时，str_to_date()返回NULL，后续的date_format(NULL, '%Y-%m-%d')同样返回NULL。此时CASE语句的ELSE分支结果为NULL，而WHEN dateColumn = '' THEN NULL分支也返回NULL，最终所有行的dateColumn都会被赋值为NULL。

三、修改方案 #

方案1：开启严格模式（优先推荐，避免静默错误） #

执行以下语句临时开启严格模式（重启MySQL后失效，如需永久生效需修改配置文件）：

SET sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

开启后，str_to_date()转换失败时会直接报错，避免错误的NULL覆盖正常数据。

方案2：兼容多格式并保留原始数据 #

如果需要同时支持横杠、斜杠分隔的日期格式，或转换失败时保留原始值，可调整语句：

UPDATE `table` 
SET `dateColumn` = CASE 
  WHEN `dateColumn` = '' THEN NULL 
  -- 先尝试横杠格式解析
  WHEN str_to_date(`dateColumn`, '%m-%d-%Y %h:%i:%s %p') IS NOT NULL THEN date_format(str_to_date(`dateColumn`, '%m-%d-%Y %h:%i:%s %p'), '%Y-%m-%d')
  -- 横杠格式失败则尝试斜杠格式
  WHEN str_to_date(`dateColumn`, '%m/%d/%Y %h:%i:%s %p') IS NOT NULL THEN date_format(str_to_date(`dateColumn`, '%m/%d/%Y %h:%i:%s %p'), '%Y-%m-%d')
  -- 不匹配格式的情况，保留原始值（可根据需求改为NULL）
  ELSE `dateColumn` END;

方案3：用正则提前过滤合法格式 #

通过正则表达式匹配符合要求的日期字符串，仅处理合法格式的行：

UPDATE `table` 
SET `dateColumn` = CASE 
  WHEN `dateColumn` = '' THEN NULL 
  WHEN `dateColumn` REGEXP '^[0-9]{1,2}-[0-9]{1,2}-[0-9]{4} [0-9]{1,2}:[0-9]{2}:[0-9]{2} (AM|PM)$' THEN date_format(str_to_date(`dateColumn`, '%m-%d-%Y %h:%i:%s %p'), '%Y-%m-%d')
  WHEN `dateColumn` REGEXP '^[0-9]{1,2}/[0-9]{1,2}/[0-9]{4} [0-9]{1,2}:[0-9]{2}:[0-9]{2} (AM|PM)$' THEN date_format(str_to_date(`dateColumn`, '%m/%d/%Y %h:%i:%s %p'), '%Y-%m-%d')
  ELSE `dateColumn` END;

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