解决 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;
总结
- 查找非法日期时间:使用 SQL 查询和正则表达式筛选出不合法的
assign_customer_time
记录。 - 修正非法日期时间:根据具体情况将非法日期时间设置为
NULL
或修正为默认值。 - 防止未来数据不合法:通过添加约束或触发器,确保未来插入的日期时间合法。
通过以上步骤,可以彻底解决 MyBatis 异常问题,并确保数据库中的日期时间字段数据合法和规范。