121

As I SET cust_fax in a table in MySQL like this:

cust_fax integer(10) NOT NULL,

and then I insert value like this:

INSERT INTO database values ('3172978990');

but then it say

`error 1264` out of value for column

And I want to know where the error is? My set? Or other?

Any answer will be appreciated!

yunzen
  • 32,854
  • 11
  • 73
  • 106
Cin
  • 1,343
  • 2
  • 9
  • 9
  • 1
    you could check this issue http://stackoverflow.com/questions/6621530/1264-out-of-range-value-fix to solve your problem. – Lucas Maus Jan 11 '13 at 18:42
  • It's good practice to specify numbers and numbers and strings as strings. `'3172978990'` is a string, `3172978990` is a number. Btw. the `10` in `int(10)` does ***not*** define any data type constraint. It is only a client display *hint*. –  Jan 11 '13 at 18:48
  • possible duplicate of [How do I fix the 'Out of range value adjusted for column' error?](http://stackoverflow.com/questions/1786509/how-do-i-fix-the-out-of-range-value-adjusted-for-column-error) – Brad Werth Sep 16 '14 at 16:24

6 Answers6

205

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.

Salman A
  • 262,204
  • 82
  • 430
  • 521
  • For me, the error was caused by `AUTO_INCREMENT` somewhy becoming a huge number in a table with a small row count. So I needed `ALTER TABLE table_name AUTO_INCREMENT=103;` (if there are 102 rows at the moment). – totymedli Mar 16 '19 at 07:56
  • 1
    @totymedli this could make a useful answer for someone experiencing same symptoms but different problem. – Salman A Mar 16 '19 at 18:26
  • I use `int` for phone numbers, because I sanitize user input by removing all non-numbers. – Reed May 30 '20 at 04:09
  • 1
    @Reed, I wouldn't recommend using `int` for phone numbers. If someone would input an international format as 0044.... you will loose 00. Sanitize phone number in your code before sending it to the query. – Tipul07 Jul 28 '20 at 09:11
  • 1
    @Tipul07 You're totally right. `VARCHAR` it is, then. I dev in the u.s. & most stuff I make is local to my hometown, BUT i still should code with accessibility in mind from day one. – Reed Jul 28 '20 at 20:08
  • int is perfectly suitable if your application is only intended for local phone numbers or if you are going to add the prefix separately. But i usually use numeric formats when i expect to compute such number, but phone numbers arent mixed with other numbers, they are just read. But int is a lighter format so up to you – Mbotet Jan 24 '23 at 09:19
48

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;
Giacomo1968
  • 25,759
  • 11
  • 71
  • 103
Rohit Kolhekar
  • 491
  • 4
  • 6
  • 1
    If the application is storing a phone number, then using strings is certainly preferred. However, thank you for the ALTER statement. It fixed my problem (plain integer overflow where I was storing file sizes using INTEGER). – Curt Aug 14 '17 at 18:52
  • This worked for me ALTER TABLE tbl_name MODIFY tbl_column BIGINT(50) UNSIGNED NOT NULL – Winnipass Jun 06 '19 at 10:43
  • 1
    Upvoted! Other suggestions to convert to strings are nonsense. Integers are integers for a reason. – Whir Jul 14 '19 at 18:17
  • "@Reed I wouldn't recommend using int for phone numbers. If someone would input an international format as 0044.... you will loose 00." - Another comment on this page. I'm going with `VARCHAR` – Reed Jul 28 '20 at 20:10
14

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

Saharsh Shah
  • 28,687
  • 8
  • 48
  • 83
11

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';
totymedli
  • 29,531
  • 22
  • 131
  • 165
3

Work with:

ALTER TABLE `table` CHANGE `cust_fax` `cust_fax` VARCHAR(60) NULL DEFAULT NULL;
jorge adrian rodriguez
  • 131
  • 1
  • 1
0

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

Khairul Islam Tonmoy
  • 177
  • 3
  • 8
  • This does not really answer the question. If you have a different question, you can ask it by clicking [Ask Question](https://stackoverflow.com/questions/ask). To get notified when this question gets new answers, you can [follow this question](https://meta.stackexchange.com/q/345661). Once you have enough [reputation](https://stackoverflow.com/help/whats-reputation), you can also [add a bounty](https://stackoverflow.com/help/privileges/set-bounties) to draw more attention to this question. - [From Review](/review/late-answers/31500379) – Tio Apr 12 '22 at 20:41