本文内容: 1、起因 2、MySQL 对 0000-00-00 的支持 3、Java 对 0000-00-00 的支持 4、为什么线上的代码能正常运行? |
|| 起因
前几天组内有系统做了数据库迁移,MySQL版本 5.6.16,其中某张表的一个字段是这么定义的:
create_time TIMESTAMP DEFAULT '0000-00-00 00:00:00' COMMENT '创建时间'
迁移后发现新表里原本应该为 '0000-00-00 00:00:00' 的字段的值,都变成了迁移时当前时间,后来得知是XX云RDS的bug导致。
今天先抛开这个bug,只研究一下这个默认值的问题,因为之前从来没这么用过,正好借此问题学习一下。
|| MySQL 对 0000-00-00 的支持
| “zero” date or time
https://dev.mysql.com/doc/refman/5.6/en/date-and-time-types.html
官网在日期数据类型的使用注意事项中提到了"zero" date or time:
-
日期或时间类型的值超出了范围或该类型无效时,默认会转换成"zero"值。特殊情况会转换成对应类型范围的极值。
-
DATE、DATETIME类型中,允许月、日为空, 例如"2009-00"或"2009-01-00",但是这种值用在日期函数中会出错。
-
支持用 "0000-00-00" 代替 "zero" 值存储。在某些情况下比使用NULL要方便,还节省数据和索引空间。如果要禁用'0000-00-00',需要启用 NO_ZERO_DATE 模式。
-
Connector/ODBC 不支持 "zero" 值,会把这种值转换成NULL(说白了就是我们平时使用的图形界面的客户端)。
下图是官网给出的几种日期类型对应的零值(也可以使用 '0' 或 0 代替):
| NO_ZERO_DATE 模式
上面提到了 NO_ZERO_DATE模式,我们就继续看一下这个模式是什么。
https://dev.mysql.com/doc/refman/5.6/en/sql-mode.html
NO_ZERO_DATE是众多SQL Mode之一,可以通过命令行启动参数或者修改MySQL的配置文件来控制具体启用哪些模式。下面是启用、查看SQL Mode的语法。
-- 设置模式
SET GLOBAL sql_mode = 'modes';
SET SESSION sql_mode = 'modes';
-- 查看模式
SELECT @@GLOBAL.sql_mode;
SELECT @@SESSION.sql_mode;
这个模式决定着 '0000-00-00' 是否是一个有效值。
-
未启用,可以插入 '0000-00-00'。
-
启用,插入 '0000-00-00' 有警告。
-
启用 并且在Strict SQL模式下,插入会报错,如果指定了INSERT IGNORE 和 UPDATE IGNORE,插入的时候只会发出警告。
| Strict SQL 模式
因为很多模式的效果都会受到Strict SQL模式的影响,包括上面说的NO_ZERO_DATE,所以这里捎带说一下Strict SQL模式。
严格模式主要是控制插入、更新时的无效值(包括类型错误、数值越界、除数为0),如果是查询类的只会发出警告。从5.6.11开始,严格模式下如果数值越界会报错,但这之前会截取到边界值来使用。
如果STRICT_ALL_TABLES或STRICT_TRANS_TABLES两种模式任意启用一个,都相当于启用了Strict SQL。
所以到底到报错还是警告,是受多种模式共同影响的。
|| Java 对 0000-00-00 的支持
| Date / SimpleDateFormat
自己写了一个测试类,运行结果很是诧异,看了几遍内部处理逻辑,没看懂。
结论就是:不报错,但结果是不准确的。
public class DateTest {
public static void main(String[] args) throws Exception{
Date date = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").parse("0000-00-00 00:00:00");
System.out.println(date);
// 运行结果:Sun Nov 30 00:00:00 CST 2
System.out.println(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(date));
// 运行结果:0002-11-30 00:00:00
System.out.println(date.getTime());
// 运行结果:-62170185600000
System.out.println(new Date(-62170185600000L));
// 运行结果:Sun Nov 30 00:00:00 CST 2
}
}
| LocalDate
public class DateTest {
public static void main(String[] args) throws Exception{
System.out.println(LocalDateTime.parse("0000-00-00 00:00:00",
DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss")));
}
}
运行结果:
Exception in thread "main" java.time.format.DateTimeParseException: Text '0000-00-00 00:00:00' could not be parsed: Invalid value for YearOfEra (valid values 1 - 999999999/1000000000): 0
at java.time.format.DateTimeFormatter.createError(DateTimeFormatter.java:1920)
at java.time.format.DateTimeFormatter.parse(DateTimeFormatter.java:1855)
at java.time.LocalDateTime.parse(LocalDateTime.java:492)
at com.DateTest.main(DateTest.java:17)
Caused by: java.time.DateTimeException: Invalid value for YearOfEra (valid values 1 - 999999999/1000000000): 0
异常信息已经很明确了,Year的数值不在有效范围。
其他各个时间单位的有效值范围,都可以在这个类里找到java.time.temporal.ChronoField
| 通过 JDBC 查询这种数据
Connection conn = DriverManager.getConnection(URL, USER, PASSWORD);
ResultSet resultSet = conn.prepareStatement("select create_time from bla limit 1").executeQuery();
if(resultSet.next()){
System.out.println(resultSet.getTimestamp(1));
}
结果报错了:
Exception in thread "main" java.sql.SQLException: Value '0000-00-00 00:00:00' can not be represented as java.sql.Date
|| 为什么线上代码没问题?
| MySQL Client 对 0000-00-00 是如何转换的?
顺着代码捋一下,看看到底哪一步报的错,首先看服务端返回的信息,是一个字节数组,转成字符串后就是建表时的默认值 "0000-00-00 00:00:00",说明server是支持把这种默认值吐给client的。
继续向下找,看到是在字节数组转日期的时候报错了。
com.mysql.jdbc.ResultSetRow#getDateFast(int columnIndex, byte[] dateAsBytes, int offset, int length, MySQLConnection conn, ResultSetImpl rs, Calendar targetCalendar) L118
这个方法里规定了几个合法日期里的肯定会包含字符,结果字节数组里的值都没能满足,所以就报错了。
| 玄机竟然在这里 !!!
再仔细研究一下上面的方法,zeroDate 抛出异常是因为 connection 的一个属性导致的。
这个属性是什么时候赋值为"exception"的?
从头再debug一遍,发现在建立连接前,解析URL的过程中会实例化
com.mysql.jdbc.ConnectionPropertiesImpl,这时已经有了默认值"exception",如果URL带有此属性的参数,就会覆盖这个默认值。
既然是在解析URL时赋的值,那就翻一下线上的代码,还真是URL上的参数影响的。
URL = "jdbc:mysql://****:3306/****?zeroDateTimeBehavior=convertToNull";
建立连接的时候有了这个参数,在接收转换日期数据的时候,"zeroDate" 就会被转换成null,所以线上这种情况一直是按null处理的。
顺便整理一下zeroDateTimeBehavior参数对应的几个值都有什么作用:
| 抛异常 |
| 转换为边界值返回 |
| 返回null |
|| 总结
1、不要使用 0 做默认值了,可以使用 1970-01-01 00:00:00
2、尽量开启 Strict SQL Mode
3、注意MySQL不同版本间各种模式的差异
4、工作中试着用LocalDate 替换 Date吧
之前写过一篇 #日期 相关的文章,可以参考。