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

One thought on “MySql Data type confusion – int(11) – what it is?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s