本文主要是介绍MySQL数据库中的浮点类型和高精度类型有什么区别?为什么不推荐使用浮点类型?,希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!
在软件开发中,作为后端,无可避免的需要熟练使用 MySQL 数据库进行数据存储和读取。对于信息系统而言,数据库的的地位不言而喻。那作为软件开发工程师,在使用 MySQL 过程中,又有哪些需要注意的呢?我们从实际开发来一点点的介绍。
本篇文章,我们先来了解一下关于数据库数字类型的一些内容。我们在做表设计的时候,数字类型是常见的数据类型,用于存储数字相关的信息(整数型、浮点型、高精度型)。但是在不同的业务场景中,错误的使用数字类型,也会给系统带来很大的风险。
一、数字类型
1、整数类型
MySQL 数据库支持 SQL 标准支持的整型类型:INT、SMALLINT。此外,MySQL 数据库也支持诸如 TINYINT、MEDIUMINT 和 BIGINT 整型类型。我们来看一下各整型所占用的存储空间及取值范围:
类型 | 占用空间 | 最小值-最大值(signed) | 最小值-最大值(unsigned) |
---|---|---|---|
TINYINT | 1 | -128~127 | 0~255 |
SMALLINT | 2 | -32768~32768 | 0~65535 |
MEDIUMINT | 3 | -8388608~8388607 | 0~16777215 |
INT | 4 | -2147483648~2147483647 | 0~4294967295 |
BIGINT | 8 | -9223372036854775808~9223372036854775807 | 0~18446744073709551615 |
在整型数字类型中,有 signed 和 unsigned 的属性,表示的是整型的取值范围。默认是 signed。我们在设计时,建议不要刻意去用 unsigned 属性,因为在做数据分析时,SQL 可能会返回不是理想的结果。
2、浮点类型和高精度类型
MySQL 的数字类型中,除了上面说的整数类型,还有浮点型和高精度型。
MySQL 之前的版本中存在浮点类型 Float 和 Double,但这些类型因为不是高精度,也不是 SQL 标准的类型,所以在真实的生产环境中不推荐使用,否则在计算时,由于精度类型问题,会导致最终的计算结果出错。
更重要的是,在 MySQL8.0.17 版本开始,如果我们建表继续使用 FLOAT 和 DOUBLE,则会抛出警告:
Specifying number of digits for floating point data types is deprecated and will be removed in a future release
数字类型中的高精度 DECIMAL 类型可以使用,在设置字段为 DECIMAL 类型时,需要指定精度和标度。例如:
count DECIMAL(6,4)
其中,6 是精度(精度表示保存值的主要位数),4 是标度(标度表示小数点后面保存的位数)。通常在表结构设计中,类型 DECIMAL 可以用来表示用户的工程款项、账户的余额等精确到小数点后 4 位的业务。
然而,在海量并发的互联网业务中使用,金额字段的设计并不推荐使用 DECIMAL 类型,而更推荐使用 INT 整型类型。
二、表结构设计实战
1、整型与主键自增设计
在真实业务场景中,整型类型最常见的就是在业务中用来表示某件物品的数量。例如销售数量,或电商中的库存数量、购买次数等。在业务中,整型类型的另一个常见且重要的使用用法是作为表的主键,即用来唯一标识一行数据。
整型结合属性 auto_increment,可以实现自增功能,但在表结构设计时用自增做主键,特别要注意以下两点,若不注意,可能会对业务造成灾难性的打击:
-
用 BIGINT 做主键,而不是 INT;
-
自增值并不持久化,可能会有回溯现象(MySQL 8.0 版本前)。
从上述的类型对比中可以发现,INT 的最大范围在 42 亿级别,在互联网应有中,很容易就达到这个量级。比如一些日志表、浏览记录表等,每天 1000w 的数据增量,420 天后就达到了 INT 上限。
因此,用自增型做主键,应该使用 BIGINT,而不是 INT。
另外,MySQL8.0 以前的版本,自增不持久化,自增值可能会存在回溯问题。
我们以以下实例来看:
mysql> SELECT * FROM t;+---+| a |+---+| 1 || 2 || 3 |+---+3 rows in set (0.01 sec)mysql> DELETE FROM t WHERE a = 3;Query OK, 1 row affected (0.02 sec)mysql> SHOW CREATE TABLE t\G*************************** 1. row ***************************Table: tCreate Table: CREATE TABLE `t` (`a` int NOT NULL AUTO_INCREMENT,PRIMARY KEY (`a`)) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci1 row in set (0.00 sec
从上述代码可以看出,在删除自增为 3 的记录后,下一个自增依然为 4,这里没有问题,自增不会回溯。但如果此时数据库发生重启,那数据库启动后,表 t 的自增起始值会再次变为 3,这就是自增值发生回溯。
mysql> SHOW CREATE TABLE t\G*************************** 1. row ***************************Table: tCreate Table: CREATE TABLE `t` (`a` int NOT NULL AUTO_INCREMENT,PRIMARY KEY (`a`)) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci1 row in set (0.00 s
如果想解决这个问题,有以下两个做法:
-
1、升级 MySQL 到 8.0 版本,让每个表的自增值持久化。
-
2、如无法升级版本,则不推荐在核心业务表使用自增数据类型做主键。
其实在海量的互联网架构设计中,为了后续分布式架构更好的扩展,一般是不使用整型来做主键的,更为推荐的是使用字符串类型。
2、资金字段设计
在用户余额、基金账户余额、数字钱包、零钱等的业务设计中,由于字段都是资金字段,通常技术人员习惯使用 DECIMAL 类型作为字段的选型,因为这样可以精确到分,如:DECIMAL(6,2)。
CREATE TABLE User (userId BIGINT AUTO_INCREMENT,money DECIMAL(8,2) NOT NULL,......)
在海量互联网业务的设计标准中,并不推荐用 DECIMAL 类型,而是更推荐将 DECIMAL 转化为 整型类型。也就是说,资金类型更推荐使用用分单位存储,而不是用元单位存储。如 1 元在数据库中用整型类型 100 存储。
金额字段的取值范围如果用 DECIMAL 表示的,如何定义长度呢?因为类型 DECIMAL 是个变长字段,若要定义金额字段,则定义为 DECIMAL(8,2) 是远远不够的。这样只能表示存储最大值为 999999.99,百万级的资金存储。
用户的金额至少要存储百亿的字段,而统计局的 GDP 金额字段则可能达到百万亿级别。用类型 DECIMAL 定义,不好统一。
另外重要的是,类型 DECIMAL 是通过二进制实现的一种编码方式,计算效率远不如整型来的高效。因此,推荐使用 BIG INT 来存储金额相关的字段。
字段存储时采用分存储,即便这样 BIG INT 也能存储千兆级别的金额。这里,1 兆 = 1 万亿。
文章将持续更新,欢迎关注公众号:服务端技术精选。欢迎点赞、关注、转发。
这篇关于MySQL数据库中的浮点类型和高精度类型有什么区别?为什么不推荐使用浮点类型?的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!