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

by

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:

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.

More Related Contents:

  1. What is the size of column of int(11) in mysql in bytes?
  2. What column type/length should I use for storing a Bcrypt hashed password in a Database?
  3. What data type to use for hashed password field and what length?
  4. What is the benefit of zerofill in MySQL?
  5. What MySQL data type should be used for Latitude/Longitude with 8 decimal places?
  6. mysql equivalent data types
  7. How do you use MYSQL in ColdFusion? [closed]
  8. Using backticks around field names
  9. Possible to do a MySQL foreign key to one of two possible tables?
  10. MySQL error: key specification without a key length
  11. Mysql query to dynamically convert rows to columns
  12. Copy/duplicate database without using mysqldump
  13. Suppress warning messages using mysql from within Terminal, but password written in bash script
  14. MySQL variable format for a “NOT IN” list of values
  15. Convert partially non-numeric text into number in MySQL query
  16. MYSQL ORDER BY CASE Issue
  17. How to get database structure in MySQL via query
  18. Can’t use a MySQL connection for entity framework 6
  19. MySQL JOIN with LIMIT 1 on joined table
  20. MySQL Select last 7 days
  21. MySQL triggers cannot update rows in same table the trigger is assigned to. Suggested workaround?
  22. MySQL view performance
  23. Does MySQL Regexp support Unicode matching
  24. mysqld_safe Directory ‘/var/run/mysqld’ for UNIX socket file don’t exists
  25. Remove DEFINER clause from MySQL Dumps
  26. How to retrieve JSON data from MySQL?
  27. Is incrementing a field in MySQL atomic?
  28. Delete from one table with join
  29. Encryption at rest and/or AES_ENCRYPT
  30. MySQL: Compare BETWEEN time

Leave a Comment