MySQL 服务中浮点型和定点型介绍

2020-12-17 0 By admin

一、浮点类型

浮点数和定点数类型的特点是可以处理小数,你可以把整数看成小数的一个特例。因此,浮点数和定点数的使用场景,比整数大多了。
MySQL支持的浮点数类型,分别是 FLOAT、DOUBLE、REAL。

  1. FLOAT 表示单精度浮点数;
  2. DOUBLE 表示双精度浮点数;
MySQL 浮点型的取值范围
MySQL 浮点型的取值范围

REAL默认就是 DOUBLE。如果你把 SQL 模式设定为启用“REAL_AS_FLOAT”,那 么,MySQL 就认为 REAL 是 FLOAT。
如果要启用“REAL_AS_FLOAT”,可以通过以下 SQL 语句实现:

SET sql_mode = “REAL_AS_FLOAT”;

1.1、深入讨论

问题1:FLOAT 和 DOUBLE 这两种数据类型的区别是啥呢?
答案:FLOAT 占用字节数少,取值范围小;DOUBLE 占用字节数多,取值范围也大。

问题2:为什么浮点数类型的无符号数取值范围,只相当于有符号数取值范围的一半,也就是只相当于有符号数取值范围大于等于零的部分呢?
答案:MySQL 存储浮点数的格式为:符号(S)、尾数(M)和 阶码(E)。
因此,无论有没有符号,MySQL 的浮点数都会存储表示符号的部分。因此,所谓的无符号数取值范围,其实就是有符号数取值范围大于等于零的部分。

1.2、数据精度说明

对于浮点类型,在MySQL中单精度值使用4个字节,双精度值使用8个字节。
MySQL允许使用非标准语法(其他数据库未必支持,因此如果涉及到数据迁移,则最好不要这么用):
FLOAT(M,D)或DOUBLE(M,D)
这里,M称为精度,D称为标度。(M,D)中 M=整数位+小数位,D=小数位。 D<=M<=255,0<=D<=30。

例如,定义为FLOAT(5,2)的一个列可以显示为-999.99-999.99。如果超过这个范围会报错。
FLOAT和DOUBLE类型在不指定(M,D)时,默认会按照实际的精度(由实际的硬件和操作系统决定)来显示。
说明:浮点类型,也可以加UNSIGNED,但是不会改变数据范围,例如:FLOAT(3,2) UNSIGNED仍然只能表示0-9.99的范围。

1.3、浮点型数据存储逻辑

不管是否显式设置了精度(M,D),这里MySQL的处理方案如下:
如果存储时,整数部分超出了范围,MySQL就会报错,不允许存这样的值。
如果存储时,小数点部分若超出范围,就分以下情况:

  1. 若四舍五入后,整数部分没有超出范围,则只警告,但能成功操作并四舍五入删除多余的小数位后保存。例如在FLOAT(5,2)列内插入999.009,近似结果是999.01。
  2. 若四舍五入后,整数部分超出范围,则MySQL报错,并拒绝处理。如FLOAT(5,2)列内插入999.995和-999.995都会报错。

从MySQL 8.0.17开始,FLOAT(M,D) 和DOUBLE(M,D)用法在官方文档中已经明确不推荐使用,将来可能被移除。另外,关于浮点型FLOAT和DOUBLE的UNSIGNED也不推荐使用了,将来也可能被移除。

1.4、精度误差说明

浮点数类型有个缺陷,就是不精准。
MySQL 用 4 个字节存储 FLOAT 类型数据,用 8 个字节来存储 DOUBLE 类型数据。无论哪个,都是采用二进制的方式来进行存储的。
比如 9.625,用二进制来表达,就是 1001.101,或者表达成 1.001101×2^3。
如果尾数不是 0 或 5(比如 9.624),你就无法用一个二进制数来精确表达。进而,就只好在取值允许的范围内进行四舍五入。

在编程中,如果用到浮点数,要特别注意误差问题,

  1. 因为浮点数是不准确的,所以我们要避免使用“=”来判断两个数是否相等。
  2. 同时,在一些对精确度要求较高的项目中,千万不要使用浮点数,不然会导致结果错误,甚至是造成不可挽回的损失。

那么,MySQL 有没有精准的数据类型呢?当然有,这就是定点数类型:DECIMAL。

二、定点数类型

MySQL中的定点数类型只有 DECIMAL 一种类型。
定点数在MySQL内部是以字符串的形式进行存储,这就决定了它一定是精准的。
当DECIMAL类型不指定精度和标度时,其默认为DECIMAL(10,0)。当数据的精度超出了定点数类型的精度范围时,则MySQL同样会进行四舍五入处理。

数据类型 字节数 含义
DECIMAL(M,D),DEC,NUMERIC M+2字节 有效范围由M和D决定

使用 DECIMAL(M,D) 的方式表示高精度小数。
其中,M被称为精度,D被称为标度。0<=M<=65,0<=D<=30,D<M。例如,定义decimal(5,2)的类型,表示该列取值范围是-999.99~999.99。
decimal(m,d)的最大取值范围与double类型一样,但是有效的数据范围是由m和d决定的。
decimal 的存储空间并不是固定的,由精度值m决定,总共占用的存储空间为m+2个字节。
也就是说,在一些对精度要求不高的场景下,比起占用同样字节长度的定点数,浮点数表达的数值范围可以更大一些。

2.1、浮点数 vs 定点数

浮点数相对于定点数的优点是在长度一定的情况下,浮点类型取值范围大,但是不精准,适用于需要取值范围大,又可以容忍微小误差的科学计算场景(比如计算化学、分子建模、流体动力学等)
定点数类型取值范围相对小,但是精准,没有误差,适合于对精度要求极高的场景 (比如涉及金额计算的场景)