When should I use UNSIGNED and SIGNED INT in MySQL

Question

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

User · Answer

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

User · Answer

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    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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

Use UNSIGNED for non-negative integers

[mysql] When should I use UNSIGNED and SIGNED INT in MySQL?

Examples related to mysql