135

When should I use UNSIGNED and SIGNED INT in MySQL ? What is better to use or this is just personal prefernce ? Because I've seen it used like this;

id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT

and

id INT(11) NOT NULL AUTO_INCREMENT
Tux
  • 1,773
  • 4
  • 16
  • 19

10 Answers10

213

UNSIGNED only stores positive numbers (or zero). On the other hand, signed can store negative numbers (i.e., may have a negative sign).

Here's a table of the ranges of values each INTEGER type can store:

MySQL INTEGER types and lengths
Source: http://dev.mysql.com/doc/refman/5.6/en/integer-types.html

UNSIGNED ranges from 0 to n, while signed ranges from about -n/2 to n/2.

In this case, you have an AUTO_INCREMENT ID column, so you would not have negatives. Thus, use UNSIGNED. If you do not use UNSIGNED for the AUTO_INCREMENT column, your maximum possible value will be half as high (and the negative half of the value range would go unused).

Wiseguy
  • 20,522
  • 8
  • 65
  • 81
  • 32
    Do note, however, that `UNSIGNED` is MySQL-specific and *not* a standard SQL feature. This means that using `UNSIGNED` can make a future migration to a different RDBMS more complicated or cause you difficulties when using software libraries targeting standard SQL such as SQLAlchemy. I think this should be a part of the answer. – minexew Sep 14 '18 at 12:20
  • 2
    Seconding @minexew's comment. I'm using SQLAlchemy in Python with a MySQL database. I'm having issues assigned foreign key restraints because tables previously created have UNSIGNED integers and a library like SQLAlchemy, which follows SQL standards, doesn't have an option to create UNSIGNED columns unless you start writing MySQL specific code. This isn't as scalable (i.e. using SQLite database for integration tests, etc.) even though it's still definitely usable. – MillerMedia Oct 29 '20 at 22:34
10

Use UNSIGNED for non-negative integers.

Michal Šrůtek
  • 1,647
  • 16
  • 17
Paul Denisevich
  • 2,329
  • 14
  • 19
6

Basically with UNSIGNED, you're giving yourself twice as much space for the integer since you explicitly specify you don't need negative numbers (usually because values you store will never be negative).

Nae
  • 14,209
  • 7
  • 52
  • 79
Srneczek
  • 2,143
  • 1
  • 22
  • 26
2

For negative integer value, SIGNED is used and for non-negative integer value, UNSIGNED is used. It always suggested to use UNSIGNED for id as a PRIMARY KEY.

vikash singh
  • 1,479
  • 1
  • 19
  • 29
Sheikh Hafizur Rahman
  • 21
  • 1
1

I think, UNSIGNED would be the best option to store something like time_duration(Eg: resolved_call_time = resolved_time(DateTime)-creation_time(DateTime)) value in minutes or hours or seconds format which will definitely be a non-negative number

Nae
  • 14,209
  • 7
  • 52
  • 79
Kamal
  • 69
  • 4
1

I don't not agree with vipin cp.

The true is that first bit is used for represent the sign. But 1 is for negative and 0 is for positive values. More over negative values are coded in different way (two's complement). Example with TINYINT:

The sign bit
|
1000 0000b = -128d  
...  
1111 1101b = -3d  
1111 1110b = -2d  
1111 1111b = -1d  

0000 0000b = 0d  
0000 0001b = 1d  
0000 0010b = 2d  
...  
0111 1111b = 127d
juzraai
  • 5,693
  • 8
  • 33
  • 47
Kinga the Witch
  • 129
  • 1
  • 5
0

One thing i would like to add In a signed int, which is the default value in mysql , 1 bit will be used to represent sign. -1 for negative and 0 for positive. So if your application insert only positive value it should better specify unsigned.

Vipin CP
  • 3,642
  • 3
  • 33
  • 55
0

If you know the type of numbers you are going to store, your can choose accordingly. In this case your have 'id' which can never be negative. So you can use unsigned int. Range of signed int: -n/2 to +n/2 Range of unsigned int: 0 to n So you have twice the number of positive numbers available. Choose accordingly.

Debanik Dawn
  • 797
  • 5
  • 28
0

If you wanna keep it simply, then you have to keep in mind if you want to calculate with these numbers as well (subtraction). If you for example calculate A - B and B is bigger than A, you can get in trouble. There you would have to CAST the SIGNED number to a UNSIGNED.

You can read more about this for example here: BIGINT UNSIGNED VALUE IS out of range My SQL

terryer
  • 1
  • 1
  • 2
0

Use the unsigned if u not need use foreign this field on current table. I was have next problem:

General error: 3780 Referencing column 'event_id' and referenced column 'id' in foreign key constraint 'events_event_id_foreign' are incompatible. (SQL: alter table `events` add constraint `events_event_id_foreign` foreign key (`event_id`) references `users` (`id`) on delete cascade)
Vito Bryliano
  • 161
  • 1
  • 5