0000-00-00 00:00:00 的坑，你踩了吗？

本文内容： 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参数对应的几个值都有什么作用：

ZERO_DATETIME_BEHAVIOR_EXCEPTION	抛异常
ZERO_DATETIME_BEHAVIOR_ROUND	转换为边界值返回
ZERO_DATETIME_BEHAVIOR_CONVERT_TO_NULL	返回null

||  总结

1、不要使用 0 做默认值了，可以使用 1970-01-01 00:00:00

2、尽量开启 Strict SQL Mode

3、注意MySQL不同版本间各种模式的差异

4、工作中试着用LocalDate 替换 Date吧

之前写过一篇 #日期 相关的文章，可以参考。