Numeric data types
Integer types
| Type | Storage (Bytes) | Minimum Value Signed | Minimum Value Unsigned | Maximum Value Signed | Maximum Value Unsigned |
|---|---|---|---|---|---|
| TINYINT | 1 | -128 | 0 | 127 | 255 |
| SMALLINT | 2 | -32768 | 0 | 32767 | 65535 |
| MEDIUMINT | 3 | -8388608 | 0 | 8388607 | 16777215 |
| INT | 4 | -2147483648 | 0 | 2147483647 | 4294967295 |
| BIGINT | 8 | -2^63 | 0 | 2^63-1 | 2^64-1 |
INT and INTEGER can be used interchangeably.
CREATE TABLE employees(id INT(255));
The maximum value that an INTEGER column can store is either 2147483647 (in case of a signed INTEGER) or 4294967295 (in case of an unsigned INTEGER). 255 here defines the visible length of a number. On the one handed, it is impractical to display a number 255 digits long. On the other hand, INTEGER supports 10 digit numbers as a maximum value. So, it will be converted to INT(11). The extra one digit is kept for storing the sign.
ZEROFILL is an attribute which indicates that the number value should be prefixed with zeros if the length of the number value is smaller than the length of the column.
Fixed point types
Fixed point types represent numbers with a fixed number of digits after the decimal or radix point. MySQL has DECIMAL and NUMERIC as fixed point, or exact, value data types. These values are stored in a binary format.