Data type: what is an int(11)?

Over and over I see developers that don’t understand what int(11) really means. Their confusion is understandable. Many know what defining a char(10) means (a fixed-sized character string that allows up to 10 characters). However, ints are different.

First of all, there are 5 types of integer. They are all fixed size.
Type # of bytes
tinyint 1
smallint 2
mediumint 3
int 4
bigint 8

As you can see from the chart, an int is always 4 bytes. That can store signed numbers from -2 billion to +2 billion (and unsigned numbers 0 to 4B). So, what does it mean if you declare an int(5)? It does not restrict the number of digits to 5… It may actually do nothing! The (5) part is a display width. It’s only used if you use UNSIGNED and ZEROFILL with an integer type. Then the display of those numbers will be zero-padded on the left to 5 digits if they contain less than 5 digits. Example:

CREATE TABLE `foo` (
`bar` int(5) unsigned zerofill DEFAULT NULL
)

SELECT * FROM foo;
+———+

| bar |

+———+

| 00042 |

| 00101 |

| 9876543 |

+———+

Author:Sarah Sproehnle

Advertisements

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

%d bloggers like this: