MysqlDataTruncation:数据截断:第1行的列'column'值超出范围

I am using java and MySQL. I am trying to insert a value into a table.

我正在使用java和MySQL。我试图向表中插入一个值。

When I insert, I get this exception:

当我插入时，我得到这个异常:

com.mysql.jdbc.MysqlDataTruncation: Data truncation: Out of 
range value for column 'idPxxx' at row 1

Only 5% of inserts produces this exception, others work. What does the error mean and how do I prevent it?

只有5%的插入会产生这个异常，其他的会起作用。这个错误是什么意思，我如何预防它?

3 个解决方案

#1

It means that the data you are storing in idPxxx doesn't fit. For example a string might be too long, or a number too large.

这意味着在idPxxx中存储的数据不适合。例如，一个字符串可能太长，或者一个数字太大。

What is the datatype of idPxxx? And what are you trying to store in it?

idPxxx的数据类型是什么?你想把什么储存在里面?

#2

How to produce this Exception on the MySQL console:

如何在MySQL控制台上产生此异常:

mysql> create table penguin (mydecimal decimal(9,8));
Query OK, 0 rows affected (0.02 sec)

mysql> insert into penguin values (1234.1234);
Query OK, 1 row affected, 1 warning (0.01 sec)

mysql> show warnings;
+---------+------+----------------------------------------------------+
| Level   | Code | Message                                            |
+---------+------+----------------------------------------------------+
| Warning | 1264 | Out of range value for column 'mydecimal' at row 1 |
+---------+------+----------------------------------------------------+
1 row in set (0.00 sec)

mysql> select * from penguin;
+------------+
| mydecimal  |
+------------+
| 9.99999999 |
+------------+
1 row in set (0.00 sec)

You tried to cram 1234 into a column that could take maximum 9.9 repeated. Notice the row is still inserted.

你试着把1234塞进一个最多可以重复9.9次的列里。注意行仍然被插入。

You can get the MySQL console to prevent this conversion using strict mode:

您可以使用严格的模式来获得MySQL控制台，以防止这种转换:

mysql> set sql_mode=STRICT_ALL_TABLES;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into penguin values (5678.5678);
ERROR 1264 (22003): Out of range value for column 'mydecimal' at row 1

The insert command failed in attempting add a second row to penguin:

insert命令在尝试向penguin添加第二行时失败:

mysql> select * from penguin;
+------------+
| mydecimal  |
+------------+
| 9.99999999 |
+------------+
1 row in set (0.00 sec)

Solutions

解决方案

Expand the size of the datatype in your column to accept the value you put in there.
展开列中的数据类型的大小，以接受在其中输入的值。
Shrink the size of the value that you are trying to cram into a small data type.
缩小您试图填入小数据类型的值的大小。

#3

To me the problem was of unsigned datatype, removing the flag has fixed my problem.

对我来说，问题是无符号数据类型，移除标志解决了我的问题。

#1