What's the difference in int(11) and int(11) UNSIGNED?

What's the difference in int(11) and int(11) UNSIGNED ?

An UNSIGNED type cannot be negative, but on the other hand it has twice as large a range for the positive integers. The types TINYINT, SMALLINT, MEDIUMINT, INT and BIGINT all have signed and unsigned versions.

For INT the ranges are defined as follows:

Type          Storage         Min           Max
INT                 4 -2147483648    2147483647
INT UNSIGNED        4           0    4294967295

The signed and unsigned types take the same storage space (4 bytes for INT).

See the documentation for more details.

INT goes from -2147483648 to +2147483647
UNSIGNED INT goes from 0 to 4294967295

the 11 between the braces has no effect on the number, just how it's displayed.

UNSIGNED means that it can hold only nonnegative values, i.e. it can't hold for example -20

UNSIGNED is exactly that, its all positive (no sign) numbers. The size of bytes is the same, but if your data is never negative you can get larger positive numbers out of it. The 11 is the default of how many characters it will fetch and display. For the exact size, do a search for the DBMS you are using and the type.

All integer types can have an optional (nonstandard) attribute UNSIGNED. 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.

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

The unsigned one can't hold negative numbers.

An unsigned integer can handle values from 0 to 2^(size in bits of the integer field). A signed integer can handle values from -2^(size of the integer field-1) to 2^(size of the integer field-1)-1.

I think you may want to know the difference between int and int(10).

Let's give an example for int(10) one with zerofill keyword, one not, the table likes that:

create table tb_test_int_type(
    int_10 int(10),
    int_10_with_zf int(10) zerofill,
    unit int unsigned
);

Let's insert some data:

insert into tb_test_int_type(int_10, int_10_with_zf, unit)
values (123456, 123456,3147483647), (123456, 4294967291,3147483647) 
;

Then

select * from tb_test_int_type; 

# int_10, int_10_with_zf, unit
'123456', '0000123456', '3147483647'
'123456', '4294967291', '3147483647'

We can see that

• with keyword zerofill, num less than 10 will fill 0, but without zerofill it won't

• Secondly with keyword zerofill, int_10_with_zf becomes unsigned int type, if you insert a minus you will get error Out of range value for column...... But you can insert minus to int_10. Also if you insert 4294967291 to int_10 you will get error Out of range value for column.....

Conclusion:

1. int(X) without keyword zerofill, is equal to int range -2147483648~2147483647

2. int(X) with keyword zerofill, the field is equal to unsigned int range 0~4294967295, if num's length is less than X it will fill 0 to the left