【MySQL】在MySQL中，涉及到金钱的字段一般用什么数据类型？

vlambda
2020-06-13

【MySQL】在MySQL中，涉及到金钱的字段一般用什么数据类型？

我们知道，如果要存储小数的话，MySQL中有double\float\decimal三种数据类型供我们挑选。当我们存储的小数代表金钱的时候，我们又该如何选择这些数据类型呢？

单精度型浮点数（float）是一个占用4字节共32位的数据类型，由符号位、指数位、尾数位组成，最高位（31位）为符号位，表示正负值，指数位则是23~30位，范围为-128~127，尾数位是0~22位，表示小数部分，尾数位有23位，2^23 = 8388608，一共7位，这意味着最多能有7位有效数字，但能保证的为6位，也即float的精度为6~7位有效数字。float数值范围为-3.4E38~3.4E38。

双精度型浮点数（double）是一个占用8字节64位的数据类型，由符号位、指数位、尾数位组成，最高位（63位）为符号位，表示正负值，指数位则是51~62位，范围为-1024~1023，尾数位是0~50位，表示小数部分。尾数位有52位，2^52 = 4503599627370496，一共16位，double的精度为15~16位有效数字，这意味着最多能有16位有效数字，但能保证的为15位。double数值范围-1.7E308~1.7E308。

decimal从MySQL 5.1引入，列的声明语法是DECIMAL(M,D)，其中M表示有效数字数，范围为1〜65，默认值是10。D是表示小数点后的位数。D的范围是0~30，默认为零，且要求D<=M。例如，DECIMAL(6,2)表示最多可以存储6位数字，小数位数为2位，取值范围为从-9999.99到9999.99。

float double定义金钱字段存在什么问题？

首先，我们先来看看使用float数据类型存储金钱，假如我们有一张表，用来存储用户的余额，表定义如下：

CREATE TABLE `f` ( `f1` float(10,2) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf-8

然后向这个表里插入131072.32的积分值，如下所示：

mysql> insert into f value (131072.32);Query OK, 1 row affected (0.00 sec)mysql> select * from f;+-----------+| f1 |+-----------+| 131072.31 |+-----------+1 row in set (0.00 sec)

接着我们查询到的值为131072.31，那么问题来了，用户的金额居然少了0.01分，虽然是0.01分钱，但是我们作为东家，必须做到保障用户一分钱也不能少。

float double为什么存在精度丢失问题？

那么为什么会出现精度问题呢？我们先来看下一直0.3的双精度数字是如何存储在计算机中的。任何字符存储在计算机中都是以二进制形式存储的。而浮点数转化为二进制的计算过程是：将小数乘以2，去除整数部分作为二进制表示，即整数部分大于1为1，小于1为0，然后继续将小数部分乘以2，直到小数部分为0。例如将一个0.3的小数转为二进制：

0.3 * 2 = 0.6 ——————- 0

0.6 * 2 = 1.2 ——————- 1

0.2 * 2 = 0.4 ——————- 0

0.4 * 2 = 0.8 ——————- 0

0.8 * 2 = 1.6 ——————- 1

0.6 * 2 = 1.2 ——————- 1

…………

我们可以发现在该计算中已经出现了循环，0.3用二进制表示为 0100 1100 1100 1100 .... 小数部分出现无限循环，计算机有限的空间无法存储这么多位，则只能舍弃后面的位数，通过有限的位数存储下来得到一个近似值。用有限的二进制位无法准确表示一个小数，这也是在编程语言中表示小数会出现误差的原因。

而一个二进制的小数转化为十进制的过程是：从左到右，依次为每位乘以2^( - i )， i从左到右依次增长。我们再将一个0.3的二进制的数转为十进制：

1001 1001 1001 1001

0 * 2^-1 + 1 * 2^-2 + 0 * 2^-3 + 0 * 2^-4 + 0 * 2^-5 + ……

= 0 * 0.5 + 1*0.25 + 0 + 0 * 1/16 + 0 * 1/32 + ……

= 0 + 0.25 + 0 + ……

=0.2999999999999998897....

所以我们发现，一个浮点数只能保证近似值的精确。同样的问题，在double中也会出现。这是因为存储时如果是float和double，那么会尽可能以近似的值存储，以保证精度。

哪些类型可以定义对精度有要求的字段

而如果是decimal来存储，就能保证准确的精度了，因为decimal内部以字符形式存储小数，属于准确存储，保证数字精度不会丢失的。假如我们有一张表，用来存储用户的余额，表定义如下：

CREATE TABLE `dc` ( `dc1` decimal(10,2) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf-8

然后向这个表里插入131072.32的积分值，如下所示：

mysql> insert into dc value (131072.32);Query OK, 1 row affected (0.00 sec)mysql> select * from dc;+-----------+| dc1 |+-----------+| 131072.32 |+-----------+1 row in set (0.00 sec)

我们可以发现精度得于保证，综上所述，建议大家使用decimal类型来定义金额类型的字段。

除了decimal之外，我们还可以选择整型来表示金钱字段，例如1.01元用101分表示，整型的存储和计算就不会出现精度丢失问题了。

end

往期推荐