Just a quick note on integer type sizing in MySQL since I am coming across some very interesting column definitions in some odd corners of my inherited database schemas.
First, a cute picture to soften everybody up:
We'll start with a question - What is the difference between a column of type INT(8) and a column of type INT(11) or even INT(64) ?
Think about it for a second… Think you know the answer? Scroll down to find out!
The max unsigned value for an INT column is 2^32 -1 regardless of what sits in the parentheses. That is 4294967295. You may notice the number of characters in that is 10 (incidentally, the same for signed).
Now, let's create a quick table and see what happens with it:
CREATE TABLE `myints` (
`a` int(4) unsigned NOT NULL,
`b` int(8) unsigned NOT NULL,
`c` int(10) unsigned NOT NULL,
`d` int(16) unsigned NOT NULL,
`e` int(32) unsigned NOT NULL,
`f` int(64) unsigned NOT NULL,
PRIMARY KEY (`a`)
)
Let's try a nice 4 digit number:
insert into myints values (1024, 1024, 1024, 1024, 1024, 1024);
Query OK, 1 row affected (0.00 sec)
Let's try another one with 8 digits:
insert into myints values (10242048, 10242048, 10242048, 10242048, 10242048, 10242048);
Query OK, 1 row affected (0.00 sec)
Let's try again with 12 digits:
insert into myints values (102420484096, 102420484096, 102420484096, 102420484096, 102420484096, 102420484096);
ERROR 1264 (22003): Out of range value for column 'a' at row 1
Aw, failure… Yes, those are bigger than the max value of a 32 bit integer. Let's back off a bit, maybe just column 'a' didn't like it since it's an int(4), after all:
insert into myints values (4294967295, 4294967295, 4294967295, 4294967295, 4294967295, 4294967295);
Query OK, 1 row affected (0.00 sec)
Max value works, let's try incrementing the 'f' column, remember we said int(64) there, and see:
insert into myints values (4294967295, 4294967295, 4294967295, 4294967295, 4294967295, 4294967296);
ERROR 1264 (22003): Out of range value for column 'f' at row 1
So, doesn't seem to limit what values you can insert. What about when you select values out?
mysql> select * from myints;
+------------+------------+------------+------------+------------+------------+
| a | b | c | d | e | f |
+------------+------------+------------+------------+------------+------------+
| 1024 | 1024 | 1024 | 1024 | 1024 | 1024 |
| 10242048 | 10242048 | 10242048 | 10242048 | 10242048 | 10242048 |
| 4294967295 | 4294967295 | 4294967295 | 4294967295 | 4294967295 | 4294967295 |
+------------+------------+------------+------------+------------+------------+
Nope. Not there either.
So, in the words of Yzma from Emperor's New Groove "Kronk! Why do we even have that lever?!?"
For one and only one particular use. Formatting when you enable the ZEROFILL option on the column. However, a note From the manual:
"If you specify ZEROFILL for a numeric column, MySQL automatically adds the UNSIGNED attribute to the column."
Now, a demonstration.
mysql> alter table myints modify column a int(4) unsigned zerofill not null;
mysql> alter table myints modify column b int(8) unsigned zerofill not null;
mysql> alter table myints modify column c int(10) unsigned zerofill not null;
mysql> alter table myints modify column d int(16) unsigned not null zerofill;
We'll leave 'e' and 'f' alone for now for the sake of sane formatting.
mysql> select * from myints;
+------------+------------+------------+------------------+------------+------------+
| a | b | c | d | e | f |
+------------+------------+------------+------------------+------------+------------+
| 1024 | 00001024 | 0000001024 | 0000000000001024 | 1024 | 1024 |
| 2048 | 00002048 | 0000002048 | 0000000000002048 | 2048 | 2048 |
| 10242048 | 10242048 | 0010242048 | 0000000010242048 | 10242048 | 10242048 |
| 4294967295 | 4294967295 | 4294967295 | 0000004294967295 | 4294967295 | 4294967295 |
+------------+------------+------------+------------------+------------+------------+
That's what ZEROFILL does. That's the only actual use for the number inside the parentheses with an integer type in MySQL. Without ZEROFILL it is effectively meaningless. You can either leave it off or just put it at whatever you like.
Please feel free to read more details in the documentation.
So, did you guess right? If so the next picture is for you:
