MySQL 数据类型之 浮点型和定点型

MySQL 中使用浮点数和定点数来表示小数。

浮点类型有两种，分别是单精度浮点数（FLOAT）和双精度浮点数（DOUBLE）；定点类型只有一种，就是 DECIMAL。

浮点型 FLOAT、DOUBLE、REAL

FLOAT、DOUBLE 的区别

数据类型	字节数	有符号的取值范围	无符号的取值范围	备注
FLOAT(M, D)	4	(-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38)	0，(1.175 494 351 E-38，3.402 823 466 E+38)	单精度浮点数，7位有效数字。
DOUBLE(M, D)	8	(-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	双精度浮点数，15位有效数字。

REAL 默认就是 DOUBLE。如果把 SQL 模式设定为启用 REAL_AS_FLOAT，那么 MySQL 就认为 REAL 是 FLOAT。如果要启用 REAL_AS_FLOAT，可以使用 SET sql_mode = “REAL_AS_FLOAT”;

1. MySQL允许使用 非标准语法 （其他数据库未必支持，因此如果涉及到数据迁移，则最好不要这么用）：FLOAT(M,D) 或 DOUBLE(M,D)。M 称为精度，D 称为标度。其中：

   • M 称为精度，表示总共的位数，取值范围为 M（1～255）
   • D 称为标度，表示小数的位数，取值范围为 D（1～30，且不能大于 M-2）

2. M 和 D 在 FLOAT 和 DOUBLE 中是可选的，FLOAT 和 DOUBLE 在不指定精度时，默认会按照实际的精度（由计算机硬件和操作系统决定），windows的默认如下：

   • FLOAT 默认精度为 6(除去小数点)，如果超过6位，则四舍五入。
     • 如 存入 1.123456 会变成 1.12346
     • 如 存入 12345267 会变成 12345300 ，前5位是准确的，第6位是四舍五入的结果。
   • DOUBLE 默认精度为17(除去小数点)，如果超过17位，则四舍五入。
     • 如 存入 1.12345678901234567 会变成 1.1234567890123457
     • 如 存入 12345678901234567890 会变成 12345678901234567000 ，前17位是准确的，第17位是四舍五入的结果。

3. 不管是否显式设置了精度(M,D)，这里MySQL的处理方案如下。

   • 如果存储时，整数部分超出了范围（如FLOAT(5,2)，添加数值为1000.01），MySql就会报错，不允许存这样的值。
   • 如果存储时，小数点部分若超出范围，就分以下情况：
     • 若四舍五入后，整数部分没有超出范围，则只警告，但能成功操作并四舍五入删除多余的小数位后保存，如FLOAT(5,2)列内插入999.009，近似结果是999.01。
     • 若四舍五入后，整数部分超出范围，则MySql报错，并拒绝处理。如FLOAT(5,2)列内插入999.995和-999.995都会报错。

4. 小数类型，也可以加unsigned，但是不会改变数据范围，例如：float(3,2) unsigned仍然只能表示0-9.99的范围。

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

问题

1. 为什么浮点数类型的无符号数取值范围，只相当于有符号数取值范围的一半，也就是只相当于有符号数取值范围大于等于零的部分呢？

   MySQL 存储浮点数的格式为： 符号(S)、尾数(M)和阶码(E)。因此，无论有没有符号，MySQL 的浮点数都会存储表示符号的部分。因此， 所谓的无符号数取值范围，其实就是有符号数取值范围大于等于零的部分。

精度误差说明

浮点数类型有个缺陷，就是不精准。比如，我们设计一个表，有f1这个字段，插入值分别为0.47,0.44,0.19，我们期待的运行结果是：0.47 + 0.44 + 0.19 = 1.1。而使用sum之后查询：

CREATE TABLE test_double2(
f1 DOUBLE 
);
INSERT INTO test_double2 VALUES(0.47),(0.44),(0.19);

select sum(f1) from test_double2

查询结果是 1.0999999999999999。虽然误差很小，但确实有误差。你也可以尝试把数据类型改成 FLOAT，然后运行求和查询，得到的是 1.0999999940395355。显然，误差更大了。

那么，为什么会存在这样的误差呢？问题还是出在 MySQL 对浮点类型数据的存储方式上。

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

IEEE754标准：内存中，FLOAT占4-byte（1位符号位 8位表示指数 23位表示尾数），DOUBLE占8-byte（1位符号位 11位表示指数 52位表示尾数）。IEEE754标准还对尾数的格式做了规范：d.dddddd…，小数点左面只有1位且不能为零，计算机内部是二进制，因此，尾数小数点左面部分总是1。显然，这个1可以省去，以提高尾数的精度。由上可知，单精度浮点数的尾数是用24bit表示的，双精度浮点数的尾数是用53bit表示的。所以就能算出取值范围和准确的有效位数了，但MySql中其实略有不同。

定点型

因为 float、double类型存在精度丢失问题，即写入数据库的数据未必是插入数据库的数据，那么，MySQL 有没有精准的数据类型呢？当然有，这就是定点数类型：DECIMAL。

• MySQL中的定点数类型只有 DECIMAL 一种类型。

• 使用 DECIMAL(M,D) 的方式表示高精度小数。其中，M被称为精度，D被称为标度。0<=M<=65， 0<=D<=30，D<M。例如，定义DECIMAL（5,2）的类型，表示该列取值范围是-999.99~999.99。

• 当DECIMAL类型不指定精度和标度时，其默认为DECIMAL(10,0)。当数据的精度超出了定点数类型的精度范围时，则MySQL同样会进行四舍五入处理。

• DECIMAL(M,D)的最大取值范围与DOUBLE类型一样，但是有效的数据范围是由M和D决定的。DECIMAL 的存储空间并不是固定的，由精度值M决定，总共占用的存储空间为M+2个字节。也就是说，在一些对精度要求不高的场景下，比起占用同样字节长度的定点数，浮点数表达的数值范围可以更大一些。

• DECIMAL在MySQL内部是将数据分为两个部分来存储【整数、小数部分分开存储】, 每个部分都是整数，这就决定了它一定是精准的。

选择

• 浮点数相对于定点数的优点是在长度一定的情况下，浮点数能够表示更大的范围；缺点是会引起精度问题。

• 在 MySQL 中，定点数以字符串形式存储，在对精度要求比较高的时候（如货币、科学数据），使用 DECIMAL 的类型比较好

• 两个浮点数进行减法和比较运算时也容易出问题，所以在使用浮点数时需要注意，并尽量避免做浮点数比较。