What does `unsigned` in MySQL mean and when to use it?

mysql types
HELP picture HELP · Oct 9, 2010 · Viewed 183.8k times · Source

What does "unsigned" mean in MySQL and when should I use it?

Answer

codaddict picture codaddict · Oct 9, 2010

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 do I use it ?

Ask yourself this question: Will this field ever contain a negative value?
If the answer is no, then you want an UNSIGNED data type.

A common mistake is to use a primary key that is an auto-increment INT starting at zero, yet the type is SIGNED, in that case you’ll never touch any of the negative numbers and you are reducing the range of possible id's to half.

Related questions

What is the size of column of int(11) in mysql in bytes?

What is the size of column of int(11) in mysql in bytes? And Maximum value that can be stored in this columns?

Read More
What data type to use for hashed password field and what length?

I'm not sure how password hashing works (will be implementing it later), but need to create database schema now. I'm thinking of limiting passwords to 4-20 characters, but as I understand after encrypting hash string will be of different length. …

Read More
Types in MySQL: BigInt(20) vs Int(20)

I was wondering what the difference between BigInt, MediumInt, and Int are... it would seem obvious that they would allow for larger numbers; however, I can make an Int(20) or a BigInt(20) and that would make seem that it is …

Read More