MySQL Int(11) Number Out of Range

Mysql int(11) number out of range

An int, with MySQL, is stored on 4 bytes, and, as such, can only contain values between -2147483648 and 2147483647.

622108120237 is greater than 2147483647 ; so it doesn't fit into an int -- looks like you are going to have to use a bigint.

See the Datatypes - Numeric types section of

the MySQL manual, about that.

What is the size of column of int(11) in mysql in bytes?

An INT will always be 4 bytes no matter what length is specified.

• TINYINT = 1 byte (8 bit)
• SMALLINT = 2 bytes (16 bit)
• MEDIUMINT = 3 bytes (24 bit)
• INT = 4 bytes (32 bit)
• BIGINT = 8 bytes (64 bit).

The length just specifies how many characters to pad when selecting data with the mysql command line client. 12345 stored as int(3) will still show as 12345, but if it was stored as int(10) it would still display as 12345, but you would have the option to pad the first five digits. For example, if you added ZEROFILL it would display as 0000012345.

... and the maximum value will be 2147483647 (Signed) or 4294967295 (Unsigned)

MySQL Error 1264: out of range value for column

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.

Out of range value for Integer

It's not a bug, it's merely a case of integer overflow.

If you need your number-in-string value to max out, you should add code to do that, either when you do the insert, or as a trigger.

See http://dev.mysql.com/doc/refman/5.0/en/type-conversion.html for more information.

You can also experiment with small selects (untested, I don't have MySQL access here):

SELECT CAST('2123456789012345678' AS INT(11))
SELECT CAST('21234567890123456789' AS INT(11))

int(11) vs. int(anything else)

The x in INT(x) has nothing to do with space requirements or any other performance issues, it's really just the display width. Generally setting the display widths to a reasonable value is mostly useful with the UNSIGNED ZEROFILL option.

//INT(4) UNSIGNED ZEROFILL
0001
0002 
...
0099
...
0999
...
9999
...
10000

//INT(2) UNSIGNED ZEROFILL
01
02 
...
09
...
99
...
100

Without the UNSIGNED ZEROFILL option the value will be left-padded with spaces to the appropriate display width.

//INT(4)
   1
   2 
...
  99
...
 999
...
9999
...
10000

//INT(2)
 1
 2 
...
 9
...
99
...
100

why is phone number not stored in integer datataype?

Because your number is out of range for an int type. You could try using bigint instead, or store the column as a varchar which is likely more appropriate as described here.

See this answer. An int is always stored with 4 bytes. I think you are misinterpreting what the (100) does, it just controls the display width.

More info on numeric types in the docs.

How do I fix the 'Out of range value adjusted for column' error?

The value you were trying to set is too large for a signed INT field. The display width (15) does not affect the range of values that can be stored, only how the value is displayed.

Ref: MySQL Docs on numerics

On phone numbers - see Is it better to store telephone numbers in some canonical format or "as entered"?

---

Related Topics