java - Mysql datetime 变为 0000-00-00 00 :00:00 in mysql version 5. 6

Question

我们有一个基于 java 的程序，其中包含数十万行代码，在 mysql 5.5 之前的过去 8-9 年里一直运行良好。一个客户安装了mysql 5.6.17，现在我们面临一个大问题:Datetime值变成0000-00-00 00:00:00这是其中一个表: 如您所见，默认值为 Null:

+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| Id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| Key1  | varchar(10) | NO   | MUL | NULL    |                |
| Key2  | varchar(25) | NO   |     | NULL    |                |
| Date  | datetime    | YES  |     | NULL    |                |
| Value | text        | NO   |     | NULL    |                |

然后我们通过 java 代码中的 mysql-connector 像这样插入:

String sql = "INSERT INTO DATA_SUNDRYMATRIX (Key1, Key2, Date, Value) VALUES(?,?,?,?)";
PreparedStatement p = c.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
p.setString(1, i.getKey1());
p.setString(2, i.getKey2());
p.setTimestamp(3, i.getDate()==null?null:new Timestamp(i.getDate().getTime()));
p.setString(4, i.getValue());
p.executeUpdate();

我可以在 Preparedstatement 上取出一个系统 println 来查看 executeupdate 之前的 sql，它看起来像这样:

com.mysql.jdbc.ServerPreparedStatement[1] - INSERT INTO DATA_SUNDRYMATRIX (Key1, Key2, Date, Value) VALUES('TEST2','','2014-08-04 14:46:15','')

但是表中的结果还是'0000-00-00 00:00:00。这是为什么？？真正奇怪的是，如果我将插入查询复制/粘贴到 mysql 控制台，则日期时间值设置正确。我试过升级到 mysql-connector-java-5.1.31-bin.jar 但没有成功。选择@@sql_mode；给出 NO_ENGINE_SUBSTITUTIONlas，由于大量遗留表和代码，我们无法将其设置为严格模式。我不认为 ?zeroDateTimeBehavior=convertToNull 有帮助，因为我们不想要空值，我们想要实际值，并且它们以某种方式转换为 0000-00-00，即使 NULL 是表的默认值。 (顺便说一下，更新也是如此)这个 mysql-setup 到底出了什么问题？

编辑:我还有一些信息。我现在用以下内容扩展了我的代码:

System.out.println(p);
p.executeUpdate();
SQLWarning warning = p.getWarnings();
while (warning != null){
    System.out.println("ToString(): "+warning.toString());
    System.out.println("ErrorCode: "+warning.getErrorCode());
    System.out.println("LocalizedMessage: "+warning.getLocalizedMessage());
    System.out.println("State: "+warning.getSQLState());
    warning = warning.getNextWarning();
}

输出是:

com.mysql.jdbc.ServerPreparedStatement[1] - INSERT INTO DATA_SUNDRYMATRIX (Key1, Key2, Date, Value) VALUES('TEST2','','2014-08-04 15:35:02','')
ToString(): java.sql.SQLWarning: Data truncated for column 'Date' at row 1
ErrorCode: 1265
LocalizedMessage: Data truncated for column 'Date' at row 1
State: 01000

所以我实际上收到了一个数据截断警告，即使我正确地使用了 preparedstatements setTimeStamp(对于我们尝试过的所有其他 mysql 版本)并且在执行它之前打印 PreparedStatement 对象时 sql 似乎没问题。那么数据截断的原因是什么？

编辑#2有人告诉我尝试使用 setDate 而不是 setTimestamp 来查看是否会出现相同的错误。它没有，但我不能使用 setDate，因为我没有得到 DATETIME 的时间部分，而只有日期。正如我所说，准备好的 PreparedStatement 的 System.out.println 给出“INSERT INTO DATA_SUNDRYMATRIX (Key1, Key2, Date, Value) VALUES('TEST2','','2014-08-04 15:35:02', '')"这是有效的 sql，并且在将其复制到 mysql 控制台时没有警告和截断。我现在也直接通过以下代码尝试了这个sql:

String sql = "INSERT INTO DATA_SUNDRYMATRIX (Key1, Key2, Date, Value) VALUES('TEST2','','2014-08-04 15:35:02','')";
PreparedStatement p = c.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
p.executeUpdate();

这也给出了正确的结果，没有警告也没有截断。只有当我在 PreparedStatement 上使用 setTimestamp 时才会发生截断。我尝试过新旧版本的 jconnector，3.1.10、5.1.18 和 5.1.31。所有版本都可以在我的旧 mysql 版本上正常工作，并在 mysql 5.6.17 中产生这个特殊的错误。

编辑#3我注意到 setTimestamp(1, new Timestamp(0));没有截断，而是 1970 年 1 月的正确日期。因此我做了一些小测试代码:

//CREATE TABLE `test` (Id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY, `Test` DATETIME NULL DEFAULT NULL);
String sql = "INSERT INTO Test (Id, Test) VALUES (?,?)";
PreparedStatement p = c.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
long current = System.currentTimeMillis();
for (long t = 0; t < current; t += (long)100000000){
    p.setLong(1,t);
    p.setTimestamp(2, new Timestamp(t));
    p.executeUpdate();
}
p.close();
p = c.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
p.setLong(1,current);
p.setTimestamp(2, new Timestamp(current));
p.executeUpdate();
p.close();

这只给出了一个不正确的结果，即最后一个。 SELECT * FROM test 给出以下最后几行:

| Id            | Test                |
+---------------+---------------------+
....
| 1407000000000 | 2014-08-02 19:20:00 |
| 1407100000000 | 2014-08-03 23:06:40 |
| 1407200000000 | 2014-08-05 02:53:20 |
| 1407224107714 | 0000-00-00 00:00:00 |

编辑#4似乎存在一个错误，即如果在设置 setTimestamp 时时间戳中存在亚秒级精度，则不会截断纳秒，而是破坏整个日期时间。如果我执行以下操作，它似乎工作正常:

p.setTimestamp(2, new Timestamp(current/1000*1000));

然后我会得到结果

| 1407225888000 | 2014-08-05 10:04:48 |

我可能很快会把它写成一个解决方案。但是我将不得不在我们的代码中修复很多 setTimestamp 值。任何人都可以确认这是否仍然是 5.6.17 之后 mysql 数据库中的错误？

编辑#5(解决方案)useServerPrepStmts=false 的构造函数建议解决了这个问题!这是一个更好的解决方案，我自己的/1000*1000 -我发现的解决方案，因为我只需要更改连接字符串而不是每个 setTimestamp 调用(好吧，我已经在最后一个小时完成了这个，但很高兴有也是“真正的”解决方案 ;-) )

Answer 1

由于服务器端准备好的语句是disabled by default来自 Connector/J 5.0.5 on ，很有可能结合fractional second support in MySQL 5.6.4的介绍它们会导致您的问题。

尝试将 useServerPrepStmts=false 添加到您的连接字符串(这也应该确保 System.out.println 真正打印执行的 SQL 而不仅仅是近似值).