what is MySql signed / unsigned option?

images

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
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s