《Mysql入门mysql 中int类型字段unsigned和signed的探索》要点：
本文介绍了Mysql入门mysql 中int类型字段unsigned和signed的探索，希望对您有用。如果有疑问，可以联系我们。
MYSQL必读拿tinyint字段来举例,unsigned后,字段的取值范围是0-255,而signed的范围是-128 - 127. 那么如果我们在明确不需要负值存在的情况下,通常是不要设置signed来支持负数的. 因为只支持正数会让存储空间大一倍呢(当然我这种表达可能不准确). 假设我们使用tinyint来存储一些状态值. 0表示删除,1表示待付款,2表示已付款,3.... 突然来个需求要加订单取消,一些有代码洁癖的人就想,那就将定义为：-1表示取消吧. 但是就因为有了-1,我们说起来应该可以从0存到255的,结果就变为了0-127. 所以一般情况下,我们不建议这样设置
MYSQL必读 字段设置为unsigned后有一个问题是：
MYSQL必读当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`)'
MYSQL必读所以注意这种情况即可
MYSQL必读探索二：性能问题
MYSQL必读严格讲,在性能上是有细微的差别的. 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/
MYSQL必读里面讲到：
MYSQL必读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:
MYSQL必读SELECT *
FROM customer
WHERE quantity <= 500
MYSQL必读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.
MYSQL必读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”.
MYSQL必读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.
MYSQL必读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.
MYSQL必读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.
MYSQL必读总的说来,设置unsigned最大的差异是字段取值范围的变化. 所以基于这点来对字段的unsigned或者signed是比较明智的决定
MYSQL必读以上
MYSQL必读参考文献：
MYSQL必读http://verysimple.com/2006/10/22/mysql-data-type-optimization-tips/
MYSQL必读http://rakesh.sankar-b.com/2010/08/25/mysql-unsigned-int-to-signed-int-performance-tips-index/
MYSQL必读http://dev.mysql.com/doc/refman/5.7/en/numeric-type-overview.html
MYSQL必读http://www.cnblogs.com/blankqdb/archive/2012/11/03/blank_qdb.html
转载请注明本页网址：
http://www.vephp.com/jiaocheng/5767.html
