mariadb：jdbc：setTimestamp截断毫秒 [英] mariadb: jdbc: setTimestamp truncates milliseconds

问题描述

在我看来，如果我使用preparedStatement将它们插入到我的mariadb中，则毫秒会被截断。谷歌搜索它不成功，我发现许多类似的问题要么已经解决，要么不适用。但是很难相信我是唯一一个遇到这个问题的人所以我想在向mariadb提交bug之前先问这里。
代码非常简单：

表：

  create table tt（id decimal（10），create_time timestamp（6）default 0）; 
或
 create table tt（id decimal（10），create_time datetime（6）default 0）; 
  

Javacode：

 的Class.forName（ org.mariadb.jdbc.Driver）; 
连接conn = DriverManager.getConnection（DB_URL，root，abc）; 
语句insert1 = conn.createStatement（）; 
 insert1.execute（insert into tt（id，create_time）values（1，'2013-07-18 13：44：22.123456'））; 
 PreparedStatement insert2 = conn.prepareStatement（
insert into tt（id，create_time）values（？，？））; 
 insert2.setInt（1,2）; 
 insert2.setTimestamp（2，new Timestamp（1273017612999L））; 
 insert2.execute（）; 
  

我的DOS控制台输出：

  MariaDB [duc]> select * from tt; 
 + ------ + ---------------------------- + 
 | id | create_time | 
 + ------ + ---------------------------- + 
 | 1 | 2013-07-18 13：44：22.123456 | 
 | 2 | 2010-05-05 02：00：12.000000 | 
 + ------ + ---------------------------- + 
 2行（ 0.00秒）
  

==> id = 2时会丢失毫秒。

这看起来像个错误吗？或者我做错了什么？

操作系统：windows7,64位
MariaDB版本：10.0.12
JDBC连接器：1.1.7（mariadb ）或5.1.32（mysql）

我在Oracle数据库上测试了相同的代码：插入毫秒数。

<感谢您的帮助......

更新：我向mariaDB错误数据库报告了这个问题： https://mariadb.atlassian.net/browse/CONJ-107

解决方案

您需要在MariaDB JDBC驱动程序中启用useFractionalSeconds。例如，jdbc：mariadb：//127.0.0.1：3306 / somedb？useFractionalSeconds = true。

（恕我直言，这应该默认启用，我不是'我知道为什么不是。）

It seems to me that milliseconds gets truncated if I insert them in my mariadb using a preparedStatement. Googling it was not successfull, I found lots of similar problems which are either resolved or do not apply. But it's hard to believe that I am the only one having this problem so I wanted to ask here first before submitting a bug to mariadb. The code is very simple:

Table:

create table tt (id decimal(10), create_time timestamp(6) default 0);
or
create table tt (id decimal(10), create_time datetime(6) default 0);

Javacode:

Class.forName("org.mariadb.jdbc.Driver");
Connection conn = DriverManager.getConnection(DB_URL,"root","abc");
Statement insert1 = conn.createStatement();
insert1.execute("insert into tt (id, create_time) values (1,'2013-07-18 13:44:22.123456')");
PreparedStatement insert2 = conn.prepareStatement(
  "insert into tt (id, create_time) values (?,?)");
insert2.setInt(1, 2);
insert2.setTimestamp(2, new Timestamp(1273017612999L));
insert2.execute();

Output on my DOS-console:

MariaDB [duc]> select * from tt;
+------+----------------------------+
| id   | create_time                |
+------+----------------------------+
|    1 | 2013-07-18 13:44:22.123456 |
|    2 | 2010-05-05 02:00:12.000000 |
+------+----------------------------+
2 rows in set (0.00 sec)

==> Milliseconds are lost for id=2.

Does this look like a bug to you? Or did I do something wrong?

OS: windows7, 64 bit MariaDB Version: 10.0.12 JDBC connector: 1.1.7 (mariadb) OR 5.1.32 (mysql)

I tested the same code on an Oracle database: Milliseconds get inserted.

Thanks for any help...

Update: I reported this to the mariaDB bug database: https://mariadb.atlassian.net/browse/CONJ-107

解决方案

You need to enable "useFractionalSeconds" in the MariaDB JDBC driver. For example, "jdbc:mariadb://127.0.0.1:3306/somedb?useFractionalSeconds=true".

(IMHO this should be enabled by default, I don't know why it's not.)

这篇关于mariadb：jdbc：setTimestamp截断毫秒的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！