MySQL如何选择数据类型

2019-02-16 07:28:10   MySQL
  MySQL  

选择合适的数据类型

几个简单的原则:

  • a 更小的通常更好
  • b 简单就好。例如多用内建类型而非字符串存日期时间;用整形存ip
  • c 尽量避免null,尤其是索引列,会在排序的时候产生一些效率问题,例外是innodb引擎使用单独的位(bit)存null,对系数数据有很好的空间效率。

整形

  • tinyint, smallint, mediumint, int, bigint 几种数据类型所占空间分别是8bit, 16bit, 24bit, 36bit, 64bit
  • 范围是-2的n-1次方到2的n-1次方减1
  • 可选unsigned属性,不许负值,如此以来存储负数的空间用来存正数,可以使正数上限提高近1倍
  • 指定宽度只是限制交互工具,如命令行客户端等显示字符的个数,对于存储和计算来说int(1) int(20)是相同的

实数(带小数部分的数字)

  • 不只为存小数部分,也可用decimal存比bigint大的正数。
  • float和double类型支持使用标准浮点运算进行近似计算。
  • decimal类型用于存储精确的小数。5.0版本后支持精算。其将数字打包保存到一个二进制字符串,每四个字节存九个数字,例如decimal(18,9)小数点两边各9数字,共9字节,前后各4,小数点占1个。最多允许65个数字。
  • float和double分别是占4字节和8字节,内部使用double进行浮点计算。
  • 对于小数精算时使用decimal,例如财务数据。但是数据量过大,可以用bigint带起,将需存的货币单位乘以相应倍数即可。

字符串类型

  • varchar的选择条件

    • 字符串列最大长度比平均长度大很多
    • 列的更新很少,所以碎片不是问题
    • 使用了像utf8这样复杂的字符集,每个字符都用不同字节数存储
  • char

    • 会根据需要采用空格进行填充以便比较
    • 适合短字符串或所有值接近一个长度,例如md5值就是定长
    • 对于常变数据char优于varchar,因为定长char无碎片
    • 短列char空间利用率高,例如char(1)占1byte 而varchar(1)需要2byte,因为有1byte存字符串长度
    • varchar(5)跟varchar(200)存'hello'空间开销一样,但是短列内存消耗更少

日期和时间

  • timestamp 4字节 datetime 8字节
  • timestamp 与时区有关,datetime与时区无关
  • timestamp区间1970到2038,但是我曾测试可以插入0000 00:00:00,datetime1001年到9999年
  • timestamp默认not null,但并不是网上说的不能设置null,插入时会默认当前时间,但是这些功能可以通过default current_timestamp, on update current_timestamp等实现插入或者更新保存当前时间,并不是timestamp才可以
  • 关于这两种类型的比较详情请见这里

补充

  • varchar类型是用于存储可变长的字符串,比定长类型更节省空间,它仅使用必要的空间。
  • 所谓varchar(5)跟varchar(200)存 ‘hello’ 空间开销一样,可以看上面的定义‘仅使用必要空间’,所以说都是存5个字符,只占用5个字符的空间。
  • 而在内存的分配过程中会根据varchar后面的数字分配固定大小的内存空间,这样就导致了varchar(200)分配了多余的内存,这样在使用内存临时表等情况的时候会有所影响。
  • 不要把int后面的数字跟varchar后面的数字混为一谈,int后面数字是指宽度,举例来说1到9的宽度都是1,10到99的宽度都是2,56345的宽度是5,这个只影响客户端交互,mysql手册上说这个是显示宽度,而实际不会跟varchar那样选择必要空间存储,也就是说int(1)跟int(20)都是需要32B的空间的,计算上也是这样,而且并不是说int(1)就不能存10, 手册上表明只要此类型可以存下就能存,与后面的显示长度无关。

注:5.0之前varchar后面数字是字节数,之后是字符个数,如果是之前的字节的话,能存多少字符就不好说了,因为还跟字符集有关。

vien.tech版权所有,允许转载,但转载请注明出处和原文链接: https://vien.tech/article/28
欢迎小伙伴们在下方评论区留言 ~ O(∩_∩)O
文章对我有帮助, 点此请博主吃包辣条 ~ O(∩_∩)O

猜你喜欢


评论

There are no comments yet.
未登录

登录后即可发表评论

登录或注册