警告#1264:MySQL中超出范围错误 [英] Warning#1264:out of range error in mysql

问题描述

以下查询:

INSERT INTO skill (`emp_number`, `skill_id`, `year_exp`, `comments`)
VALUES ('4', '3', '23.45', '')

正在产生错误:

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)

请帮助我指出错误.

推荐答案

我相信您遇到此错误是因为year_exp字段是DECIMAL(2,2)，并且您想要DECIMAL(4,2). DECIMAL(2,2)表示精度2的数字，最多2个小数位.但是此数字的精度为4位数字.

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.

此MSDN链接讨论十进制精度.

This link to MSDN talks about the decimal precision.

http://msdn.microsoft.com /en-US/library/ms187746(v=SQL.90).aspx

这是一项具有类似结果的快速测试(在SQL Server 2008中完成，但我认为您使用的是MySQL ...)

Here's a quick test with similar results (done in SQL Server 2008, but I think you're on MySQL...)

1)创建一个带有测试列的表:

1) Created a table with a test column:

CREATE TABLE testtable (testcolumn DECIMAL(2,2))

2)然插入语句...:

2) Ran insert statement... :

INSERT INTO testtable (testcolumn) VALUES (23.45)

...并收到此错误...

... and received this error ...

信息8115，第16级，状态8，第1行
将数字转换为数据类型数字的算术溢出错误.

Msg 8115, Level 16, State 8, Line 1
Arithmetic overflow error converting numeric to data type numeric.

(评论:我最喜欢的错误之一...无法将数字转换为数字..."哈哈)

(COMMENT: one of my fave errors ... "cannot convert number to number ..." ha ha)

3)将列更改为适当的规格

3) Altered column to proper specifications

ALTER TABLE testtable ALTER COLUMN testcolumn DECIMAL(4,2)

4)重新运行相同的插入语句.有用.

4) Re-ran same insert statement. It works.

请告诉我这是否有帮助.

Please let me know if this helps.

这篇关于警告#1264:MySQL中超出范围错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！