java MySQLDataTruncation：列值超出范围
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12057695/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQLDataTruncation: Out of range value for column
提问by David
I am inserting a row using ibatis and a java webapp. The POJO has a field which needs to store a number (like 3.0 or 2.34). I have tried using BigDecimal and Double on the java side. On the MySQL side, I am using a Decimal(5, 5) data type.
我正在使用 ibatis 和 java webapp 插入一行。POJO 有一个需要存储数字的字段（如 3.0 或 2.34）。我曾尝试在 Java 端使用 BigDecimal 和 Double。在 MySQL 方面，我使用的是 Decimal(5, 5) 数据类型。
When I try to insert a row which has "4" as the value for this numeric field, MySQL and iBatis throw the following exception:
当我尝试插入具有“4”作为此数字字段值的行时，MySQL 和 iBatis 抛出以下异常：
org.springframework.jdbc.UncategorizedSQLException: SqlMapClient operation: encountered SQLException [
--- The error occurred in org/mySQL.xml.
--- The error occurred while applying a result map.
--- Check the mySQL.insertQuery.
--- The error happened while setting a property on the result object.
--- Cause: java.lang.RuntimeException: org.springframework.jdbc.UncategorizedSQLException: SqlMapClient operation: encountered SQLException [
--- The error occurred while applying a parameter map.
--- Check the insertQuery-InlineParameterMap.
--- Check the statement (update failed).
--- Cause: com.mysql.jdbc.MysqlDataTruncation: Data truncation: Out of range value for column 'numericColumn' at row 316]; nested exception is com.ibatis.common.jdbc.exception.NestedSQLException:
at com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQueryWithCallback(GeneralStatement.java:188)
at com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQueryWithRowHandler(GeneralStatement.java:133)
at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryWithRowHandler(SqlMapExecutorDelegate.java:649)
at com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.queryWithRowHandler(SqlMapSessionImpl.java:156)
at com.ibatis.sqlmap.engine.impl.SqlMapClientImpl.queryWithRowHandler(SqlMapClientImpl.java:133)
at org.springframework.orm.ibatis.SqlMapClientTemplate.doInSqlMapClient(SqlMapClientTemplate.java:267)
at org.springframework.orm.ibatis.SqlMapClientTemplate.execute(SqlMapClientTemplate.java:165)
at org.springframework.orm.ibatis.SqlMapClientTemplate.queryWithRowHandler(SqlMapClientTemplate.java:265)
at org.myClass(myClass.java:83)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:324)
at org.springframework.util.MethodInvoker.invoke(MethodInvoker.java:248)
at org.springframework.scheduling.quartz.MethodInvokingJobDetailFactoryBean$MethodInvokingJob.executeInternal(MethodInvokingJobDetailFactoryBean.java:165)
at org.springframework.scheduling.quartz.QuartzJobBean.execute(QuartzJobBean.java:66)
at org.quartz.core.JobRunShell.run(JobRunShell.java:191)
at org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java:516)
Am I using the wrong java data type for the value to be inserted? If not, why won't MySQL let me insert the value?
我是否为要插入的值使用了错误的 java 数据类型？如果没有，为什么 MySQL 不让我插入值？
回答by Clockwork-Muse
You appear to have misunderstood how defining a DECIMAL(or potentially NUMERIC) column works in SQL -
Specifically, when defining the column as DECIMAL(x, y), xis the totalnumber of digits the column will store, and yis the number of digits afterthe decimal point. So, in your column definition, you specified a range of -1 < column < 1. The datatype is correct, but you're attempting to specify a value outside the range the column was told to allow.
Fortunately, the fix is simple: change the column definition. I'm assuming you want to have up to 5 digits beforethe decimal point, which would make the column definition DECIMAL(10, 5).
您似乎误解了在 SQL 中定义DECIMAL（或潜在NUMERIC）列的工作原理 -
具体而言，当将列定义为 时DECIMAL(x, y)，x是列将存储的总位数，y是小数点后的位数。因此，在您的列定义中，您指定了-1 < column < 1 的范围。数据类型是正确的，但您试图指定列被告知允许的范围之外的值。幸运的是，修复方法很简单：更改列定义。我假设您希望小数点前最多有 5 位数字，这将使列定义DECIMAL(10, 5).
