[mysql] Types in MySQL: BigInt(20) vs Int(20)

I was wondering what the difference between BigInt, MediumInt, and Int are... it would seem obvious that they would allow for larger numbers; however, I can make an Int(20) or a BigInt(20) and that would make seem that it is not necessarily about size.

Some insight would be awesome, just kind of curious. I have been using MySQL for a while and trying to apply business needs when choosing types, but I never understood this aspect.

As far as I know, there is only one small difference is when you are trying to insert value which is out of range.

In examples I'll use 401421228216, which is 101110101110110100100011101100010111000 (length 39 characters)

• If you have INT(20) for system this means allocate in memory minimum 20 bits. But if you'll insert value that bigger than 2^20, it will be stored successfully, only if it's less then INT(32) -> 2147483647 (or 2 * INT(32) -> 4294967295 for UNSIGNED)

Example:

mysql> describe `test`;
+-------+------------------+------+-----+---------+-------+
| Field | Type             | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| id    | int(20) unsigned | YES  |     | NULL    |       |
+-------+------------------+------+-----+---------+-------+
1 row in set (0,00 sec)

mysql> INSERT INTO `test` (`id`) VALUES (401421228216);
ERROR 1264 (22003): Out of range value for column 'id' at row 1

mysql> SET sql_mode = '';
Query OK, 0 rows affected, 1 warning (0,00 sec)

mysql> INSERT INTO `test` (`id`) VALUES (401421228216);
Query OK, 1 row affected, 1 warning (0,06 sec)

mysql> SELECT * FROM `test`;
+------------+
| id         |
+------------+
| 4294967295 |
+------------+
1 row in set (0,00 sec)

• If you have BIGINT(20) for system this means allocate in memory minimum 20 bits. But if you'll insert value that bigger than 2^20, it will be stored successfully, if it's less then BIGINT(64) -> 9223372036854775807 (or 2 * BIGINT(64) -> 18446744073709551615 for UNSIGNED)

Example:

mysql> describe `test`;
+-------+---------------------+------+-----+---------+-------+
| Field | Type                | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+-------+
| id    | bigint(20) unsigned | YES  |     | NULL    |       |
+-------+---------------------+------+-----+---------+-------+
1 row in set (0,00 sec)

mysql> INSERT INTO `test` (`id`) VALUES (401421228216);
Query OK, 1 row affected (0,04 sec)

mysql> SELECT * FROM `test`;
+--------------+
| id           |
+--------------+
| 401421228216 |
+--------------+
1 row in set (0,00 sec)

Let's give an example for int(10) one with zerofill keyword, one not, the table likes that:

create table tb_test_int_type(
    int_10 int(10),
    int_10_with_zf int(10) zerofill,
    unit int unsigned
);

Let's insert some data:

insert into tb_test_int_type(int_10, int_10_with_zf, unit)
values (123456, 123456,3147483647), (123456, 4294967291,3147483647) 
;

Then

select * from tb_test_int_type; 

# int_10, int_10_with_zf, unit
'123456', '0000123456', '3147483647'
'123456', '4294967291', '3147483647'

We can see that

• with keyword zerofill, num less than 10 will fill 0, but without zerofill it won't

• Secondly with keyword zerofill, int_10_with_zf becomes unsigned int type, if you insert a minus you will get error Out of range value for column...... But you can insert minus to int_10. Also if you insert 4294967291 to int_10 you will get error Out of range value for column.....

Conclusion:

1. int(X) without keyword zerofill, is equal to int range -2147483648~2147483647

2. int(X) with keyword zerofill, the field is equal to unsigned int range 0~4294967295, if num's length is less than X it will fill 0 to the left

The number in parentheses in a type declaration is display width, which is unrelated to the range of values that can be stored in a data type. Just because you can declare Int(20) does not mean you can store values up to 10^20 in it:

[...] This optional display width may be used by applications to display integer values having a width less than the width specified for the column by left-padding them with spaces. ...

The display width does not constrain the range of values that can be stored in the column, nor the number of digits that are displayed for values having a width exceeding that specified for the column. For example, a column specified as SMALLINT(3) has the usual SMALLINT range of -32768 to 32767, and values outside the range allowed by three characters are displayed using more than three characters.

For a list of the maximum and minimum values that can be stored in each MySQL datatype, see here.

I wanted to add one more point is, if you are storing a really large number like 902054990011312 then one can easily see the difference of INT(20) and BIGINT(20). It is advisable to store in BIGINT.

Quote:

The "BIGINT(20)" specification isn't a digit limit. It just means that when the data is displayed, if it uses less than 20 digits it will be left-padded with zeros. 2^64 is the hard limit for the BIGINT type, and has 20 digits itself, hence BIGINT(20) just means everything less than 10^20 will be left-padded with spaces on display.