Q . What does int(10) or int(11) means in MySQL ?

@mit $ingh. asked, Apr 08 ' 2017

in MySQL if we create a field dataType of INT and does not specify any length/values then it automatically became int(11) and if we set the attribute UNSIGNED or UNSIGNED ZEROFILLthen it turns into int(10)

Where does this length(1) goes?

  • MySQL
  • integer
  • datatype

2 Answers

Did you guys ever thought why do we use int(4)/int(10)/int(11) as data type in MySQL while creating a table ? Not sure if anyone had such doubts , but I seriously had and wanted to clarify it for myself and later I thought of sharing my thoughts on the same here.

So when I was thinking about this syntax I got couple of questions in my mind.

  1. Is this going to effect max/min limit of the INT data type ?
  2. If not then what is the use of having 11 along with INT ?

I hope most of us might be aware of the max and min limits of signed and unsigned INT data type , but still lets have it again here.

TypeBytesMinimumMaximum
INT4-21474836482147483648
INT UNSIGNED404294967295

We will be doing testing with two things one on a column with data type as just INT and the other as INT(4)

INT Data Type

I created a test table for testing the behavior of just INT data type as below

create table int_test(a int zerofill NOT NULL auto_increment, PRIMARY KEY (a));

Now insert sample data as below

insert into int_test values(1);
insert into int_test values();
insert into int_test values(03);
insert into int_test values(2147483648);

Now the data inserted above will list as below :

select * from int_test;
+------------+
| a          |
+------------+
| 0000000001 |
| 0000000002 |
| 0000000003 |
| 2147483648 |
+------------+

So from the above output we can see that for what ever record gets inserted through auto-increment or manually prefixes “0” by default till the 10th digit. So this is what happens when we use just “int” in data type while defining a table.

INT(4) Data Type

Now, Lets see how the same thing works with int(4) data type

create table int_test_4(a int(4) zerofill NOT NULL auto_increment, PRIMARY KEY (a));

Now inserted the same sample data as before :

insert into int_test_4 values(1);
insert into int_test_4 values();
insert into int_test_4 values(03);
insert into int_test_4 values(2147483648);

Now the data inserted above will list as below :

select * from int_test_4;
+------------+
| a          |
+------------+
|       0001 |
|       0002 |
|       0003 |
| 2147483648 |
+------------+

So now here is the difference, from the above output we can see that for every record inserted is getting prefixed with a “0” by default till the 4th digit and this fourth is the limit which we are setting up in int(4). So this is what happens when we use just “int(4)” in data type while defining a table. And this does not affect the max value limit of int data type by anyway.

Conclusion : 

The INT size is always 4 bytes and the possible values are between -2,147,483,648 and 2,147,483,647 or if it’s not signed then from 0 to 4,294,967,295 so defining columns using int(4),int(10),int(11) etc. is not going to effect these values anyway.

int value can be -2147483648 these are 11 digits so the default display size is 11

unsigned int does not allow negative numbers so by default it need only display size 10

As the documentation below shows, the number of bits required to store SIGNED INT and UNSIGNED INT is the same, the range of storable numbers is merely shifted:

Unsigned type can be used to permit only nonnegative numbers in a column or when you need a larger upper numeric range for the column. For example, if an INT column is UNSIGNED, the size of the column's range is the same but its endpoints shift from -2147483648 and 2147483647 up to 0 and 4294967295.

http://dev.mysql.com/doc/refman/5.0/en/numeric-types.html

Leave a Ansewer