java.sql.SQLException: Value ‘xxx‘ can not be represented as java.sql.Timestamp

参考地址： https://blog.csdn.net/ljj_9/article/details/79402140

---

问题描述

最近做项目查询数据库操作的时候，有一个表的查询操作一直报错，Java 使用 JDBC 连接 MySQL 数据库，在操作值为0的timestamp类型时不能正确的处理，而是默认抛出一个异常：java.sql.SQLException: Cannot convert value '0000-00-00 00:00:00' from column 7 to TIMESTAMP。这一问题在官方文档中有详细说明，详见如下链接：

http://bugs.mysql.com/bug.php?id=19274

在 JDBC 连接串中有一项属性：zeroDateTimeBehavior，可以用来配置出现这种情况时的处理策略，该属性有下列三个属性值：

因此对于这类异常，可以考虑通过修改连接串，附加zeroDateTimeBehavior=convertToNull属性的方式予以规避，例如：

jdbc:mysql://localhost:3306/mydbname?characterEncoding=utf8&useSSL=false&zeroDateTimeBehavior=convertToNull
1

从另一个层面讲，这类异常的触发也与timestamp赋值的操作有关，如果能够在设计阶段和记录写入阶段做好逻辑判断，避免写入'0000-00-00 00:00:00'这类值，那么也可以避免出现Cannot convert value '0000-00-00 00:00:00' from column N to TIMESTAMP的错误。比如设定一个默认值为当前时间：

alter table 表名 modify CREATE_TIME timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间';
1

问题数据

出现问题的数据和数据表结构。

报错日志

java.sql.SQLException: Value '83533 7dad37625f4a193ba83cc87b7cd4fd783964019829美国第七舰队司令美国第七舰队%https://www.dvidshub.net/unit/USNF-7F40201005001026
                                                                                                                                                              dvidshub.net
                                                                                                                                                                          dvidshub.net!DVIDS - Commander, U.S. 7th Fleet%https://www.dvidshub.net/unit/USNF-7F%https://www.dvidshub.net/unit/USNF-7F00100000-00-00 00:00:00
1659606065
1659606065
16596060652000
172.22.74.127vPhttps://d34w7g4gy10iej.cloudfront.net/video/2207/DOD_109138880/DOD_109138880.mp4Ynews_article_396401_a259e747c3648c47625c226be3ad8298_9f1efc70f1653de4fb1cb73a08f9c9ab.mp4Ynews_article_396401_a259e747c3648c47625c226be3ad8298_9f1efc70f1653de4fb1cb73a08f9c9ab.mp4
' can not be represented as java.sql.Timestamp
1
2
3
4
5
6
7
8

详细日志

INFO - 本次 HYNewsCustomV2 轮询起始id是：83514，执行SQL：SELECT * FROM articles_news WHERE id > ? ORDER BY id LIMIT 100
ERROR - error getting messages from database
java.sql.SQLException: Value '83533 7dad37625f4a193ba83cc87b7cd4fd783964019829美国第七舰队司令美国第七舰队%https://www.dvidshub.net/unit/USNF-7F40201005001026
                                                                                                                                                              dvidshub.net
                                                                                                                                                                          dvidshub.net!DVIDS - Commander, U.S. 7th Fleet%https://www.dvidshub.net/unit/USNF-7F%https://www.dvidshub.net/unit/USNF-7F00100000-00-00 00:00:00
1659606065
1659606065
16596060652000
172.22.74.127vPhttps://d34w7g4gy10iej.cloudfront.net/video/2207/DOD_109138880/DOD_109138880.mp4Ynews_article_396401_a259e747c3648c47625c226be3ad8298_9f1efc70f1653de4fb1cb73a08f9c9ab.mp4Ynews_article_396401_a259e747c3648c47625c226be3ad8298_9f1efc70f1653de4fb1cb73a08f9c9ab.mp4
' can not be represented as java.sql.Timestamp
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:965)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:898)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:887)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:861)
        at com.mysql.jdbc.ResultSetRow.getTimestampFast(ResultSetRow.java:947)
        at com.mysql.jdbc.BufferRow.getTimestampFast(BufferRow.java:559)
        at com.mysql.jdbc.ResultSetImpl.getTimestampInternal(ResultSetImpl.java:5913)
        at com.mysql.jdbc.ResultSetImpl.getTimestamp(ResultSetImpl.java:5581)
        at com.mysql.jdbc.ResultSetImpl.getObject(ResultSetImpl.java:4541)
        at com.zyyt.data.access.sources.DatabasePollableSource.rs2Message(DatabasePollableSource.java:78)
        at com.zyyt.data.access.sources.DatabasePollableSource.getMessages(DatabasePollableSource.java:43)
        at com.zyyt.data.access.sources.news.HYDatabaseSource.doPoll(HYDatabaseSource.java:55)
        at com.zyyt.data.access.StatefulPollableSource.doProcess(StatefulPollableSource.java:51)
        at org.apache.flume.source.AbstractPollableSource.process(AbstractPollableSource.java:60)
        at org.apache.flume.source.PollableSourceRunner$PollingRunner.run(PollableSourceRunner.java:133)
        at java.base/java.lang.Thread.run(Unknown Source)
ERROR - Unhandled exception, logging and sleeping for 5000ms
org.apache.flume.FlumeException: error getting messages from database
        at com.zyyt.data.access.sources.DatabasePollableSource.getMessages(DatabasePollableSource.java:50)
        at com.zyyt.data.access.sources.news.HYDatabaseSource.doPoll(HYDatabaseSource.java:55)
        at com.zyyt.data.access.StatefulPollableSource.doProcess(StatefulPollableSource.java:51)
        at org.apache.flume.source.AbstractPollableSource.process(AbstractPollableSource.java:60)
        at org.apache.flume.source.PollableSourceRunner$PollingRunner.run(PollableSourceRunner.java:133)
        at java.base/java.lang.Thread.run(Unknown Source)
Caused by: java.sql.SQLException: Value '83533 7dad37625f4a193ba83cc87b7cd4fd783964019829美国第七舰队司令美国第七舰队%https://www.dvidshub.net/unit/USNF-7F40201005001026
                                                                                                                                                                         dvidshub.net
                                                                                                                                                                                     dvidshub.net!DVIDS - Commander, U.S. 7th Fleet%https://www.dvidshub.net/unit/USNF-7F%https://www.dvidshub.net/unit/USNF-7F00100000-00-00 00:00:00
1659606065
1659606065
16596060652000
172.22.74.127vPhttps://d34w7g4gy10iej.cloudfront.net/video/2207/DOD_109138880/DOD_109138880.mp4Ynews_article_396401_a259e747c3648c47625c226be3ad8298_9f1efc70f1653de4fb1cb73a08f9c9ab.mp4Ynews_article_396401_a259e747c3648c47625c226be3ad8298_9f1efc70f1653de4fb1cb73a08f9c9ab.mp4
' can not be represented as java.sql.Timestamp
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:965)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:898)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:887)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:861)
        at com.mysql.jdbc.ResultSetRow.getTimestampFast(ResultSetRow.java:947)
        at com.mysql.jdbc.BufferRow.getTimestampFast(BufferRow.java:559)
        at com.mysql.jdbc.ResultSetImpl.getTimestampInternal(ResultSetImpl.java:5913)
        at com.mysql.jdbc.ResultSetImpl.getTimestamp(ResultSetImpl.java:5581)
        at com.mysql.jdbc.ResultSetImpl.getObject(ResultSetImpl.java:4541)
        at com.zyyt.data.access.sources.DatabasePollableSource.rs2Message(DatabasePollableSource.java:78)
        at com.zyyt.data.access.sources.DatabasePollableSource.getMessages(DatabasePollableSource.java:43)
        ... 5 more
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54