Signed or unsigned in MySQL

Question

I wonder if there is any positive effect when using the UNSIGNED flag when defining some integer field in MySQL? Does the query make the database faster or smaller? Or should I just worry about it if the upper limit bothers me?

Answer 1

According to section 10.2 of the MySQL 5.1 manual:

In non-strict mode, when an integer column is assigned a value out of range, MySQL stores a value representing the corresponding endpoint of the assortment column data type. If you store 256 in 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 a value that represents the corresponding endpoint of that range.

Therefore, using UNSIGNED is really necessary only if you are concerned about the upper bound. Also, adding UNSIGNED does not affect the size of the column as the number is represented .

Answer 2

It doesn’t matter if you are not trying to get the most out of your values ​​and do not need negative values.

For example, let's say you wanted to keep 0-255.

You can use tinyint, but only if you use it as unsigned.

A lot of the databases that I saw, people don’t bother with optimization and end up with rather large tables, because they just use INTs all the time.

However, if you are talking about int vs unsigned int, there is no performance impact or indirect effect at all.

From a standards perspective, I always use unsigned and only use signed ones when I know that I need negative values.

Answer 3

When it comes to performance or storage, this is exactly the same.

As a GENERAL RULE, use what suits you best: if you need only positive values, save the values ​​as UNSIGNED, otherwise let it be the default [SIGNED].

One problem arises when the PRIMARY AUTOINCREMENT column is set to SIGNED: the calculation of automatically generated numbers starts at 1 (not the least negative number), and the possible values ​​end earlier, since you will use only half of the values. Thus, in this case (column PRIMARY + AUTOINCREMENT) it is better to store as UNCERTAINTY.

Answer 4

Use unsigned when the column is only for placing positive numbers.

This will not affect the I / O performance in the column, since it will take up exactly the same amount of space.

Answer 5

This will improve overproduction, suppose if you want to find quantities <50o.

Without "unsigned": The flow process, since the quantity field is "int", and you have the index of this field, MySQL will determine the range from -2147483648 to 500, and it will get the result based on this range.

With "unsigned": The flow process, since the quantity field is "int" with "unsigned", and you have the index of this field, MySQL will determine the range from 0 to 500, and it will get the result based on this range.

Answer 6

Here you can see the MySQL documentation for the SIGNED or UNSIGNED INTs ranges. You will quickly notice that the gender for UNSIGNED INT is always 0, so it can never be negative.

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 

Answer 7

Warning , there is a problem with Unsigned Int (UINT) and Entity Framework or ADO.NET. This may be an integration issue with MySql Connector version 6.

In my experience, UInt reads as Long EF, which can cause some accuracy issues since UInt not Long . This can cause a headache with someone not familiar with the problem.

Other problems:

Integration issue between EF and Mysql Connector 6

DBContext cannot use UINT

EF Provider Problem with UINT