Data

MySQL中的float、double的精度是如何丢失的？

前言

朋友在设计表的时候很疑惑小数的时候到底该用Float、Double还是Decimal，什么情况下使用？
我们总听说Float、Double会丢失精度，如果是金钱则使用Decimal。但是在业务场景里面，我们期望的是程序是可靠的，所有数据都是准确的。那是不是意味着所有的字段都要用Decimal,那Float、Double还有什么用？
所以我们需要理解到精度到底是怎么丢失的，什么情况下丢失，什么情况下不丢失？才能得出Float、Double在怎样情况下是可靠的，才能在需要使用的时候判断出该使用什么数据类型。

Float为什么会丢失？

Float、Double存储的是近似值。为什么是近似值，先看看各数据类型空间占用情况

类型名称 说明 存储需求
Float 单精度浮点数 4字节
Double 双精度浮点数 8字节
Decimal 压缩的“严格”定点数 Decimal(M,D)，如果M>D，为M+2否则为D+2字节

存储Float、Double时采用将数据转换为二进制进行存储。
存储格式为

比如8.25用二进制表示可表示为1000.01，转成指数的形式1.00001*2^3,在计算机中

这其中小数的二进制计算方式与整数不同，需要使用小数部分2取整数，直到为0
例如0.32的二进制计算方式如下
0.322 = 0.64 0
0.642 = 1.28 1
0.282 = 0.56 0
0.562 = 1.12 1
0.122 = 0.24 0
0.242 = 0.48 0
0.482 = 0.96 0
0.962 = 1.92 1
0.922 = 1.84 1
0.842 = 1.68 1
0.682 = 1.36 1
0.36*2 = 0.72 0
…

对于这样整除不尽或者超过32位的情况，就一定会丢失精度，或者四舍五入后得到的近似值
针对float情况，至少我们可以得出结论：
1.如果一个float型数据转成二进制后的第32位之后都是0，那么数据是准的
2.如果一个float型数据转成二进制后的第32位之后不全为0，则数据就会存在误差

重新说明float(M, D)两个参数的意义

这两个参数表示一共能存M位，其中小数点后占D位。比如float(3,1)表示一共3位，其中小数点后1位数字。这里会有两个误区

数据的精度总是能精确到D位，也就是数据的不精确一定出现在小数点后
数据存储的时候只能存储到D位小数

  • 第一个误区，如果对于float4字节的存储空间连整数的存储不下的时候，连整数都有误差的，更何况小数，所以存储空间大小决定存储精度，和D值无关。来看这样一个例子

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    mysql> create table f2 (f1 float(15,2));
    Query OK, 0 rows affected (0.01 sec)
    mysql> insert into f2 values (123456789.39);
    Query OK, 1 row affected (0.00 sec)
    mysql> select * from f2;
    +--------------+
    | f1           |
    +--------------+
    | 123456792.00 |
    +--------------+
    1 row in set (0.00 sec)

    最后你会发现，连整数都不准了，小数被完全抹去了。

  • 第二个误区，对于存储而言，是和D无关的一个参数。因为浮点型数据最终都要被转成二进制进行存储。并且对于float，这个二进制只能有32位0和1的组合。看下面的例子：

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    mysql> select * from f;
    +-----------+
    | f1        |
    +-----------+
    | 131072.31 |
    +-----------+
    1 row in set (0.00 sec)
    mysql> alter table f modify f1 float(10,4);
    Query OK, 0 rows affected (0.02 sec)
    Records: 0  Duplicates: 0  Warnings: 0
    mysql> select * from f;
    +-------------+
    | f1          |
    +-------------+
    | 131072.3125 |
    +-------------+
    1 row in set (0.00 sec)

    可以看到，修改一下显示宽度D，这个时候可以看到MySQL真正存储的数字是131072.3125

怎么样才能存储一个准确的数据

如果采用float或者double类型的话，数据有时候完全准确的，有时候是不准确的，怎么才能存储一个准确的数字，完全看你需要存什么样的数据，假如存储一个8.25这样的数字，那永远都是准确的。但是如果存储0.9这样的数字，则永远存不准确。

所以如果一个实数在MySQL中存储准确的话，会出现以下三种情况

  • 数据真的准确，数据能在有限的存储空间里完全存储起来
  • 数据存储被截断，但是通过四舍五入依然能够将数据显示准确
  • 数据存储被截断，通过四舍五入不能将数字正确显示

关于decimal类型

通过前面的分析，了解了float和double类型的区别和误差来源。但是decimal类型是MySQL官方唯一指定能精确存储的类型，也是DBA强烈推荐和金钱相关的类型都要存储为decimal类型，如果猜想decimal类型的存储格式的话，那么一下两种可以保持数据的准确性

  • 继续扩大存储空间，比double更大一个级别，比如128位甚至更多
  • 通过字符串化或者其他的方式特殊存储起来

这两种方式都能实现decimal精确存储，但是由于MySQL指定decimal类型最大长度为65.在我们能测试的范围内，decimal并没有出现误差。

如何选择float，double，decimal

结论总是放在最后，根据上面的分析：可以得出以下结论

  1. 如果你要表示的浮点型数据转成二进制之后能被32位float存储，或者可以容忍截断，则使用float，这个范围大概为要精确保存6位数字左右的浮点型数据 比如10分制的店铺积分可以用float存储，小商品零售价格(1000块之内)

  2. 如果你要表示的浮点型数据转成二进制之后能被64位double存储，或者可以容忍截断，这个范围大致要精确到保存13位数字左右的浮点型数据 比如汽车价格,几千万的工程造价

  3. 相比double，已经满足我们大部分浮点型数据的存储精度要求，如果还要精益求精，则使用decimal定点型存储 比如一些科学数据，精度要求很高的金钱

写在最后

理论上的东西永远比不上实践，应用场景大于一切理论。选择float或者double或者decimal有时候也要看场景，比如我们可以用double存储一个小商铺的季度营业额（几千万），单独用double存储的时候没有问题，当多个季度，多个年份算总3年内的营业额是，就会出现问题，再也算不出一个准确的答案。所以，如果考虑情况没那么有把握的情况下，推荐使用decimal，最后，也可以通过其他手段避开这些问题，比如存储商品价格可以使用 乘以100的形式存储，展示价格的时候再除以100

B.3.4.8 Problems with Floating-Point Values
谈谈MySQL如何选择float, double, decimal