MySql Data type confusion – int(11) – what it is?

images

I found many people are confused with MySql numeric size value, as people are thinking this is size of numbers that can be stored, like if tinyint(2) – means upto 2 digit numbers can be saved, which is misconception as size doesn’t have any impact on how many digit it can store. Size¬†is only for display, and this only matters if you use the ZEROFILL option.

To make this clear,

MySql has following numeric data types:

Type Storage Minimum Value Maximum Value
(Bytes) (Signed/Unsigned) (Signed/Unsigned)
TINYINT 1 -128 127
0 255
SMALLINT 2 -32768 32767
0 65535
MEDIUMINT 3 -8388608 8388607
0 16777215
INT 4 -2147483648 2147483647
0 4294967295
BIGINT 8 -9223372036854775808 9223372036854775807
0 18446744073709551615

As per above chart, numeric data type like INT is always 4 bytes. So making int(5) doesn’t help on saving disk space.

Lets consider the table with following structure:

CREATE TABLE foo (
  age INT(3) ZEROFILL, 
  currency INT(6) ZEROFILL, 
  distance INT(11) ZEROFILL
);
INSERT INTO foo (age, currency, distance) VALUES (222, 555, 777);
INSERT INTO foo (age, currency, distance) VALUES(1,666666,88888888888);

SELECT * FROM foo;

+------+----------+-------------+
| age  | currency | distance    |
+------+----------+-------------+
|  222 | 000555   | 00000000777 |
|  001 | 666666   | 88888888888 |
+------+----------+-------------+

In above example, ‘000555’ has three extra zero, as ¬†column `currency` is INT(6) zero fill, so less than six digit will be prepended by zeros.

Advertisements