When to use signed / unsigned option in MySql.
Mysql Says:
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.
When to use UNSIGNED data type?
If your field will never stores negative values, then its best to use UNSIGNED, that will give you faster index and more range.
When to use SIGNED data type?
If you need to store negative then use signed, but remember your number range is half now.
Note: Common mistake people do is to store primary number ( auto -increment ) as as signed. Which mean you are already reducing your number range even you will never use negative number as your primary key.
MySQL supports the SQL standard integer types INTEGER
(or INT
) and SMALLINT
. As an extension to the standard, MySQL also supports the integer typesTINYINT
, MEDIUMINT
, and BIGINT
. The following table shows the required storage and range for each integer type.
Type | Storage | Minimum Value | Maximum Value |
---|---|---|---|
(Bytes) | (Signed/Unsigned) | (Signed/Unsigned) | |
TINYINT |
1 | -128 |
127 |
0 |
255 |
||
SMALLINT |
2 | -32768 |
32767 |
0 |
65535 |
||
MEDIUMINT |
3 | -8388608 |
8388607 |
0 |
16777215 |
||
INT |
4 | -2147483648 |
2147483647 |
0 |
4294967295 |
||
BIGINT |
8 | -9223372036854775808 |
9223372036854775807 |
0 |
18446744073709551615 |