How To Tutorials

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:

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.