警告＃1264：mysql超出范围错误

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.

请帮我解决错误。

2 个解决方案

#1

I believe you're having this error because the year_exp field 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

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）Ran插入声明......：

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）重新运行相同的insert语句。有用。

Please let me know if this helps.

请让我知道这可不可以帮你。

#2

Change fieldtype to decimal(4,2). Details: https://dev.mysql.com/doc/refman/5.7/en/precision-math-decimal-characteristics.html

将fieldtype更改为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:

DECIMAL列的声明语法是DECIMAL（M，D）。 MySQL 5.7中参数的值范围如下：

M is the maximum number of digits (the precision). It has a range of 1 to 65.

M是最大位数（精度）。它的范围是1到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.

D是小数点右边的位数（刻度）。它的范围为0到30，且不得大于M.

#1