最近在准备给开发做一个mysql数据库开发规范方面培训,一步一步来,结合在生产环境发现的数据库方面的问题,从几个常用的数据类型说起。
它们都是(精确)整型数据类型,但是占用字节数和表达的范围不同。首先没有这个表就说不过去了:
type
storage
minimum value
maximum value
(bytes)
(signed/unsigned)
tinyint
1
-128
127
255
smallint
2
-32768
32767
65535
mediumint
3
-8388608
8388607
16777215
int
4
-2147483648
2147483647
4294967295
bigint
8
-9223372036854775808
9223372036854775807
18446744073709551615
只需要知道对应类型占多少字节就能推算出范围了,比如int占 4 bytes,即4*8=32bits,大约10位数字,也能理解为什么int默认显示位数是11。
遇到比较多的是tinyint和bigint,tinyint一般用于存放status,type这种数值小的数据,不够用时可能会用smallint。bigint一般用于自增主键。
为了避免数据库被过度设计,布尔、枚举类型也采用tinyint。
还有一点也是经常被提到的关于 int(m) 中m的理解,int型数据无论是int(4)还是int(11),都已经占用了 4 bytes 存储空间,m表示的只是显示宽度(display width, max value 255),并不是定义int的长度。
例如:
插入几条数据看一下:
<!-- more -->
上面的实验说明了几个问题:
f_id列插入比最大值还大的数,出现warnings,并且最终的值自动变成 9223372036854775807 。这个坑曾经在迁移到阿里rds时遇到过,他们的迁移工具是java写的,结果我们的主键值大于java integer里面的最大限制,导致 duplicate key问题。
f_flag的显示宽度为1,但并不影响更多位数的显示。也证实了tinyint(1)并不像char(1)那样限制存储长度
f_num定义成无符号的zerofill类型,能存储的最大数值是65535,而signed才是32767。(当列上使用zerofill时,unsigned会自动加上)
zerofill的作用是在显示检索结果的时候,左边用0补齐到display width,实际存储时不补0的,仅作为返回结果meta data的一部分。查询的条件值忽略0和空格
length()在numeric类型中作用于char_length()一样,因为字节数已经固定了。
zerofill的使用可能会在复杂join时因为了解不够深入而带来问题,所以最终的结论也很简单:除非极端的特殊需要,尽量不用zerofill,建表时这类int无需指定 (11) 这样的显示宽度。
mysql使用<code>decimal</code>类型去存储对精度要求比较高的数值,比如金额,也叫定点数,decimal在mysql内存是以字符串存储的。声明语法是<code>decimal(m,d)</code>,占用字节 m+2 bytes。m是数字最大位数(精度precision),范围1-65;d是小数点右侧数字个数(标度scale),范围0-30,但不得超过m。
比如定义<code>decimal(7,3)</code>:
能存的数值范围是 -9999.999 ~ 9999.999,占用9个字节
123.12 -> 123.120,因为小数点后未满3位,补0
123.1245 -> 123.125,小数点只留3位,多余的自动四舍五入截断
12345.12 -> 保存失败,因为小数点未满3位,补0变成12345.120,超过了7位。严格模式下报错,非严格模式存成9999.999
单精度浮点数float占4字节,float标准语法允许通过<code>float(m)</code>的形式指定精度,但是这个精度值m只是决定存储大小: 0-23与默认不指定效果相同,24-53就变成双精度的<code>double</code>了。
float还有非mysql自己实现的非标准语法<code>float(m,d)</code>,代表最多存储m个数字长度,其中小数点后数字个数为d。效果与 decimal(m,d)很相似。
double 和 float 的区别是double精度高,有效数字16位(float精度7位)。但double消耗内存是float的两倍,占8字节,double的运算速度比float慢得多。
可以看到float与float(10)是没区别的,float默认能精确到6位有效数字
float(9,2)与decimal(9,2)是很像的,并没有前面提到24位一下6位有效数字的限制
他们俩之间的差别就在精度上,f_float9_2本应该是 1234567.10,结果小数点变成 .12 。f_decimal9_2因为标度为2,所以 .125 四舍五入成 .13
将 12345.1 插入f_float7_3列,因为转成标度3时 12345.100,整个位数大于7,所以 out of range 了
另外在编程中应尽量避免做浮点数的比较,否则可能会导致一些潜在的问题。
坚决不允许使用float去存money,使用decimal更加稳妥,但使用decimal做除法依旧会产生浮点型,所以特殊情况请考虑使用整型,货币单位使用 分 ,或者除法在最后进行。