解决 MyBatis 异常：java.time.DateTimeException: Invalid value for DayOfMonth (valid values 1 - 28/31): 0_java.time.datetimeexception: invalid value for hou-优快云博客

本文链接：https://blog.youkuaiyun.com/wangchl0932/article/details/145223128

解决 MyBatis 异常：`Invalid value for DayOfMonth (valid values 1 - 28/31): 0`

在使用 MyBatis 查询数据库时，可能会遇到以下异常：

org.mybatis.spring.MyBatisSystemException: nested exception is org.apache.ibatis.executor.result.ResultMapException: 
Error attempting to get column 'assign_customer_time' from result set.  
Cause: java.time.DateTimeException: Invalid value for DayOfMonth (valid values 1 - 28/31): 0

该异常的根本原因是数据库中 assign_customer_time 字段的值包含非法日期时间（如 2023-02-30 00:00:00 或 0000-00-00 00:00:00），而 MyBatis 在映射结果集时无法将其转换为合法的 java.time.LocalDateTime 对象。

本文将详细分析该异常的原因，并提供解决方案。

异常分析

1. 异常原因

非法日期时间：数据库中的 assign_customer_time 字段包含非法日期时间值（如 0000-00-00 00:00:00 或 2023-02-30 00:00:00）。
MyBatis 映射失败：MyBatis 在将结果集映射为 Java 对象时，尝试将 assign_customer_time 字段转换为 java.time.LocalDateTime，但由于日期时间值非法，抛出 java.time.DateTimeException。

2. 根本问题

数据库中存在非法日期时间值，可能是由于：
- 数据导入时未校验日期时间格式。
- 数据库未启用严格模式，允许插入非法日期时间（如 0000-00-00 00:00:00）。

解决方案

1. 查找非法日期时间记录

通过 SQL 查询，查找 assign_customer_time 字段中不合法的日期时间记录。可以使用正则表达式检查日期时间格式的合法性。

SQL 查询

SELECT id, assign_customer_time
FROM t_customer_management
WHERE 
    -- 将 DATETIME 转换为字符串，并检查是否符合合法的日期时间格式
    DATE_FORMAT(assign_customer_time, '%Y-%m-%d %H:%i:%s') NOT REGEXP '^[0-9]{4}-(0[1-9]|1[0-2])-(0[1-9]|[12][0-9]|3[01]) ([01][0-9]|2[0-3]):[0-5][0-9]:[0-5][0-9]$';

正则表达式说明

^[0-9]{4}：匹配 4 位年份。
-(0[1-9]|1[0-2])：匹配月份（01-12）。
-(0[1-9]|[12][0-9]|3[01])：匹配日期（01-31）。
([01][0-9]|2[0-3])：匹配小时（00-23）。
:[0-5][0-9]：匹配分钟（00-59）。
:[0-5][0-9]$：匹配秒（00-59）。

2. 修正非法日期时间记录

找到非法日期时间记录后，可以根据具体情况修正这些记录。以下是几种常见的修正方法：

方法 1：设置为 `NULL`

将非法日期时间设置为 NULL。

UPDATE t_customer_management
SET assign_customer_time = NULL
WHERE 
    DATE_FORMAT(assign_customer_time, '%Y-%m-%d %H:%i:%s') NOT REGEXP '^[0-9]{4}-(0[1-9]|1[0-2])-(0[1-9]|[12][0-9]|3[01]) ([01][0-9]|2[0-3]):[0-5][0-9]:[0-5][0-9]$';

方法 2：修正为默认值

将非法日期时间修正为一个默认值（如 1970-01-01 00:00:00）。

UPDATE t_customer_management
SET assign_customer_time = '1970-01-01 00:00:00'
WHERE 
    DATE_FORMAT(assign_customer_time, '%Y-%m-%d %H:%i:%s') NOT REGEXP '^[0-9]{4}-(0[1-9]|1[0-2])-(0[1-9]|[12][0-9]|3[01]) ([01][0-9]|2[0-3]):[0-5][0-9]:[0-5][0-9]$';

方法 3：根据逻辑修正

如果某些日期的错误是已知的（如 0000-00-00 00:00:00 应修正为 1970-01-01 00:00:00），可以手动修正：

UPDATE t_customer_management
SET assign_customer_time = '1970-01-01 00:00:00'
WHERE assign_customer_time = '0000-00-00 00:00:00';

3. 防止未来数据不合法

为了避免未来插入不合法的日期时间，可以在数据库中添加约束或触发器。

添加约束

如果日期时间字段是 DATETIME 类型，数据库通常会自动校验日期时间的有效性。如果是字符串类型，可以添加检查约束：

ALTER TABLE t_customer_management
ADD CONSTRAINT chk_datetime_format
CHECK (assign_customer_time IS NULL OR assign_customer_time REGEXP '^[0-9]{4}-(0[1-9]|1[0-2])-(0[1-9]|[12][0-9]|3[01]) ([01][0-9]|2[0-3]):[0-5][0-9]:[0-5][0-9]$');

添加触发器

在插入或更新数据时，使用触发器校验日期时间：

CREATE TRIGGER validate_datetime BEFORE INSERT ON t_customer_management
FOR EACH ROW
BEGIN
    IF NEW.assign_customer_time IS NOT NULL AND DATE_FORMAT(NEW.assign_customer_time, '%Y-%m-%d %H:%i:%s') NOT REGEXP '^[0-9]{4}-(0[1-9]|1[0-2])-(0[1-9]|[12][0-9]|3[01]) ([01][0-9]|2[0-3]):[0-5][0-9]:[0-5][0-9]$' THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Invalid datetime format';
    END IF;
END;