Exploring the unsigned and signed of int type fields in MySQL

Transferred from: http://www.0791quanquan.com/news_keji/topic_816453/

Explore one: Positive negative number problem

Take the tinyint field for example, after unsigned, the range of fields is 0-255, and the range of signed is-128-127. So if we explicitly do not need a negative value to exist, we usually do not set signed to support negative numbers. Because only a positive number would make the storage space one-fold (of course, I might not be accurate). Suppose we use tinyint to store some state values. 0 means delete, 1 for payment, 2 for paid, 3 .... A sudden need to add order cancellation, some people with code cleanliness think, it will be defined as:-1 means cancel it. But because of the 1, we say we should be able to save from 0 to 255, and the result becomes 0-127. So in general, we do not recommend this setting

After the field is set to unsigned, there is a problem:

When select a-B from T, the A is 10,b 12, then an exception occurs: ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in ' (' Test '. ' t '. ' A ' -' test '. ' t '. ' B ') '

So pay attention to this situation

Explore Two: Performance issues

Strictly speaking, there is a slight difference in performance. Unsigned performance is better when only a positive integer is stored. Because, when unsigned, assuming that the query value is below 500, then MySQL defines the scope as: 0-500, and if it is signed, the query scope is:-2147483648-500. Reference article: http://rakesh.sankar-b.com/2010/08/25/mysql-unsigned-int-to-signed-int-performance-tips-index/

The inside says:

Let's say you want to know the list of customers who has purchased an item of quantity. Following is the query you might be used to get these results:

SELECT *
From customer
WHERE Quantity <= 500

Cool, the above query would yield you the list of customers who has purchased an item of quantity and less. Right, what's the big deal, it should return fast, but consider if you had a table with millions of records and this Query might is slow in returning you the results.

Yes, that's true, you can always add a "index" to the "Quantity" field and improve the performance–exactly, this Sho Uld improve the performance of processing the query much better than without an "index".

Without "unsigned":
Process flow, since the Quantity field is an "int." and you had an index of this field, MySQL would define the range a s-2147483648 to and it'll get the result based on this range.

With "unsigned":
Process flow, since the Quantity field is an ' int ' with ' unsigned ' and you has an index of this field, MySQL would d Efine the range as 0 to + and it'll get the result based on this range.

Now compare the difference yourself and tell me, for sure it'll improve the performance of the Your query. Since We know we never store any negative (signed values) in the Quantity field and the default behavior of ' int ' is ' s Igned ", it's always better to write a full-syntax while creating a table.

Overall, the biggest difference in setting unsigned is the change in the range of field values. So it's a wise decision to unsigned or signed a field based on this point.

Above

Reference documents:

http://verysimple.com/2006/10/22/mysql-data-type-optimization-tips/

http://rakesh.sankar-b.com/2010/08/25/mysql-unsigned-int-to-signed-int-performance-tips-index/

Http://dev.mysql.com/doc/refman/5.7/en/numeric-type-overview.html

Http://www.cnblogs.com/blankqdb/archive/2012/11/03/blank_qdb.html

Exploring the unsigned and signed of int type fields in MySQL