0000-00-00 00:00:00 的坑，你踩了吗？_1024个为什么的博客

link之家

链接快照平台

输入网页链接，自动生成快照
标签化管理网页链接

| | 起因

前几天组内有系统做了数据库迁移，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吧 
 之前写过一篇 #日期 相关的文章，可以参考。 
CREATE TABLE `login_log` (
 `id`          bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT ,
 `uuid`        VARCHAR(34) UNIQUE NOT NULL  COMMENT '登录编号',
 `number`      VARCHAR(20) NOT NULL  COMMENT '登录账号
                                    在java应用程序中，数据表中有记录的time字段（属性为timestamp）其值为：“0000-00-00 00:00:00”
程序使用select 语句从中取数据时出现以下异常：
java.sql.SQLException:Value '0000-00-00' can not be represented as java.sql.Date
这是因为 “0000-00-00 00:00:0...
                                    在创建文件时，由于时间那一栏没有填写，从而出现了默认值“0000-00-00 00:00:00”这时如果select时会抛出SQLExecption如下：
java.sql.SQLException: Value ‘0000-00-00 00:00:00’ can not be represented as java.sql.Timestamp，也可以不要在代码中用java.sql.Timesta...
                                    在解决问题之前，我们要先了解一下mysql的严格模式和非严格模式，有兴趣可以自己查看一下官方文档
顾名思义，就是比非严格模式更为严格，主要就是对数据的要求更为严格，比如数据的类型，长度，格式等。比如一个整型字段我们写入一个字符串类型的数据，在非严格模式下MySQL不会报错，同样如果定义了char或varchar类型的字段，当写入或更新的数据超过了定义的长度也不会报错。还有一些关于null...
                                    Date date = new Date();SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");String dt = simpleDateFormat.format(date);
                                    String dateStr = "0000-00-00";
SimpleDateFormat sdf = new SimpleDateFormat(datePattern);
Timestamp time = new Timestamp(sdf.parse(dateStr).getTime());
System.out.println(time.toString());
                                    比如一个整型字段我们写入一个字符串类型的数据，在非严格模式下MySQL不会报错，同样如果定义了char或varchar类型的字段，当写入或更新的数据超过了定义的长度也不会报错，还有一些关于null的数据问题也不会报错。通过查阅资料可知，这是mysql的严格模式造成的，看了网上的一些方法，有更改数据库配置为非严格模式的，也有直接执行更新sql的，前者不建议，后者正常执行会报错，转变一下思路即可解决。就是比非严格模式更为严格，主要就是对数据的要求更为严格，比如数据的类型，长度，格式等。但是严格模式会报错。
                                    我有个问题。我正在调用我的web服务器,它返回一个JSON字符串。在这个JSON字符串中,有几个对象如下:public class Transaction {private int id;private LocalDateTime datetime;private double quantity;private double avgPrice;public ArrayList parseJsonT...
                                    今天报表程序发生异常，最后发现发生异常的原因是因为数据库表中，一条结果的时间字段的值为'0000-00-00 00:00:00' ,google了一把，结果发现很多人都遇到相同的问题，就是通过JDBC去访问时就会发生此类异常。
以下是解决办法：
在使用MySql 时, 数据库中的字段类型是timestamp的,默认为0000-00-00, 会发生异常:java.sql.SQLException
3、重新建表原因：NO_ZERO_IN_DATE,NO_ZERO_DATE是无法默认为‘0000-00-00 00:00:00’的根源。NO_ZERO_IN_DATE：在严格模式下，不允许日期和月份为零NO_ZERO_DATE：设置该值，mysql数据库不允许插入零日期，插入零日期会抛出错误而不是警告。在这个会话里变量是不生效的，需要退出后在进入查看mysql变量就生效，修改好后记得要修改my.cnf文件让mys
                                    mysql 中为 datetime 类型的字段有时候想插入默认值：0000-00-00 00:00:00，但是却报错无效的值插入
出现这种情况是 mysql 内部默认的 datetime 无 0 配置，可通过 sql 语句 select @@sql_mode; 查看：
ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
                                    记录MySQL5.7 datetime 默认值设为‘0000-00-00 00:00:00’值出错我的MySQL版本 mysql --version 5.7.28C:\Users\x1c>mysql --versionmysql  Ver 14.14 Distrib 5.7.28, for Win64 (x86_64)C:\Users\x1c>我要创建的表结构DROP TABLE IF...
                                    问题描述：
java.time.format.DateTimeParseException: Text '20220310' could not be parsed at index 0
问题分析：
1、使用LocalDate.parse()方法时，要转换的日期字符串格式和指定的日期格式对不上。
LocalDate.parse("20220310", DateTimeFormatter.ofPattern("yyyy-MM-dd"));
解决办法：要转换的日期字符串格式和指定的日期格式一致即可。

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