java - 陈述 。 executeUpdate 给出第 1 行 X 列截断的日期错误

Question

我正在开发一个试图通过 tsv infile 将数据插入 mysql 数据库的 java 项目。运行该程序时出现以下错误:

Exception in thread "main" java.sql.SQLException: Data truncated for column 'Services' at row 1 at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:946) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2985) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1631) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1723) at com.mysql.jdbc.Connection.execSQL(Connection.java:3277) at com.mysql.jdbc.Statement.executeUpdate(Statement.java:1402) at com.mysql.jdbc.Statement.executeUpdate(Statement.java:1317) at LogProcessing.LogReader.addDay(LogReader.java:313) at LogProcessing.LogReader.updateTables(LogReader.java:177) at LogProcessing.LogReader.start(LogReader.java:59) at LogProcessing.LogReader.main(LogReader.java:45)

代码是

s.executeUpdate(query);

查询在哪里

LOAD DATA INFILE 'C:/temp/BA5770/tmp/temp.dat'INTO TABLE thisweek FIELDS TERMINATED BY '    ';

奇怪的是，当我复制这个查询并直接输入到mysql中时，它被接受了。

mysql> LOAD DATA INFILE 'C:/temp/BA5770/tmp/temp.dat'INTO TABLE thisweek FIELDS TERMINATED BY ' '; Query OK, 76932 rows affected, 65535 warnings (2.44 sec) Records: 76932 Deleted: 0 Skipped: 0 Warnings: 76932

表格看起来像:

mysql> show columns from thisweek;
+------------+----------------------+------+-----+---------+-------+
| Field      | Type                 | Null | Key | Default | Extra |
+------------+----------------------+------+-----+---------+-------+
| functionID | smallint(5) unsigned | YES  | MUL | NULL    |       |
| serverID   | tinyint(3) unsigned  | YES  |     | NULL    |       |
| date       | date                 | YES  |     | NULL    |       |
| time       | time                 | YES  |     | NULL    |       |
| calls      | int(10) unsigned     | YES  |     | NULL    |       |
| meanTime   | float                | YES  |     | NULL    |       |
| WSC        | float                | YES  |     | NULL    |       |
| THM_DB2    | float                | YES  |     | NULL    |       |
| DEP        | float                | YES  |     | NULL    |       |
| Tandem     | float                | YES  |     | NULL    |       |
| LDAP       | float                | YES  |     | NULL    |       |
| PS         | float                | YES  |     | NULL    |       |
| IAV        | float                | YES  |     | NULL    |       |
| IMS        | float                | YES  |     | NULL    |       |
| Services   | float                | YES  |     | NULL    |       |
+------------+----------------------+------+-----+---------+-------+
15 rows in set (0.00 sec)

输入数据如下:

31  0   20111207    235900  19  0.0 -1.0    -1.0    -1.0    -1.0    -1.0    -1.0    -1.0    -1.0    -1.0
32  0   20111207    235900  405 60.94   0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
33  0   20111207    235900  1   32.0    24.0    14.0    0.0 0.0 0.0 0.0 8.0 0.0 24.0
34  0   20111207    235900  3   39.0    36.0    21.0    6.0 0.0 0.0 0.0 8.0 0.0 32.0
35  0   20111207    235900  27  9.44    -1.0    -1.0    -1.0    -1.0    -1.0    -1.0    -1.0    -1.0    -1.0

Answer 1

你怎么能说查询在mysql控制台被接受？
查看执行状态语句:

Query OK, 76932 rows affected, 65535 warnings (2.44 sec)
Records: 76932 Deleted: 0 Skipped: 0 Warnings: 76932

对于 76932 记录，生成的警告是 76932，这意味着每条记录都有一些可调整的错误。
在load data ...查询执行和
后在mysql控制台执行show warnings也许您会看到大多数 ... data truncated ... 警告消息。

在 MySQL 中使用 float 和 double 有问题。
解决方法是:

1. 在 float 和 double 上使用 decimal 或 real 数据类型。
2. 在数据文件中使用正确的 float 精度。但这对于大型数据文件来说是乏味的。

在以下位置报告了错误和解决方法:

1. In floating point have the same problem with FLOAT and DOUBLE. REALand DECIMAL work fine.
2. I don't know What happenig with Float and Double, but my shot isthe ROUND made by Float... With DECIMAL will work fine.
3. FLOAT and DOUBLE mess up with data probaly by they roundprocedures.