青海媒体网站建设公司,整站系统,苏州关键词优化seo,网站留言板制作转自#xff1a;http://www.0791quanquan.com/news_keji/topic_816453/探索一#xff1a;正负数问题拿tinyint字段来举例#xff0c;unsigned后#xff0c;字段的取值范围是0-255#xff0c;而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为10b为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 customerWHERE quantity 500Cool, 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.htmlhttp://www.cnblogs.com/blankqdb/archive/2012/11/03/blank_qdb.html
