Types in MySQL: BigInt(20) vs Int(20)

Question

See http://dev.mysql.com/doc/refman/8.0/en/numeric-types.html

• INT is a four-byte signed integer.

• BIGINT is an eight-byte signed integer.

They each accept no more and no fewer values than can be stored in their respective number of bytes. That means 2³² values in an INT and 2⁶⁴ values in a BIGINT.

The 20 in INT(20) and BIGINT(20) means almost nothing. It's a hint for display width. It has nothing to do with storage, nor the range of values that column will accept.

Practically, it affects only the ZEROFILL option:

CREATE TABLE foo ( bar INT(20) ZEROFILL );
INSERT INTO foo (bar) VALUES (1234);
SELECT bar from foo;

+----------------------+
| bar                  |
+----------------------+
| 00000000000000001234 |
+----------------------+

It's a common source of confusion for MySQL users to see INT(20) and assume it's a size limit, something analogous to CHAR(20). This is not the case.

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.

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.

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)