插入TIMESTAMP或DATETIME时遇8/9值解析异常求助

这问题我之前碰到过！核心原因是MySQL在非严格模式下，会把带前导零的数字（比如08、09）解析为八进制数，但八进制的有效数字只有0-7，8和9属于非法值，会被强制转换为0，最终导致日期字段的月、日、时分等部分出错。

1. 检查并启用MySQL严格模式 #

严格模式会强制MySQL正确处理十进制的前导零数字，同时避免很多数据插入的隐式转换错误。

查看当前SQL_MODE设置 #

先执行这条命令确认当前的模式：

SELECT @@sql_mode;

如果结果里没有STRICT_TRANS_TABLES或STRICT_ALL_TABLES，反而包含NO_UNSIGNED_SUBTRACTION，那就是问题所在。

修改SQL_MODE #

• 临时生效（重启MySQL后失效）：

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

• 永久生效：
  找到MySQL的配置文件（Linux是/etc/my.cnf或/etc/mysql/my.cnf，Windows是my.ini），在[mysqld]段落下添加或修改：

  sql_mode = "STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION"
  

  保存后重启MySQL服务即可。

2. 确保日期格式的正确性 #

• 插入TIMESTAMP/DATETIME字段时，坚持使用标准的YYYY-MM-DD HH:MM:SS格式，严格模式下MySQL会正确解析这种格式的前导零数字。
• 使用STR_TO_DATE时，确保格式符和输入字符串严格匹配，比如你的测试语句STR_TO_DATE("10/08/2018 7:47:25", "%d/%m/%Y %k:%i:%s")，格式符%d/%m/%Y对应日/月/年，所以解析后应该是2018-08-10 07:47:25，启用严格模式后就能得到正确结果。

3. 验证修复效果 #

重新执行之前的测试语句，比如：

INSERT INTO test(date) VALUES("2018-01-08 10:47:47");
SELECT * FROM test WHERE id = 3;

应该返回正确的2018-01-08 10:47:47；再测试STR_TO_DATE：

SELECT STR_TO_DATE("10/08/2018 7:47:25", "%d/%m/%Y %k:%i:%s");

会得到正确的2018-08-10 07:47:25。

严格模式是生产环境的推荐配置，它能帮你捕获很多隐式的数据转换错误，避免脏数据进入数据库，除了这个日期问题，还能防止比如字符串截断、非法数值插入等问题。

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