在JDBC中处理DATETIME值0000-00-00 00:00:00

Question

如果我尝试执行以下操作，会出现异常（请参见下文）：

resultset.getString("add_date");

对于一个包含DATETIME值为0000-00-00 00:00:00（DATETIME的准空值）的MySQL数据库的JDBC连接，即使我只是尝试将该值作为字符串获取而不是作为对象，仍然会出现问题。

我通过以下方式解决了这个问题：

SELECT CAST(add_date AS CHAR) as add_date

这种方法可以运行，但似乎很傻...有更好的方法吗？

我的意思是我只想要原始DATETIME字符串，这样我就可以按照原样解析它。

注意：这里的0000是从http://dev.mysql.com/doc/refman/5.0/en/datetime.html中来的。

不合法的DATETIME、DATE或TIMESTAMP值会被转换为相应类型的“零”值（'0000-00-00 00:00:00'或'0000-00-00'）。

具体的例外情况如下：

SQLException: Cannot convert value '0000-00-00 00:00:00' from column 5 to TIMESTAMP.
SQLState: S1009
VendorError: 0
java.sql.SQLException: Cannot convert value '0000-00-00 00:00:00' from column 5 to TIMESTAMP.
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1055)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:926)
    at com.mysql.jdbc.ResultSetImpl.getTimestampFromString(ResultSetImpl.java:6343)
    at com.mysql.jdbc.ResultSetImpl.getStringInternal(ResultSetImpl.java:5670)
    at com.mysql.jdbc.ResultSetImpl.getString(ResultSetImpl.java:5491)
    at com.mysql.jdbc.ResultSetImpl.getString(ResultSetImpl.java:5531)

- Jason S

Answer 1

另一种回答是，您可以直接在数据源配置中使用此JDBC URL：

jdbc:mysql://yourserver:3306/yourdatabase?zeroDateTimeBehavior=convertToNull

编辑：

来源：MySQL 手册

日期时间的所有零组件（0000-00-00…）——在 Java 中无法可靠地表示这些值。Connector/J 3.0.x 在从 ResultSet 读取时总是将它们转换为 NULL。

当遇到这些值时，Connector/J 3.1 默认会抛出异常，因为这是 JDBC 和 SQL 标准中最正确的行为。可以使用 zeroDateTimeBehavior 配置属性修改此行为。允许的值如下：

• exception（默认值），抛出一个带有 SQLState 为 S1009 的 SQLException。
• convertToNull，返回 NULL 而不是日期。
• round，将日期四舍五入到最接近的值，即 0001-01-01。

更新：Alexander 报告了一个影响 mysql-connector-5.1.15 的 bug。请参阅官方网站上的 CHANGELOGS。

- Brian Clozel

---

有趣。你从哪里找到这个信息的？ - Jason S

刚更新了我的答案！但在这种情况下，@sarumont的URL可能更合适（它列出了所有连接器属性）。 - Brian Clozel

jdbc软件的5.1.16版本包含了这个bug修复:

• 修复了BUG#57808 - 对于getDate()中值为0000-00-00的DATE字段，尽管zeroDateTimeBehavior设置为convertToNull，但wasNull未被设置。

- Alexander Kjäll

哇！谢谢你提供这个信息。我猜你花了不少时间才弄明白它 :-( - Brian Clozel

Answer 2

我发现这个问题并试图解决它。 我正在使用JBOSS和Hibernate进行安装，因此我必须以不同的方式处理它。 对于基本情况，您应该能够根据配置属性页面将zeroDateTimeBehavior=convertToNull添加到连接URI中。

我在其他地方找到了其他建议，涉及将该参数放入您的hibernate配置中：

在 hibernate.cfg.xml 中：

<property name="hibernate.connection.zeroDateTimeBehavior">convertToNull</property>

在 hibernate.properties 中：

hibernate.connection.zeroDateTimeBehavior=convertToNull

但是我不得不将它放在我的mysql-ds.xml文件中，供JBOSS使用：

<connection-property name="zeroDateTimeBehavior">convertToNull</connection-property>

希望这能帮到某个人。 :)

- sarumont

---

对于我来说，在 hibernate.cfg.xml 文件上进行更改就可以解决问题，谢谢，因为我对 Java 几乎一无所知。 - Gendrith

Answer 3

我的意思是，我只想要原始的日期时间字符串，这样我就可以自己解析它。

这让我想到你的“变通方法”并不是一种变通方法，而实际上是从数据库中获取值到代码中的唯一方式：

SELECT CAST(add_date AS CHAR) as add_date

顺便提一下，以下是从MySQL文档中获取的一些注释：

MySQL 对无效数据的限制：

在 MySQL 5.0.2 之前，MySQL 对非法或不合适的数据值宽容处理，并将它们转换为合法值以进行数据输入。对于 MySQL 5.0.2 及更高版本，这仍然是默认行为，但您可以更改服务器SQL模式以选择更传统的坏值处理方式，使服务器拒绝它们并中止其中出现的语句。

[..]

如果您尝试将NULL存储到不接受NULL值的列中，则会出现单行INSERT语句的错误。对于多行INSERT语句或INSERT INTO ... SELECT语句，MySQL服务器会存储列数据类型的隐含默认值。

MySQL 5.x 日期和时间类型：

MySQL还允许您将“0000-00-00”作为“虚拟日期”存储（如果您没有使用NO_ZERO_DATE SQL模式）。在某些情况下，这比使用NULL值更方便（并且使用更少的数据和索引空间）。

[..]

默认情况下，当MySQL遇到超出范围或其他方面非法的日期或时间类型的值时（如本节开头所述），它会将该值转换为该类型的“零”值。

- Arjan

Answer 4

DATE_FORMAT(column name, '%Y-%m-%d %T') as dtime

使用此方法可以避免出现错误。它以字符串格式返回日期，然后您可以将其作为字符串获取。

resultset.getString("dtime");

实际上这并不起作用。即使您调用getString，内部mysql仍然会尝试首先将其转换为日期。

在com.mysql.jdbc.ResultSetImpl.getDateFromString(ResultSetImpl.java: 2270)

~ [mysql-connector-java-5.1.15.jar：na]在com.mysql.jdbc.ResultSetImpl.getStringInternal(ResultSetImpl.java:5743)

~ [mysql-connector-java-5.1.15.jar：na]在com.mysql.jdbc.ResultSetImpl.getString(ResultSetImpl.java:5576)

~ [mysql-connector-java-5.1.15.jar：na]

- Atul

---

这与我的CAST(add_date AS CHAR)解决方案有些相似，但是因为它允许您明确地按照所需格式进行格式化，所以我会给它加上+1。 - Jason S

Answer 5

如果在添加行之后：

<property
name="hibernate.connection.zeroDateTimeBehavior">convertToNull</property>

hibernate.connection.zeroDateTimeBehavior=convertToNull

<connection-property
name="zeroDateTimeBehavior">convertToNull</connection-property>

仍存在一个错误：

Illegal DATETIME, DATE, or TIMESTAMP values are converted to the “zero” value of the appropriate type ('0000-00-00 00:00:00' or '0000-00-00').

查找行：

1) resultSet.getTime("time"); // time = 00:00:00
2) resultSet.getTimestamp("timestamp"); // timestamp = 00000000000000
3) resultSet.getDate("date"); // date = 0000-00-00 00:00:00

请分别替换为以下几行：

1) Time.valueOf(resultSet.getString("time"));
2) Timestamp.valueOf(resultSet.getString("timestamp"));
3) Date.valueOf(resultSet.getString("date"));

- Vadim

Answer 6

我曾经遇到过这个问题，并实施了上面讨论过的“convertToNull”解决方案。在我的本地 MySql 实例中它能够正常工作。但是当我将我的 Play/Scala 应用部署到 Heroku 上时，它就无法工作了。Heroku 还会将几个参数连接到他们提供给用户的 DB URL 上，因为 Heroku 在他们自己的一组参数之前使用了“？”的连接方式，所以这个解决方案将不再奏效。然而，我找到了一个不同的解决方案，似乎同样有效。

SET sql_mode = 'NO_ZERO_DATE';

我将其放在我的表描述中，解决了“0000-00-00 00:00:00”无法表示为 java.sql.Timestamp 的问题。

- Aqume

Answer 7

我解决了这个问题，考虑到 '00-00-....' 不是一个有效的日期，因此我更改了我的 SQL 列定义，添加了 "NULL" 表达式来允许空值：

SELECT "-- Tabla item_pedido";
CREATE TABLE item_pedido (
    id INTEGER AUTO_INCREMENT PRIMARY KEY,
    id_pedido INTEGER,
    id_item_carta INTEGER,
    observacion VARCHAR(64),
    fecha_estimada TIMESTAMP,
    fecha_entrega TIMESTAMP NULL, // HERE IS!!.. NULL = DELIVERY DATE NOT SET YET
    CONSTRAINT fk_item_pedido_id_pedido FOREIGN KEY (id_pedido)
        REFERENCES pedido(id),...

然后，我需要能够插入NULL值，这意味着“我还没有注册该时间戳”...

SELECT "++ INSERT item_pedido";
INSERT INTO item_pedido VALUES
(01, 01, 01, 'Ninguna', ADDDATE(@HOY, INTERVAL 5 MINUTE), NULL),
(02, 01, 02, 'Ninguna', ADDDATE(@HOY, INTERVAL 3 MINUTE), NULL),...

表格的样子如下：

mysql> select * from item_pedido;
+----+-----------+---------------+-------------+---------------------+---------------------+
| id | id_pedido | id_item_carta | observacion | fecha_estimada      | fecha_entrega       |
+----+-----------+---------------+-------------+---------------------+---------------------+
|  1 |         1 |             1 | Ninguna     | 2013-05-19 15:09:48 | NULL                |
|  2 |         1 |             2 | Ninguna     | 2013-05-19 15:07:48 | NULL                |
|  3 |         1 |             3 | Ninguna     | 2013-05-19 15:24:48 | NULL                |
|  4 |         1 |             6 | Ninguna     | 2013-05-19 15:06:48 | NULL                |
|  5 |         2 |             4 | Suave       | 2013-05-19 15:07:48 | 2013-05-19 15:09:48 |
|  6 |         2 |             5 | Seco        | 2013-05-19 15:07:48 | 2013-05-19 15:12:48 |
|  7 |         3 |             5 | Con Mayo    | 2013-05-19 14:54:48 | NULL                |
|  8 |         3 |             6 | Bilz        | 2013-05-19 14:57:48 | NULL                |
+----+-----------+---------------+-------------+---------------------+---------------------+
8 rows in set (0.00 sec)

最后，JPA的实战应用：

@Stateless
@LocalBean
public class PedidosServices {
    @PersistenceContext(unitName="vagonpubPU")
    private EntityManager em;

    private Logger log = Logger.getLogger(PedidosServices.class.getName());

    @SuppressWarnings("unchecked")
    public List<ItemPedido> obtenerPedidosRetrasados() {
        log.info("Obteniendo listado de pedidos retrasados");
        Query qry = em.createQuery("SELECT ip FROM ItemPedido ip, Pedido p WHERE" +
                " ip.fechaEntrega=NULL" +
                " AND ip.idPedido=p.id" +
                " AND ip.fechaEstimada < :arg3" +
                " AND (p.idTipoEstado=:arg0 OR p.idTipoEstado=:arg1 OR p.idTipoEstado=:arg2)");
        qry.setParameter("arg0", Tipo.ESTADO_BOUCHER_ESPERA_PAGO);
        qry.setParameter("arg1", Tipo.ESTADO_BOUCHER_EN_SERVICIO);
        qry.setParameter("arg2", Tipo.ESTADO_BOUCHER_RECIBIDO);
        qry.setParameter("arg3", new Date());

        return qry.getResultList();
    }

最后，所有的工作都完成了。希望这能对你有所帮助。

- EdU

---

我认为这并没有解决将MySQL DATETIME的所有零转换为有效日期的问题。它只是展示了如何将null输入到DATETIME字段中，这实际上并没有太大帮助。 - Mark Chorley

Answer 8

我建议您使用null来表示空值。

您收到了什么异常？

顺便说一下：

没有0或0000年。（尽管某些日期允许这一年份）

并且没有每年的0月或0日。（这可能是您问题的原因）

- Peter Lawrey

---

是的，但即使回顾过去，也没有叫做0年的年份。公元1年之前的那一年是公元前1年。 - Peter Lawrey

这就是MySQL使用0000作为无效日期的全部原因，因为它不会与现有日期冲突。此外，像1999-00-00这样的日期有时被用来表示1999年而不是具体的某一天（虽然我不会这么做！）。 - Jason S

Answer 9

补充其他答案：如果您想要字符串“0000-00-00”，您可以使用“noDatetimeStringSync=true”（但需要牺牲时区转换）。

官方MySQL错误：https://bugs.mysql.com/bug.php?id=47108。

另外，对于历史记录，JDBC过去返回NULL用于0000-00-00日期，但现在默认返回异常。来源：Source

- damio

Answer 10

你可以将 JDBC URL 追加。

?zeroDateTimeBehavior=convertToNull&autoReconnect=true&characterEncoding=UTF-8&characterSetResults=UTF-8

通过这个功能，可以将SQL中的 '0000-00-00 00:00:00' 转换成 null 值。

例如：

jdbc:mysql:<host-name>/<db-name>?zeroDateTimeBehavior=convertToNull&autoReconnect=true&characterEncoding=UTF-8&characterSetResults=UTF-8

- Mukul Aggarwal