如何将0000-00-00日期转为NULL？执行UPDATE语句遇SQLDataException报错求助

这个报错本质是因为你的MySQL实例开启了NO_ZERO_DATE和STRICT_TRANS_TABLES这两个sql_mode选项——这两个规则会阻止系统识别'0000-00-00'这种无效日期，导致你直接用它作为查询条件时触发数据异常。下面给你几个可行的解决办法：

方法一：临时调整sql_mode（推荐快速解决） #

这个方法不需要修改配置文件，临时调整会话级别的sql_mode就能绕过限制：

• 首先查看当前的sql_mode设置：

  SELECT @@sql_mode;
  

• 复制查询结果，去掉其中的STRICT_TRANS_TABLES和NO_ZERO_DATE，然后用SET语句临时更新：
  比如原来的sql_mode是ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION，就执行：

  SET sql_mode = 'ONLY_FULL_GROUP_BY,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
  

• 现在执行你的更新语句就不会报错了：

  UPDATE `my_table` SET `my_date` = NULL WHERE `my_date` = '0000-00-00';
  

• 如果需要恢复原来的sql_mode，把第一步查到的完整内容再用SET语句设置回去即可。

方法二：用函数绕过直接匹配（无需修改sql_mode） #

如果不想调整sql_mode，可以利用STR_TO_DATE函数的特性：当输入的日期字符串无效时，它会返回NULL。我们可以用它来精准筛选目标数据：

UPDATE `my_table` SET `my_date` = NULL WHERE STR_TO_DATE(`my_date`, '%Y-%m-%d') IS NULL AND `my_date` = '0000-00-00';

如果确认字段里只有'0000-00-00'这一种无效日期，也可以简化成：

UPDATE `my_table` SET `my_date` = NULL WHERE STR_TO_DATE(`my_date`, '%Y-%m-%d') IS NULL;

不过要注意，这个简化语句会把所有格式无效的日期都转成NULL，如果你字段里还有其他异常日期（比如'2023-13-01'），也会被一并处理。

方法三：永久修改sql_mode（谨慎操作） #

如果你希望以后都允许'0000-00-00'这种日期存在，或者不想每次都临时调整，可以修改MySQL的配置文件：

• Linux系统：编辑/etc/my.cnf或/etc/mysql/my.cnf
• Windows系统：编辑my.ini文件
  找到sql_mode配置项，去掉其中的STRICT_TRANS_TABLES和NO_ZERO_DATE，保存后重启MySQL服务。
  注意：这个修改会影响整个数据库实例的所有操作，请确认你的业务场景允许这种无效日期存在后再执行。

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