MySQL 错误1264: 列值超出范围

正如我在 MySQL 中的表中的 SET cust _ fax 一样:

cust_fax integer(10) NOT NULL,

然后像这样插入值:

INSERT INTO database values ('3172978990');

然后它说

“错误1264”超出列值

我想知道错误出在哪里? 我的片场? 还是其他地方？

任何回答都将不胜感激！

476882 次浏览
小开
最佳答案

The value 3172978990 is greater than 2147483647 – the maximum value for INT – hence the error. MySQL integer types and their ranges are listed here.

Also note that the (10) in INT(10) does not define the "size" of an integer. It specifies the display width of the column. This information is advisory only.

To fix the error, change your datatype to VARCHAR. Phone and Fax numbers should be stored as strings. See this discussion.

小开

You are exceeding the length of int datatype. You can use UNSIGNED attribute to support that value.

SIGNED INT can support till 2147483647 and with UNSIGNED INT allows double than this. After this you still want to save data than use CHAR or VARCHAR with length 10

小开

You can also change the data type to bigInt and it will solve your problem, it's not a good practice to keep integers as strings unless needed. :)

ALTER TABLE T_PERSON MODIFY mobile_no BIGINT;
小开

tl;dr

Make sure your AUTO_INCREMENT is not out of range. In that case, set a new value for it with:

ALTER TABLE table_name AUTO_INCREMENT=100 -- Change 100 to the desired number

Explanation

AUTO_INCREMENT can contain a number that is bigger than the maximum value allowed by the datatype. This can happen if you filled up a table that you emptied afterward but the AUTO_INCREMENT stayed the same, but there might be different reasons as well. In this case a new entry's id would be out of range.

Solution

If this is the cause of your problem, you can fix it by setting AUTO_INCREMENT to one bigger than the latest row's id. So if your latest row's id is 100 then:

ALTER TABLE table_name AUTO_INCREMENT=101

If you would like to check AUTO_INCREMENT's current value, use this command: 

SELECT `AUTO_INCREMENT`
FROM  INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'DatabaseName'
AND   TABLE_NAME   = 'TableName';
小开

Work with:

ALTER TABLE `table` CHANGE `cust_fax` `cust_fax` VARCHAR(60) NULL DEFAULT NULL;
小开

Just table id column name delete and new id created with autoincrement then worked successfully.


提交答案