Difference between MySQL INT(1) or INT(10) .. etc
I know im not alone in thinking that it affected the size of the data field. An unsigned int has the max value of 4294967295 no matter if its INT(1) or int(10) and will use 4 bytes of data.
So, what does the number in the brackets mean?
It pretty much comes down to display, its called the display-width. The display width is a number from 1 to 255. You can set the display width if you want all of your integer values to “appear”. If you enable zerofill on the row, the field will have a default value of 0 for int(1) and 0000000000 for int(10).
There are 5 main numeric data types, and you should choose each one on its own merits. Based on the data you expect (or in some cases hope) to hold, you should use the correct data type. If you dont ever expect to use a value of above 127 and below -128, you should use a tinyint. This will only use 1 byte of data, which may not seem like much of a difference between the 4 used by an int, but as soon as you start to store more and more data, the effect on speed and space will be noticable.
Anyway, I thought I should share my new found knowledge of the display width with everyone, because it will save me thinking its optimising stuff changing from 10 to 5, ha ha.
Illustration :-
A TABLE WITH-OUT ZEROFILL :-
mysql> CREATE TABLE XYZ_ (ID_ INT(200));
Query OK, 0 rows affected (0.06 sec)
mysql> INSERT INTO XYZ_ (ID_) VALUES (1);
Query OK, 1 row affected (0.00 sec)
mysql> select * from XYZ_;
+——+
| ID_ |
+——+
| 1 |
+——+
1 row in set (0.00 sec)
A TABLE WITH ZEROFILL :-
mysql> CREATE TABLE XYZ (ID INT(200) ZEROFILL);
Query OK, 0 rows affected (0.08 sec)
mysql>
mysql> INSERT INTO XYZ (ID) VALUES (10);
Query OK, 1 row affected (0.00 sec)
mysql> select * from XYZ;
+———————————————————————————————————————————————————————————————————-+
| ID |
+———————————————————————————————————————————————————————————————————-+
| 00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000010 |
+———————————————————————————————————————————————————————————————————-+
1 row in set (0.00 sec)
mysql>