警告#1264：mysql 中的超出范围错误
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14216642/
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
Warning#1264:out of range error in mysql
提问by Misty
The following query:
以下查询：
INSERT INTO skill (`emp_number`, `skill_id`, `year_exp`, `comments`)
VALUES ('4', '3', '23.45', '')
It is producing the error :
它正在产生错误：
1 row inserted.
Warning: #1264 Out of range value for column 'year_exp' at row 1
year_exp column is of datatype decimal(2,2)
Please help me to pint out the error.
请帮我找出错误。
回答by user1958225
I believe you're having this error because the year_expfield is DECIMAL(2,2), and you want DECIMAL(4,2). DECIMAL(2,2)means a number of precision 2, with up to 2 decimal places. But this number has 4 digits of precision.
我相信您遇到此错误是因为该year_exp字段是DECIMAL(2,2)，而您想要DECIMAL(4,2). DECIMAL(2,2)表示精度为 2 的数，最多保留 2 位小数。但是这个数字有 4 位精度。
This link to MSDN talks about the decimal precision.
这个指向 MSDN 的链接讨论了十进制精度。
http://msdn.microsoft.com/en-US/library/ms187746(v=SQL.90).aspx
http://msdn.microsoft.com/en-US/library/ms187746(v=SQL.90).aspx
Here's a quick test with similar results (done in SQL Server 2008, but I think you're on MySQL...)
这是一个具有类似结果的快速测试（在 SQL Server 2008 中完成，但我认为您使用的是 MySQL...）
1) Created a table with a test column:
1）创建一个带有测试列的表：
CREATE TABLE testtable (testcolumn DECIMAL(2,2))
2) Ran insert statement... :
2) 运行插入语句...：
INSERT INTO testtable (testcolumn) VALUES (23.45)
... and received this error ...
...并收到此错误...
Msg 8115, Level 16, State 8, Line 1
Arithmetic overflow error converting numeric to data type numeric.
消息 8115，级别 16，状态 8，第 1 行
将数字转换为数据类型数字的算术溢出错误。
(COMMENT: one of my fave errors ... "cannot convert number to number ..." ha ha)
（评论：我最喜欢的错误之一......“无法将数字转换为数字......”哈哈）
3) Altered column to proper specifications
3) 将色谱柱改为适当的规格
ALTER TABLE testtable ALTER COLUMN testcolumn DECIMAL(4,2)
4) Re-ran same insert statement. It works.
4) 重新运行相同的插入语句。有用。
Please let me know if this helps.
请让我知道这可不可以帮你。
回答by long
Change fieldtype to decimal(4,2). Details: https://dev.mysql.com/doc/refman/5.7/en/precision-math-decimal-characteristics.html
将字段类型更改为decimal(4,2). 详情：https: //dev.mysql.com/doc/refman/5.7/en/precision-math-decimal-characteristics.html
The declaration syntax for a DECIMAL column is DECIMAL(M,D). The ranges of values for the arguments in MySQL 5.7 are as follows:
M is the maximum number of digits (the precision). It has a range of 1 to 65.
D is the number of digits to the right of the decimal point (the scale). It has a range of 0 to 30 and must be no larger than M.
DECIMAL 列的声明语法是 DECIMAL(M,D)。MySQL 5.7 中参数的取值范围如下：
M 是最大位数（精度）。它的范围是 1 到 65。
D 是小数点右边的位数（刻度）。它的范围是 0 到 30，并且不能大于 M。
