轉自：http://www.0791quanquan.com/news_keji/topic_816453/ 探索一：正負數問題 拿tinyint欄位來舉例，unsigned後，欄位的取值範圍是0-255，而signed的範圍是-128 - 127。 那麼如果我們在明確不需要負值存在的情況下，通常是 ...
轉自：http://www.0791quanquan.com/news_keji/topic_816453/
探索一：正負數問題
拿tinyint欄位來舉例，unsigned後，欄位的取值範圍是0-255，而signed的範圍是-128 - 127。 那麼如果我們在明確不需要負值存在的情況下，通常是不要設置signed來支持負數的。 因為只支持正數會讓存儲空間大一倍呢（當然我這種表達可能不准確）。 假設我們使用tinyint來存儲一些狀態值。 0表示刪除，1表示待付款，2表示已付款，3...。 突然來個需求要加訂單取消，一些有代碼潔癖的人就想，那就將定義為：-1表示取消吧。 但是就因為有了-1，我們說起來應該可以從0存到255的，結果就變為了0-127。 所以一般情況下，我們不建議這樣設置
欄位設置為unsigned後有一個問題是：
當select a - b from t時，a為10，b為12，那麼這時就會出現異常情況：ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '(`test`.`t`.`a` - `test`.`t`.`b`)'
所以註意這種情況即可
探索二：性能問題
嚴格講，在性能上是有細微的差別的。 unsigned的性能更好，當只存儲正整數的情況下。 因為，當unsigned時，假設查詢值在500以下的數據，那麼MySQL會將範圍定義為：0-500，而如果是signed，則查詢範圍為：-2147483648 - 500。 參考文章：http://rakesh.sankar-b.com/2010/08/25/mysql-unsigned-int-to-signed-int-performance-tips-index/
裡面講到：
Let’s say you want to know the list of customers who have purchased an item of quantity 500 or less. Following is the query you might be used to get these results:
SELECT *
FROM customer
WHERE quantity <= 500
Cool, the above query will yield you the list of customers who have purchased an item of quantity 500 or less. Right, what is the big deal, it should return fast, but consider when you have a table with millions of records then this query might be slow in returning you the results.
Yes, that is true, you can always add an “ index ” to the “quantity” field and improve the performance – exactly, this should 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 have an index of this field, MySQL will define the range as -2147483648 to 500 and it will get the result based on this range.
With “unsigned”:
Process flow, since the quantity field is an “ int ” with “ unsigned ” and you have an index of this field, MySQL will define the range as 0 to 500 and it will get the result based on this range.
Now compare the difference yourself and tell me, for sure it will 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 “ signed “, it’s always better to write a full-syntax while creating a table.
總的說來，設置unsigned最大的差異是欄位取值範圍的變化。 所以基於這點來對欄位的unsigned或者signed是比較明智的決定
以上
參考文獻：
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
