CODEWITHSUNDEEP
mysql
Ben
Ben

Reputation: 62384

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

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

Upvotes: 52

Views: 53710

Answers (8)

Jayhello
Jayhello

Reputation: 6602

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

Upvotes: 1

KeithS
KeithS

Reputation: 71573

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.

Upvotes: 1

Mark Byers
Mark Byers

Reputation: 838226

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.

Upvotes: 96

Matteo Riva
Matteo Riva

Reputation: 25060

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.

Upvotes: 29

Tyilo
Tyilo

Reputation: 30102

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

Upvotes: 3

rayman86
rayman86

Reputation: 1395

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.

Upvotes: 4

tloach
tloach

Reputation: 8040

The unsigned one can't hold negative numbers.

Upvotes: 2

Armen Tsirunyan
Armen Tsirunyan

Reputation: 133004

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

Upvotes: 7

Related Questions