MySQL | TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT
Start your free 7-days trial now!
There are a total of 5 integer types offered by MySQL. The following table summarizes them:
Type | Storage (Bytes) | Min Value Signed | Min Value Unsigned | Max Value Signed | Max Value Unsigned |
|---|---|---|---|---|---|
| 1 | -128 | 0 | 127 | 255 |
| 2 | -32768 | 0 | 32767 | 65535 |
| 3 | -8388608 | 0 | 8388607 | 16777215 |
| 4 | -2147483648 | 0 | 2147483647 | 4294967295 |
| 8 | - $2^{63}$ | 0 | $2^{63}$ - 1 | $2^{64}$ - 1 |
For an explanation on Signed and Unsigned refer here.
Specifying Length
Note that we can also specify the number of digits that we want to display by using the notation INT(M) where M is the length that you want to display.
You should set the length only when you are using the ZEROFILL option as well. Otherwise, specifying the length is pointless.
INT(4) ZEROFILLwith the stored value of42will show0042INT(4)with the stored value of42will show42INTwith the stored value of32will show32
Note that no matter what length you specify, the storage space would still remain the same.
Examples
Consider the following table about some students:
student_id | fname | lname | day_enrolled | age | username |
|---|---|---|---|---|---|
1 | Sky | Towner | 2015-12-03 | 17 | stowner1 |
2 | Ben | Davis | 2016-04-20 | 19 | bdavis2 |
3 | Travis | Apple | 2018-08-14 | 18 | tapple3 |
4 | Arthur | David | 2016-04-01 | 16 | adavid4 |
5 | Benjamin | Town | 2014-01-01 | 17 | btown5 |
The above sample table can be created using the code here.
To specify the length of column age as INT(5) with ZEROFILL:
ALTER TABLE students MODIFY age INT(5) ZEROFILL;
Query OK, 5 rows affected, 2 warnings (0.03 sec)Records: 5 Duplicates: 0 Warnings: 2
To check the length for column has been updated:
DESCRIBE students;
+--------------+--------------------------+------+-----+---------+----------------+| Field | Type | Null | Key | Default | Extra |+--------------+--------------------------+------+-----+---------+----------------+| student_id | int unsigned | NO | PRI | NULL | auto_increment || fname | varchar(30) | YES | | NULL | || lname | varchar(30) | YES | | NULL | || day_enrolled | date | YES | | NULL | || age | int(5) unsigned zerofill | YES | | NULL | || username | varchar(15) | YES | | NULL | |+--------------+--------------------------+------+-----+---------+----------------+
To check how age is displayed in students table now:
SELECT * FROM students;
+------------+----------+--------+--------------+-------+----------+| student_id | fname | lname | day_enrolled | age | username |+------------+----------+--------+--------------+-------+----------+| 1 | Sky | Towner | 2015-12-03 | 00017 | stowner1 || 2 | Ben | Davis | 2016-04-20 | 00019 | bdavis2 || 3 | Travis | Apple | 2018-08-14 | 00018 | tapple3 || 4 | Arthur | David | 2016-04-01 | 00016 | adavid4 || 5 | Benjamin | Town | 2014-01-01 | 00017 | btown5 |+------------+----------+--------+--------------+-------+----------+
Note that age values are now left-padded with zeros to 5 digits.
