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

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 232 values in an INT and 264 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:

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.

Leave a Comment