整数类型
整数类型 | 字节 | 有符号数取值范围 | 无符号数取值范围 |
---|---|---|---|
tinyint | 1 | -128 ~ 127 | 0 ~ 255 |
smallint | 2 | -32768 ~ 32767 | 0 ~ 65535 |
mediumint | 3 | -8388608 ~ 8388607 | 0 ~ 16777215 |
int、integer | 4 | -2147483648 ~ 21474836647 | 0 ~ 4294967295 |
bigint | 8 | -923372036854775808 ~ 923372036854775807 | 0 ~ 18446744073709551615 |
补充知识:
- ZEROFILL:0填充。配合宽度一起使用,但现已不建议如此使用了。
注:在mysql5.7中整数类型仍然显示宽度,但在mysql8中此宽度被取消。 - 适用场景
tinyint:一般用于枚举数据,比如系统设定取值范围很小且固定的场景
smallint:可以用于较小范围的统计数据,比如统计工厂的固定资产库存数量等
mediumint:用于较大整数的计算,比如车站每日的客流量等
Int、integer:取值范围足够大,一般情况下不用考虑超限问题,用的最多,如商品编号
Bigint:只有当你处理特别巨大的整数才会用到。比如双十一的交易量,大型门户网站点击量等。 - 如何选择
在评估用哪种整数类型的时候,需要考虑存储空间和可靠性的平衡问题:一方面,用占用字节少的整数类型可以节省存储空间;另一方面,要是为了节省存储空间,使用的整数类型取值范围太小,一旦遇到超出取值范围的情况就可能引起系统错误,影响可靠性。
需要注意的是,在实际工作中,系统故障产生的成本远远超过增加几个字段存储空间所产生成本。
浮点类型
- Float表示单精度浮点类型 占用字节数4
- Double表示双精度浮点数 占用字节数8
注:浮点数是不准确的,避免使用=来判断两个数是否相等。
定点数类型
Decimal(M,D),DEC,NUMERIC M+2字节数 有效范围由,和D决定
- 使用decimal(M,D)的方式表示高精度小数,其中,M被称为精度,D被称为标度。0<=M<=65,0<=D<=30,D<M
- DECIMAL(M,D)的最大取值范围与double类型一样,但是有效的数据范围是由M和D决定的,DECIMAL的存储空间并不是固定的,由精度值,决定,总共占用的存储空间为M+2字节。也就是说,在一些对精度要求不高的场景下,比起占用同样字节长度的定点数,浮点数表达的取值范围更大一些
- 定点数在mysql内部是以字符串形式进行存储的,这就决定了它一定是精准的。
- 当decimal类型不指定精度和标度时,其默认为decimal(10,0),当数据的精度超出了定点数类型的精度范围时,则mysql同样会进行四舍五入处理。
浮点数vs定点数
- 浮点数相对于定点数的优点是在长度一定的情况下,浮点数类型取值范围大,但是不精准,适用于需要取值范围大,又可以容忍微小误差的科学计算场景。
- 定点数类型取值范围相对小,但是精准,没有误差,适合对于精度要求极高的场景,比如金额计算的场景。
字符串类型
类型 | 特点 | 空间上 | 时间上 | 适用场景 |
---|---|---|---|---|
Char(M) | 固定长度 | 浪费存储空间 | 效率高 | 存储不大,速度要求高 |
Varchar(M) | 可变长度 | 节省存储空间 | 效率低 | 非char的情况 |
- 情况1:存储很短的信息,比如门牌号码这样的很短的信息应该用char,因为varchar还要占个buyte存储信息长度,本来打算节约存储的,结果得不偿失。
- 情况2:固定长度的,比如使用uuid作为主键,那用char应该更合适,因为他固定长度,varchar动态根据长度的特性就消失了,而且还要占个长度信息。
- 情况3:十分频繁改变的column。因为varchar每次存储都要有额外的计算,得到长度等工作,如果一个非常频繁改变的,那就要有很多用于计算,而这些对于char来说是不需要的
- 情况4:具体存储引擎中的情况。
- InnoDB存储引擎,建议使用varchar类型,因为对于innoDB数据表,内部的行存储格式并没有区分固定长度和可变长度列(所有数据行都使用指向数据列的头指针),而且主要影响性能的因素是数据行使用的存储总量,由于char平均占用的空间多于varchar,所以除了简短并且固定长的,其他考虑varchar。这样节省空间,对磁盘I/O和数据存储总量比较好。
TEXT文本类型
开发中的经验
Text文本类型,可以存储比较大的文本段,搜索速度稍慢,因此如果不是特别大的内容,建议使用char,varchar来代替,还有TEXT类型不用加默认值,加了也没用。而且text和blob类型的数据删除后容易导致”空洞”,使得文件碎片比较多,所以频繁使用的表不建议包含text类型的字段,建议单独分出去,单独使用一个表。
Text和Blob的使用注意事项
- Blob和text值也会引起自己的一些问题,特别是执行了大量的删除或更新操作的时候,删除这种值会在数据表中留下很大的“空洞”,以后填入这些“空洞”的记录可能长度不同,为了提高性能,建议定期使用OPTIMIZE tabel功能对这类表进行碎片整理。
- 如果需要对大文本字段进行模糊查询,mysql提供了前缀索引,但是仍然要在不必要的时候避免检索大型的blob或者text值
- 把blob或text列分离到单独的表中,在某些环境中,如果把这些数据列移动到第二张数据表中,可以让你把原数据表中的数据列转换为固定长度的数据行格式,那么它就是有意义的。这会减少主表中的碎片,使你得到固定长度数据行的性能优势。
小结以及类型选择建议
- 【强制】小数类型为DECIMAL,禁止使用FLOAT和DOUBLE
- 说明:在存储的时候,FLOAT和DOUBLE都存在精度损失的问题,很可能在比较值的时候,得到不正确的结果。如果存储的数据范围超过DECIMAL的范围,建议将数据拆成整数和小数并分开存储。
- 【强制】如果存储的字符串长度几乎相等,使用char定长字符串类型
- 【强制】VARCHAR是可变长字符串,不预先分配存储空间,长度不要超过5000.如果存储长度大于此值,定义字段为TEXT,独立出来一张表,用主键来对应,避免影响其他字段索引效率。