Signed or unsigned in MySQL

Solution 1:

According to section 10.2 of the MySQL 5.1 Manual:

In non-strict mode, when an out-of-range value is assigned to an integer column, MySQL stores the value representing the corresponding endpoint of the column data type range. If you store 256 into a TINYINT or TINYINT UNSIGNED column, MySQL stores 127 or 255, respectively. When a floating-point or fixed-point column is assigned a value that exceeds the range implied by the specified (or default) precision and scale, MySQL stores the value representing the corresponding endpoint of that range.

So using UNSIGNED is really only necessary when you are concerned about the upper bound. Also adding UNSIGNED does not affect the size of the column just how the number is represented.

Solution 2:

It doesn't matter unless you are trying to get the most bang for your buck out of the values and don't need negative values.

For instance, let's say you wanted to store 0-255.

You could use a tinyint but only if you use it as unsigned.

Lots of the databases I've seen, people don't bother optimizing like this and end up with some rather large tables because they just use INTs all the time.

Still, if you're talking about int vs unsigned int, there is no performance affect or space effect at all.

From a standards standpoint, I always use unsigned and only use signed when I know I will need negative values.

Solution 3:

When it comes to performance or storage, it is absolutely the same thing.

As a GENERAL RULE, use whichever fits better for you: if you will need only positive values, store values as UNSIGNED, otherwise, let it be the default [SIGNED].

One problem arises when a SIGNED value is set for a PRIMARY AUTOINCREMENT column: the counting of the auto-generated numbers begin with 1 (not the smallest negative number) and the possible values will end earlier, as you will use only a half of values. So in this case (PRIMARY + AUTOINCREMENT column) it is better to store as UNSIGNED.