Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Signed or unsigned in MySQL

I wonder is there any positive effect in using UNSIGNED flag on defining some integer field in MySQL? Does it make queries faster or database smaller? Or should I only bother with it if I'm concerned about upper limit?

like image 685
Riho Avatar asked Jan 25 '09 20:01

Riho


People also ask

What is signed and unsigned in MySQL?

UNSIGNED only stores positive numbers (or zero). On the other hand, signed can store negative numbers (i.e., may have a negative sign). 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.

What is unsigned in MySQL?

The “unsigned” in MySQL is a data type. Whenever we write an unsigned to any column that means you cannot insert negative numbers. Suppose, for a very large number you can use unsigned type. The maximum range with unsigned int is 4294967295. Note: If you insert negative value you will get a MySQL error.

What is the difference between unsigned and signed?

The term "unsigned" in computer programming indicates a variable that can hold only positive numbers. The term "signed" in computer code indicates that a variable can hold negative and positive values.

What is signed or unsigned number?

Signed numbers use sign flag or can be distinguish between negative values and positive values. Whereas unsigned numbers stored only positive numbers but not negative numbers.


2 Answers

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.

like image 107
Kevin Loney Avatar answered Sep 21 '22 21:09

Kevin Loney


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.

like image 27
GeoffreyF67 Avatar answered Sep 19 '22 21:09

GeoffreyF67