博客

mysql float数据类型和decimal数据类型

  • Post author:
  • Post category:mysql


http://yongxiong.leanote.com/post/mysql_float_double_decimal

前言

我们知道在MySQL中有3种类型可以表示实数，分别是float,double和decimal。关于如何合理得使用这三种类型，网上的答案也层出不穷。但是究竟该选择哪一种类型，好像并没有统一的答案，接下来，将通过一个例子来说明什么情况下选择float，什么情况下选择double，什么情况下选择decimal。相信对这个例子的剖析之后，你就会明白什么时候用什么样的类型

举个例子

假如我们有一张表，用来存储用户的积分，表定义如下： 

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

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

 
 
  1. mysql> insert into f value (131072.32);
  2. Query OK, 1 row affected (0.00 sec)
  3. 

  4. mysql> select * from f;
  5. +-----------+
  6. | f1        |
  7. +-----------+
  8. | 131072.31 |
  9. +-----------+
  10. 1 row in set (0.00 sec)

然后会惊奇的发现，这个用户的积分少了0.01，虽然这0.01的积分并不足于引起我们的注意，但是问题还是来了


  1. 丢失数据是否是正常现象？

  2. 为什么会少0.01，有没有可能少0.02，或者少1，少10甚至少100？

  3. 怎么样才能让我们的数据准确？

官方怎么看数据丢失

首先遇到问题，第一想到的就是官方找答案，我们翻阅官方文档，关于float和double有这样一段描述

For FLOAT, the SQL standard permits an optional specification of the precision (but not the range of the exponent) in bits following the keywordFLOAT in parentheses. MySQL also supports this optional precision specification, but the precision value is used only to determine storage size. A precision from 0 to 23 results in a 4-byte single-precision FLOAT column. A precision from 24 to 53 results in an 8-byte double-precision DOUBLEcolumn.

这段话大致可以这样描述：数据的精确度取决于分配给每种数据类型的存储长度，其中float分配了4字节，而double分配了8字节；并且数据的这种不准确是正常现象。采用float和double本来就是不准的！！

实数保存和分配存储长度的关系

在MySQL官方里有这样一句话，数据准确度取决于分配给数据类型存储的长度。在查阅资料可知，单精度类型float和双精度类型double在计算机中存储的时候，由于计算机只能存储二进制，所以浮点型数据在存储的时候，必须转化成二进制。在计算机中，float型数据的存储格式为


图片标题


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


图片标题


我们知道对于float类型的数据，只分配了32位的存储空间，

对于double类型值分配了64位，但是并不是所有的实数都能转成32位或者64位的二进制形式，

如果超过了，就会出现截断，这就是误差的来源。


比如将上面例子中的131072.32转成二进制后的数据为：


100000000000000000.0101000111101011100001010001111010111000010100011111…


这是一个无穷数，对于float类型，只能截取前32位进行存储，对于double只能截取前64位进行存储。所以


131072.32保存为float类型是存储形式为：01001000000000000000000000010100;


131072.32保存为double类型的格式为：0100000100000000000000000000001010001111010111000010100011110101

针对float情况，至少我们可以得出结论：


1. 如果一个float型数据转成二进制后的第32位之后都是0，那么数据是准的


2. 如果一个float型数据转成二进制后的第32位之后不全为0，则数据就会存在误差


##float怎么转换成二进制的过程仍然不清楚，没看懂是怎么转的？造成还是不特别清楚怎么就有误差了。必须知道这个转换过程。看下边的例子熟悉下转换过程。（参考：http://blog.163.com/yql_bl/blog/static/847851692008112013117685/）看理论不如直接来实际的。熟悉了转化流程就知道误差来源了。自己互转一次挺好玩的。



例五：







-12.5


转为单精度二进制表示



12.5:


1.

整数部分

12

，二进制为

1100;

小数部分

0.5,

二进制是

.1

，先把他们连起来，从第一个

1

数起取

24

位（后面补

0

）：




1100

.1



000

0000

0000

0000

0000




这部分是有效数字。（把小数点前后两部分连起来再取掉头前的

1

，就是尾数）



2.

把小数点移到第一个

1

的后面，需要左移

3

位（

1.100

1




000



0000

0000

0000

0000*2^3


）

,

加上偏移量

127

：

127+3=130

，二进制是

10000010

，这是阶码。



3. -12.5

是负数，所以符号位是

1

。把符号位，阶码和尾数连起来。注意，尾数的第一位总是

1

，所以规定不存这一位的

1

，只取后

23

位：



1 10000010 10010000000000000000000



把这

32

位按

8

位一节整理一下，得：



11000001 0100

1000 00000000 00000000




就是十六进制的

C1480000.






例六：


2.025675


1.


整数部分

2

，二进制为

10;

小数部分

0.025675,

二进制是

.

0000

0110

1001

0010

1010

01

，先把他们连起来，从第一个

1

数起取

24

位（后面补

0

）：



10.0000011010010010101001



这部分是有效数字。把小数点前后两部分连起来再取掉头前的

1

，就是尾数

: 00000011010010010101001


2.

把小数点移到第一个

1

的后面，左移了

1

位

,

加上偏移量

127

：

127+1=128

，二进制是

1000

0000


，这是阶码。



3. 2.025675

是正数，所以符号位是

0

。把符号位，阶码和尾数连起来：



0 10000000 00000011010010010101001



把这

32

位按

8

位一节整理一下，得：



01000000 00000001 10100100 10101001



就是十六进制的

4001A4A9.



例七：




(


逆向求十进制整数

)

一个浮点二进制数手工转换成十进制数的例子：







假设浮点二进制数是

1011 1101 0100 0000 0000 0000 0000 0000




按

1

，

8

，

23

位分成三段：





1


01111010



10000000000000000000000



最后一段是尾数。前面加上

“1.”,

就是

1.10000000000000000000000



下面确定小数点位置。由



E = e-Bias



，


阶码

E

是

01111010

，加上

00000101

才是

01111111

（

127

），




所以他减去

127

的偏移量得

e=-5

。（或者化成十进制得

122

，

122-127=-5

）。




因此尾数

1.10

（后面的

0

不写了）是小数点右移

5

位的结果。要复原它就要左移

5

位小数点，得

0.0000110,

即十进制的

0.046875

。




最后是符号：

1

代表负数，所以最后的结果是


-0.046875


。

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

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


  1. 数据的精度总是能精确到D位，也就是数据的不精确一定出现在小数点后

  2. 数据存储的时候只能存储到D位小数

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

 
 
  1. mysql> create table f2 (f1 float(15,2));
  2. Query OK, 0 rows affected (0.01 sec)
  3. 

  4. mysql> insert into f2 values (123456789.39);
  5. Query OK, 1 row affected (0.00 sec)
  6. 

  7. mysql> select * from f2;
  8. +--------------+
  9. | f1           |
  10. +--------------+
  11. | 123456792.00 |
  12. +--------------+
  13. 1 row in set (0.00 sec)

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

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

 
 
  1. mysql> select * from f;
  2. +-----------+
  3. | f1        |
  4. +-----------+
  5. | 131072.31 |
  6. +-----------+
  7. 1 row in set (0.00 sec)
  8. 

  9. mysql> alter table f modify f1 float(10,4);
  10. Query OK, 0 rows affected (0.02 sec)
  11. Records: 0  Duplicates: 0  Warnings: 0
  12. 

  13. mysql> select * from f;
  14. +-------------+
  15. | f1          |
  16. +-------------+
  17. | 131072.3125 |
  18. +-------------+
  19. 1 row in set (0.00 sec)

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

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

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

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


  1. 数据真的准确，数据能在有限的存储空间里完全存储起来

  2. 数据存储被截断，但是通过四舍五入依然能够将数据显示准确

  3. 数据存储被截断，通过四舍五入不能将数字正确显示

关于decimal类型

MySQL中支持浮点数的类型有FLOAT、DOUBLE和DECIMAL类型，DECIMAL 类型不同于FLOAT和DOUBLE，DECIMAL 实际是以串存放的。DECIMAL 可能的最大取值范围与DOUBLE 一样，但是其有效的取值范围由M 和D 的值决定。如果改变M 而固定D，则其取值范围将随M 的变大而变大。


对于精度比较高的东西，比如money，建议使用decimal类型，不要考虑float,double, 因为他们容易产生误差，numeric和decimal同义，numeric将自动转成decimal。


DECIMAL从MySQL 5.1引入，列的声明语法是DECIMAL(M,D)。在MySQL 5.1中，参量的取值范围如下：





·M是数字的最大数（精度）。其范围为1～65（在较旧的MySQL版本中，允许的范围是1～254），M 的默认值是10。


·D是小数点右侧数字的数目（标度）。其范围是0～30，但不得超过M。





说明：float占4个字节，double占8个字节，decimail(M,D)占M+2个字节。




如DECIMAL(5,2) 的最大值为9999.99，因为有7 个字节可用。




所以M 与D 是影响DECIMAL(M, D) 取值范围的关键。


3.23以后的版本的范围最大值和列表中的计算范围不同（经测试不一样）。


例如：
 

DECIMAL(6,2)最大值  9999.99 不是下边列表中计算的最大值。最小值是正确的。


类型说明		取值范围（MySQL < 3.23）	取值范围（MySQL >= 3.23）
DECIMAL(4,1)	-9.9 到 99.9		-999.9 到 9999.9
DECIMAL(5,1)	-99.9 到 999.9		-9999.9 到 99999.9
DECIMAL(6,1)	-999.9 到 9999.9		-99999.9 到 999999.9
DECIMAL(6,2)	-99.99 到 999.99		-9999.99 到 99999.99
DECIMAL(6,3)	-9.999 到 99.999		-999.999 到 9999.999




给定的DECIMAL 类型的取值范围取决于MySQL数据类型的版本。对于MySQL3.23 以前的版本，DECIMAL(M, D) 列的每个值占用M 字节，而符号（如果需要）和小数点包括在M 字节中。因此，类型为DECIMAL(5, 2) 的列，其取值范围为-9.99 到99.99，因为它们覆盖了所有可能的5 个字符的值。


# 在MySQL 3.23 及以后的版本中，DECIMAL(M, D) 的取值范围等于早期版本中的DECIMAL(M + 2, D) 的取值范围。





结论

：


当数值在其取值范围之内，小数位多了，则直接四舍五入并截断小数位。


若数值在其取值范围之外，则用最大(小)值对其填充（当然前提是mysql的sql_mode模式没有启用严格模式，启用了的话 是插不进去的）。

如何选择float，double，decimal

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


1 如果你要表示的浮点型数据转成二进制之后能被32位float存储，或者可以容忍截断，则使用float。

2 如果你要表示的浮点型数据转成二进制之后能被64位double存储，或者可以容忍截断。

3 相比double，已经满足我们大部分浮点型数据的存储精度要求，如果还要精益求精，则使用decimal定点型存储


比如一些科学数据，精度要求很高的金钱。

数据库中float转decimal类型时需要注意：

1、float转decimal时，decimal数值范围要设置的大于原来的float值。否则转换失败或者数据丢失。

2、decimal小范围转大范围没问题，但大范围转小范围会丢失数据。

3、float小范围转大范围没问题，大范围转小范围数据虽然不会丢失。但最好还是别用。

4、最好配置mysql的sql_mode为严格模式。能起到保护的作用。


Tags: