1

I have a table in MYSQL in with a primary key id int(11) (auto-incremented). Now I have a program which reads some text file and enters the value in id column. So my table should contains :

id
897413
791783


But what happens is that, I can't find big numbers in my table. Is this because of maximum value that int(11) can hold? Increased int(11) to int(20) still facing same problem. I can't change the datatype to big int as I have already implemented a lot of code.

EDIT: I tried to insert a single record with id as 523826 and it got saved in DB as 450258. Why so?

Sri9911
  • 1,187
  • 16
  • 32
Akshay
  • 2,417
  • 7
  • 25
  • 21
  • `int` and `bigint` are handled the same way SQL wise. If you are using a strongly typed language, you'll have to change your datatype for your variables, but in PHP, you should just be able to change the datatype without any adverse effects on your code. – Andrew Moore Aug 13 '11 at 04:26
  • Whats the maxmimum value that int(11) can hold? – Akshay Aug 13 '11 at 04:30
  • What do you mean by "big numbers"? Bigger than 2147483647 (max for signed `int`)? – bfavaretto Aug 13 '11 at 04:30
  • @all:Please see my latest edit – Akshay Aug 13 '11 at 04:31

2 Answers2

12

Definition from mysql manual for the int data type:

A normal-size integer. The signed range is -2147483648 to 2147483647. The unsigned range is 0 to 4294967295.

The int type is always 4 bytes (32 bits). The 11 in int(11) is just the "display width", that only matters for UNSIGNED ZEROFILL columns. More details on this blog post.

bfavaretto
  • 71,580
  • 16
  • 111
  • 150
  • I tried to insert a record with id=523826 and it got inserted as 450258.How come?Also i have increased the size to int(20) – Akshay Aug 13 '11 at 04:34
  • Could you post the `INSERT` query? – bfavaretto Aug 13 '11 at 04:37
  • 1
    You cannot just increase the size of an INT to 20. The number in parenthesis for integer columns is really only used for zero filled values, as it will pad to that many characters. Otherwise it has absolutely no control of the values that can be inserted. INT will never be able to store values outside of +/-2147483648 or 4294967295 for unsigned. – Percy Aug 13 '11 at 04:39
3

INT in MySQL is 32 bits. INT(11) likely means you have a signed INT, which for an ID is useless. Changing it to an unsigned INT will automatically double the number of IDs available (since nobody uses a negative ID). Even though 11 "seems" bigger, it's because it takes into consideration the "length" of the number if it's the maximum negative number (-2147483648) which is 11 characters long.

BIGINT will let you go up to 64 bits, signed or unsigned. Again, unsigned will allow you twice as many IDs (>0).

As Andrew mentioned above, if your PHP does not support 64 bit integers then you will not be able to easily use them.

Hope that helps.

Percy
  • 1,037
  • 8
  • 7