前言
作为一名后端开发人员,在日常的工作中避免不了与数据库打交道。现在的互联网公司,大小不一,尤其是创业型公司,程序员身兼数职是很常见的一件事。所以,掌握MySQL数据库的相关知识是我们必要的技能。本文主要整理了我们在工作过程中在使用整型时需要注意的问题。希望对阅读者有所帮助。
自增类型不要作为主键,也不要设置为INT类型
我们先来看一下MySQL支持的整数类型的总结,参考下表:
类型 | 占用空间 | 最小值~最大值(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 |
我们先解释第一点:自增类型不要设置成INT类型。
原因很简单,随着互联网的日渐发达,我们的数据库如果设置成INT
类型的自增列,即便是unsigned
,才最多支持42亿的数据量。这是很容易达到的。数据达到上限,再次执行插入数据,会报错而不会重置为1。所以,我们设置自增类型的列,默认就设置成BIGINT
类型。
在来解释第二点:自增类型不要作为主键。
原因是:在MySQL8.0以前,自增的字段不回持久化,会发生回溯现象。回溯就是说,我们的自增id现在是4,我们删除了自增id为4的行数据,我们查看表信息会发现,此时的自增长度依然为4.如果此时发生数据库重启之后,我们再来查看该表,该表的自增id将重置为3。这种情况下,我们的关联信息就有可能发生错乱,导致不可预估的后果。
综上所述:我们的主键最好使用字符串来作为主键。
如果没有特殊需求,不要设置整型字段为unsigned
我们由上表可以知道,如果我们设置整型字段为unsigned
,我们可以保证该字段是一个非负的整型。但是,如果我们有列计算的时候,数据库会要求unsigned
类型的字段的计算结果也应该是unsigned
的(也就是非负的)。如果计算结果是负数,则会抛出ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in 。。。的异常。
解决方案:我们设置数据库支持计算结果为signed
。也就是对数据库参数sql_mode 设置为 NO_UNSIGNED_SUBTRACTION。(最好在mysql.ini里面设置,来使设置全局生效)
浮点类型尽量避免使用
这个很好理解,浮点类型不是精确类型,也不是SQL的标准类型。此种类型在计算的时候还会有精度丢失。在MySQL8.0之后,我们在创建表设置字段为浮点型的时候,会弹出警告提示,大致就是说此类型不是标准类型,后续版本的MySQL还会移除该类型等等。所以我们在设计表的时候,避免使用浮点类型。
资金字段使用整型而不是DECIMAL
我们可能涉及到资金这种高精度计算的需求,第一反应就是设计成DECIMAL
类型。但是在互联网的设计中,我们更加倾向于使用整型来存储,将其精度控制到分。原因如下:
DECIMAL
是变长字段,金额的数目往往不好确定,所以DECIMAL
的长度就不好确定。而整型的范围足够支撑超级巨大的金额数值,而且整型是定长的,存储效率高。DECIMAL
是通过二进制实现的一种编码方式,计算效率比较低。整型的计算效率比较高。
在设计数据库字段的时候,尽量使用定长的类型
原因很简单,因为定长的字段存储效率高,不需要计算占有空间。变长的字段如果发生变化,有可能产生表的空间碎片,还得需要人为清理。(比如变长的字段修改为比原来更大的值,原来的分配空间不够用,那么就得重新分配空间来存储,原来的空间记录就成了空间碎片,造成了资源浪费)。所以我们在设计表的时候,尽量使用定长类型,避免使用变长类型。
文章评论