MySQL DataType--定点数(Fixed-Point Types)学习

DECIMAL和NUMERIC

MySQL支持两种定点数类型：DECIMAL和NUMERIC，而NUMERIC实现为DECIMAL，因此MySQL中DECIMAL和NUMERIC等价相同。

如使用下面建表语句：

CREATE TABLE tb003(
id INT PRIMARY KEY,
c1 DECIMAL(20,5),
c2 NUMERIC(18,5)
)ENGINE=INNODB DEFAULT CHARSET=utf8;

表创建完成后显示的建表语句：

CREATE TABLE `tb003` (
`id` INT(11) NOT NULL,
`c1` DECIMAL(20,5) DEFAULT NULL,
`c2` DECIMAL(18,5) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

当列被定义为DECIMAL(5,2)时，5为精度，2为刻度，精度表示值存储的有效位数，刻度表示小数点后可存储的位数。
当列被定义为DECIMAL(5,2)时，该列存储范围为-999.99值999.99。

 

定点数存储空间

定点数在存储时将整数部分和小数部分分开存储，每9位10进制数据需要4个byte来存储，不满9位的部分使用不同字节来存储：

如列类型为DECIMAL(30,4)时：

1、整数部分长度=30-4=26=9+9+8，整数部分需要存储空间=4+4+4=12
2、小数部分长度=4，小数部分需要存储空间=2
因此列类型为DECIMAL(30,4)时，其所需存储空间为12+2=14。

PS1:在查看执行计划时，key_len列表示索引列的长度，对于二级索引，该长度仅为索引列的长度，不包含主键列长度。在类型为DECIMAL(30,4)  NOT NULL 的列上建索引，则key_len列显示为14。如果类型为DECIMAL(30,4)  NULL的列上建索引，则key_len显示为15，需要额外的1个字节表示是否为NULL

 

存储空间溢出问题

在严格SQL模式下(sql_mode = 'TRADITIONAL'),发生存储值溢出问题会报错且操作无法执行成功。
在非严格SQL模式下(sql_mode = 'TRADITIONAL')，发生存储值溢出问题时会有警告(1265,Data truncated)且操作能执行完成,但：
1、如果值超过存储范围，则会当做该类型最大值处理，如对类型DECIMAL(5,2)的列插入10000，最终插入值为999.99。
2、如果小数部分超过存储范围，则会进行四舍五入处理，如对类型DECIMAL(5,2)的列插入10.555，最终插入值为10.56。

 

使用BIGINT来存放“定点数”

某些文章中会以各种理由而建议使用BIGINT类型来存放与钱相关的数据：
理由1：使用对于DECIMAL(18,2)来存放时，会导致某些统计数据不准如银行计算利息。
这并不是DECIMAL的问题，而是程序设计不严谨导致，以存钱利息为例，单个用户的利息计算最终精确到分，分以后部分被抹去，但统计多个用户时，
分以后部分又被累计相加，导致总金额"变多"。
解决办法：增大数据类型精度，调整统计算法。

理由2：使用BIGINT能有效降低存储空间
BIGINT使用8字节来存储数据，数据范围为-9223372036854775808 到 9223372036854775807(922亿亿,整数部分19位),
如果我们将数据扩大10000倍来存放，那么BIGINT类型对应存放数据类型为DECIMAL(19,4)，其存储空间为10字节，降低2个字节。

理由3：不同数值类型计算对CPU的消耗不同。
以主流服务器CPU运算能力来看，不同数值类型造成的CPU影响有限。

 

受FLOAT和DOUBLE两种浮点数类型不能精确存储数据的影响，导致部分研发在不明真相情况下会排斥使用DECIMAL类型，但存在即合理，每种数据类型都有其优缺点和适用场景