06｜第六话：基础篇-MySQL数据类型之数值类型

上一话说完了时间类型，本篇文章我们来说说数值类型。

众所周知，数字可以分为整数和小数两大基本类型，MySQL中也是如此，只是小数具体又分为了浮点数和定点数。

因此，MySQL中的数值型数据类型主要分为3类：整数、定点数、浮点数。

本篇文章整体脉络如下：

一、整数类型

根据表示一个数占用字节数的不同，MySQL把整数划分成如下所示的类型：

类型	占用的存储空间（单位：字节）	无符号数取值范围	有符号数取值范围	含义
TINYINT	1	0 ~ 2⁸-1	-2⁷ ~ 2⁷-1	非常小的整数
SMALLINT	2	0 ~ 2¹⁶-1	-2¹⁵ ~ 2¹⁵-1	小的整数
MEDIUMINT	3	0 ~ 2²⁴-1	-2²³ ~ 2²³-1	中等大小的整数
INT（别名：INTEGER）	4	0 ~ 2³²-1	-2³¹ ~ 2³¹-1	标准的整数
BIGINT	8	0 ~ 2⁶⁴-1	-2⁶³ ~ 2⁶³-1	大整数

以TINYINT为例，用1个字节，也就是8个位表示有符号数的话，就是既可以表示正数，也可以表示负数的话，需要有一个比特位表示正负号。但是如果表示无符号数的话，也就是只表示非负数的话，就不需要表示正负号，这是有符号数和无符号数的区别。

光说不练假把式，我们来逐个验证自己的想法。

创建一张测试表：

CREATE TABLE test_int1(
 f1 TINYINT,
 f2 SMALLINT,
 f3 MEDIUMINT,
 f4 INTEGER,
 f5 BIGINT
);

来看下具体的表结构：

mysql> desc test_int1;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| f1    | tinyint(4)   | YES  |     | NULL    |       |
| f2    | smallint(6)  | YES  |     | NULL    |       |
| f3    | mediumint(9) | YES  |     | NULL    |       |
| f4    | int(11)      | YES  |     | NULL    |       |
| f5    | bigint(20)   | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

我们对f1赋值为-1：

mysql> insert into test_int1(f1) value (-1);
Query OK, 1 row affected (0.00 sec)

mysql> select * from test_int1;
+------+------+------+------+------+
| f1   | f2   | f3   | f4   | f5   |
+------+------+------+------+------+
|   -1 | NULL | NULL | NULL | NULL |
+------+------+------+------+------+
1 row in set (0.00 sec)

由于tinyint范围是 -128~127 ，当赋值超过127时，就会报错：

mysql> insert into test_int1(f1) value (128);
ERROR 1264 (22003): Out of range value for column 'f1' at row 1

可以看出来，创建后默认当作是有符号数，就是说有一个比特位表示正负号，比如tinyint就只有7个比特来表示数字，因此它的范围是：-128~127 。

除非我们指定f1是无符号数（非负），此时f1就变成了非负数，也不需要有一个单独的比特位来表示正负了，用8个比特来表示数字，范围为0-255 。

如何指定其为无符号数呢？在类型后面增加unsigned即可，比如int unsigned就可以指定为无符号的int类型了。

我们以f1字段为例进行修改和测试：

mysql> alter table test_int1 modify f1 tinyint unsigned;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from test_int1;
Empty set (0.00 sec)

mysql> insert into test_int1(f1) value (-1);
ERROR 1264 (22003): Out of range value for column 'f1' at row 1
mysql> 
mysql> 
mysql> insert into test_int1(f1) value (255);
Query OK, 1 row affected (0.00 sec)

mysql> select * from test_int1;
+------+------+------+------+------+
| f1   | f2   | f3   | f4   | f5   |
+------+------+------+------+------+
|  255 | NULL | NULL | NULL | NULL |
+------+------+------+------+------+
1 row in set (0.00 sec)

mysql> insert into test_int1(f1) value (256);
ERROR 1264 (22003): Out of range value for column 'f1' at row 1

下面继续看个问题，我们再来看下一开始创建的表结构：

mysql> desc test_int1;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| f1    | tinyint(4)   | YES  |     | NULL    |       |
| f2    | smallint(6)  | YES  |     | NULL    |       |
| f3    | mediumint(9) | YES  |     | NULL    |       |
| f4    | int(11)      | YES  |     | NULL    |       |
| f5    | bigint(20)   | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

可以看到tinyint(4)、int(11)这种类型后面带上了(4)、(11)这些数字，这些是什么含义呢？我在创建表结构的时候并没有指定这些数字呀？

第一个合理猜测是指定了这个字段的长度，但是我们知道tinyint有符号数的范围是-128~127，并不是四位数，因此这个推测并不正确。

这里我们以INT(M)为例说明，其实这里的M是指显示长度。M的取值范围是(0, 255)。例如，int(5)：当数据宽度小于5位的时候在数字前面需要用字符填满宽度。该项功能需要配合“ZEROFILL”使用，表示用“0”填满宽度，否则指定显示宽度无效。

如果设置了显示宽度，那么插入的数据宽度超过显示宽度限制，会不会截断或插入失败？
答案：不会对插入的数据有任何影响，还是按照类型的实际宽度进行保存，即显示宽度与类型可以存储的值范围无关。从MySQL 8.0.17开始，整数数据类型不推荐使用显示宽度属性。

为了方便理解，我们重新创建一张新表：

mysql> CREATE TABLE test_int2( f1 INT, f2 INT(5), f3 INT(5) ZEROFILL );
Query OK, 0 rows affected (0.03 sec)
mysql> desc test_int2;
+-------+--------------------------+------+-----+---------+-------+
| Field | Type                     | Null | Key | Default | Extra |
+-------+--------------------------+------+-----+---------+-------+
| f1    | int(11)                  | YES  |     | NULL    |       |
| f2    | int(5)                   | YES  |     | NULL    |       |
| f3    | int(5) unsigned zerofill | YES  |     | NULL    |       |
+-------+--------------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

根据上面所述，int的有符号数取值范围是-2³¹ ~ 2³¹-1，换算下来其实是-2147483648~2147483647，其范围足足有大约42亿，是不是很大？

此时分别为f1、f2、f3赋值为12，看下效果：

mysql> insert into test_int2(f1,f2,f3) values(12,12,12);
Query OK, 1 row affected (0.00 sec)

mysql> select * from test_int2;
+------+------+-------+
| f1   | f2   | f3    |
+------+------+-------+
|   12 |   12 | 00012 |
+------+------+-------+
1 row in set (0.00 sec)

f1和f2无异议，f3变成了00012，这里就不得不解释下“ZEROFILL”的含义了，其规则为：

ZEROFILL: 0填充,（如果某列是ZEROFILL，那么MySQL会自动为当前列添加UNSIGNED属性），如果指定了ZEROFILL只是表示不够M位时，用0在左边填充，如果超过M位，只要不超过数据存储范围即可。

下面我们来对f1、f2、f3都赋值为12345试试：

mysql> update test_int2 set f1=12345,f2=12345,f3=12345;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from test_int2;
+-------+-------+-------+
| f1    | f2    | f3    |
+-------+-------+-------+
| 12345 | 12345 | 12345 |
+-------+-------+-------+
1 row in set (0.01 sec)

可以看到f3已经被5位填满，也就正常展示没有0的填充了。

下面我们继续增大，对f1、f2、f3赋值为123456，看看会不会报错：

mysql> update test_int2 set f1=123456,f2=123456,f3=123456;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from test_int2;
+--------+--------+--------+
| f1     | f2     | f3     |
+--------+--------+--------+
| 123456 | 123456 | 123456 |
+--------+--------+--------+
1 row in set (0.00 sec)

可以看到，并没有报错，并且也没有发生截取，正常地展示我们所更新的数值。

所以，在 INT(M) 中，M 的值跟 int(M) 所占多少存储空间并无任何关系。int(3)、int(4)、int(8) 在磁盘上都是占用 4 bytes 的存储空间。也就是说，int(M)，必须和UNSIGNED ZEROFILL 一起使用才有意义。如果整数值超过M位，就按照实际位数存储，只是无须再用字符 0 进行填充。这个规则对于整数类型都适用。

各种整数类型，按照其不同的取值范围，我们结合业务来合理选择使用。

一些工程使用上的建议：

首要原则：首先确保数据不会超过取值范围，在这个前提之下，再去考虑如何节省存储空间。
我们在选择列的数据类型时，在满足业务需求的前提下，应遵循“最小”原则，选择“最小”的数据类型，能占用更少的存储空间并获得更高的查询效率；
比如存储性别，由于只有男、女、未知这三种类型，那么工程实现上只需要存储0、1、2三种即可满足业务条件，tinyint是比较好的选择；

如果有不确定的因素，应当考虑使用数值范围较大的整数类型，避免出现系统故障。

二、浮点数类型

在MySQL中，浮点数包括float（单精度）和double（双精度），而定点数仅包括decimal一种。

我们先来聊聊浮点数类型，为什么说它不精确。

浮点数是用来表示小数的，我们平时用的十进制小数也可以被转换成二进制后被计算机存储。比如9.875，这个小数可以被表示成这样：

9.875 = 8 + 1 + 0.5 + 0.25 + 0.125 = 1 × 2³ + 1 × 2⁰ + 1 × 2⁻¹ + 1 × 2⁻² + 1 × 2⁻³

也就是说，如果十进制小数9.875转换成二进制小数的话就是：1001.111。为了在计算机里存储这种二进制小数，我们统一把它们表示成a × 2ⁿ的形式，其中1≤|a|＜2，比如1001.111可以被表示成1.001111 × 2³，我们把小数点之后的001111称为尾数，把2³中的3称为指数，然后只需要在计算机中的比特位中表示出尾数和指数就行了。另外，小数也有正负之分，我们还需要单独的部分来表示小数的正负号。综上所述，表示一个浮点数需要下边几个部分：

符号部分，占用1个比特位即可。
指数部分，视具体浮点数格式而定。
尾数部分，视具体浮点数格式而定。

以单精度浮点数类型FLOAT类型为例，它占用的4个字节的各个组成部分如下图所示：

这32个二进制位的内存编号从高到低 (从31到0), 共包含如下几个部分:

sign: 符号位, 即图中蓝色的方块
biased exponent: 指数位, 即图中绿色的方块
fraction: 尾数位, 即图中红色的方块

很显然，我们表示一个浮点数使用的字节数越多，表示尾数和指数的范围就越大，也就是说可以表示的小数范围就越大。

两种浮点数类型所需要的存储空间需求如下：

类型	占用的存储空间（单位：字节）	绝对值最小非0值	绝对值最大非0值	含义
FLOAT	4	(-3.402823466E+38,-1.175494351E-38),0,(1.175494351E-38,3.402823466E+38)	0,(1.175494351E-38,3.402823466E+38)	单精度浮点数
DOUBLE	8	(-1.7976931348623157E+308,-2.2250738585072014E-308),0,(2.2250738585072014E-308,1.7976931348623157E+308)	0,(2.2250738585072014E-308,1.7976931348623157E+308)	双精度浮点数

这里注意，范围由二进制转换为了我们熟知的十进制的值来表示了，这里采取科学计数法来表示，科学记数法的形式是由两个数的乘积组成的，表示为a×10^b（aEb）。

另外需要注意的是，虽然有的十进制小数，比如1.875可以被很容易的转换成二进制数1.111，但是更多的小数是无法直接转换成二进制的，比如说0.3，它转换成的二进制小数就是一个无限小数，但是我们现在只能用4个字节或者8个字节来表示这个小数，所以只能进行一些舍入来近似的表示，所以我们说计算机的浮点数表示有时是不精确的。

关于计算机如何存储和表示浮点数，是一个比较复杂的问题，感兴趣的读者朋友可以参考这里的三篇文章：

https://zhuanlan.zhihu.com/p/343033661
https://zhuanlan.zhihu.com/p/343037540
https://zhuanlan.zhihu.com/p/343040291

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

举个例子看一下，设置了M和D的单精度浮点数的取值范围的变化：

类型	取值范围
FLOAT(4, 1)	-999.9~999.9
FLOAT(5, 1)	-9999.9~9999.9
FLOAT(6, 1)	-99999.9~99999.9
FLOAT(4, 0)	-9999~9999
FLOAT(4, 1)	-999.9~999.9
FLOAT(4, 2)	-99.99~99.99

FLOAT和DOUBLE类型在不指定(M,D)时，默认会按照实际的精度（由实际的硬件和操作系统决定）来显示。
对于浮点数来说，如果存储时，整数部分超出了范围，MySQL就会报错，不允许存这样的值；如果存储时，小数点部分若超出范围，就分以下情况：

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

针对以上所述，我们创建一张表来做一些实验验证。

CREATE TABLE test_float_double(
f1 FLOAT,
f2 FLOAT(5,2),
f3 FLOAT(10,6),
f4 DOUBLE
);

得到如下表结构：

mysql> desc test_float_double;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| f1    | float        | YES  |     | NULL    |       |
| f2    | float(5,2)   | YES  |     | NULL    |       |
| f3    | float(10,6)  | YES  |     | NULL    |       |
| f4    | double       | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

首先对f1赋值一个小数，比如123.45：

mysql> insert into test_float_double(f1) value(123.45);
Query OK, 1 row affected (0.00 sec)

mysql> select * from test_float_double;
+--------+------+------+------+
| f1     | f2   | f3   | f4   |
+--------+------+------+------+
| 123.45 | NULL | NULL | NULL | 
+--------+------+------+------+
1 row in set (0.00 sec)

可以看到正常入库了，我们上面学习知道，如果不特别设定浮点数的精度，那么会按照实际的精度来显示（由实际的硬件和操作系统决定）。抛开其他的限制来说，理论上float和double的精度可以大约达到如下水平（注意这里用的是大约）：

float单精度小数部分只能精确到后面6位，加上小数点前的一位，即有效数字为7位
double双精度小数部分能精确到小数点后的15位，加上小数点前的一位有效位数为16位

我们来验证f1的精度，分别给f1赋值为9.9、9.99、9.999、9.9999、9.99999，都塞入成功并且查询无任何误差：

mysql> update test_float_double set f1=9.99999;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from test_float_double;
+---------+------+------------+
| f1      | f2   | f3   ｜f4  | 
+---------+------+------------+
| 9.99999 | NULL | NULL | NULL|
+---------+------+------------+
1 row in set (0.00 sec)

再次增加一个小数时，触发了四舍五入：

mysql> update test_float_double set f1=9.999999;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from test_float_double;
+------+------+------------+------+
| f1   | f2   | f3         | f4   |
+------+------+------------+------+
|   10 | NULL | NULL | NULL | NULL|
+------+------+------------+------+
1 row in set (0.00 sec)

这么算来，实际上精度也就达到了6位有效数字，不过对于我们一般的业务来说，能达到这个程度已经足够。

再来看下f2，我们指定了FLOAT(5,2)，即整数位：3，小数位：2，如果赋值为123.456，那么就会超出小数范围，触发四舍五入：

mysql> update test_float_double set f2=123.45;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from test_float_double;
+----------+--------+------------+------+
| f1       | f2     | f3         | f4   | 
+----------+--------+------------+------+
| NULL | 123.45 | NULL       | NULL |
+----------+--------+------------+------+
1 row in set (0.00 sec)

mysql> update test_float_double set f2=123.456;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from test_float_double;
+----------+--------+------------+------+
| f1       | f2     | f3         | f4   |
+----------+--------+------------+------+
| NULL | 123.46 | NULL       | NULL |
+----------+--------+------------+------+
1 row in set (0.00 sec)

由于f2的整数部分只有三位，一旦四舍五入导致其超出999，那么就会报错，我们来尝试赋值为999.999，此时触发四舍五入看是否会报错：

mysql> update test_float_double set f2=999.999;
ERROR 1264 (22003): Out of range value for column 'f2' at row 1

f3字段设置为了FLOAT(10,6)，即整数位：4，小数位：6，我们对其赋值为32.214412：

mysql> update test_float_double set f3=32.214412;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from test_float_double;
+----------+--------+-----------+------+
| f1       | f2     | f3        | f4   |
+----------+--------+-----------+------+
| NULL | 999.99 | 32.214413 | NULL |
+----------+--------+-----------+------+
1 row in set (0.00 sec)

仔细看f3字段，我们明明赋值的是32.214412，但结果显示32.214413，最后一位相差一个数字，这就是float不准确的一个具体表现了。float会尽可能地寻找近似的值存储，以保证精度。这一切都是因为我们的计算机是以二进制存储的，面对这种除不尽的场景时，加之float只有32位的空间，必定要发生截取，以一个近似值去存储，此时就会出现误差。

说到底，小数相对于整数来说，压根不能穷举，比如范围是0-10，整数个数是11个是可穷举出来的，但是0-10之间的小数有多少个呢？答案自然是无穷。即便加上限制条件，最大到6位小数，此时是可以穷举了，但是不能涵盖除不尽这种情况，毕竟计算机是以二进制存储的，比如上文中提到的0.3如何用二进制表示出来，所以计算机只能尽可能地寻找到它自己能表示出来的并且最接近这个小数的值了，也就会出现一些结果上的偏差。

此时我故意给f3赋值一个32.015625，这个值的小数部分正好是2的-6次方，此时是可以精确存储和展示的：

mysql> update test_float_double set f3=32.015625;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from test_float_double;
+----------+--------+-----------+------+
| f1       | f2     | f3        | f4   |
+----------+--------+-----------+------+
| NULL | 999.99 | 32.015625 | NULL |
+----------+--------+-----------+------+
1 row in set (0.01 sec)

下面来看看double，精度上会相对于float增加不少，毕竟空间存储上是float的两倍。我们先拿32.214412小试牛刀，float不行，double应该是可以的，我们来确定下，这次给f4字段赋值。

mysql> update test_float_double set f4=32.214412;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from test_float_double;
+----------+--------+-----------+-----------+
| f1       | f2     | f3        | f4        |
+----------+--------+-----------+-----------+
| NULL | 999.99 | 32.015625 | 32.214412 |
+----------+--------+-----------+-----------+
1 row in set (0.00 sec)

可以看到，f4正确无误地保存了该值，说明遇到这种小数较多的业务时，可能得考虑是否需要使用double才行了。

mysql> update test_float_double set f4=32.214415123456789;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from test_float_double;
+----------+--------+-----------+-------------------+
| f1       | f2     | f3        | f4                |
+----------+--------+-----------+-------------------+
| NULL | 999.99 | 32.015625 | 32.21441512345679 |
+----------+--------+-----------+-------------------+
1 row in set (0.00 sec)
mysql> update test_float_double set f4=2.2144151234567891;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from test_float_double;
+----------+--------+-----------+-------------------+
| f1       | f2     | f3        | f4                |
+----------+--------+-----------+-------------------+
| 0.214414 | 999.99 | 32.015625 | 2.214415123456789 |
+----------+--------+-----------+-------------------+
1 row in set (0.01 sec)

小数点后精确到了15位！可以看到double确实能量比float大不少。

我们需要注意：

double为了提高精度，牺牲了不少存储空间，这必然会提高存储和计算的压力，如果不是业务必须，float已经足够使用了；
从MySQL 8.0.17开始，FLOAT(M,D) 和DOUBLE(M,D)用法在官方文档中已经明确不推荐使用，将来可能被移除。另外，关于浮点型FLOAT和DOUBLE的UNSIGNED也不推荐使用了，将来也可能被移除。
在编程中，如果用到浮点数，要特别注意误差问题，因为浮点数是不准确的，所以我们要避免使用“=”来判断两个数是否相等。同时，在一些对精确度要求较高的项目中，千万不要使用浮点数，不然会导致结果错误，甚至是造成不可挽回的损失。那么，MySQL 有没有精准的数据类型呢？当然有，这就是定点数类型：DECIMAL。

三、定点数类型

正因为用浮点数表示小数可能会有不精确的情况，在一些情况下我们必须保证小数是精确的，所以MySQL提供一种称之为定点数的数据类型，它也是存储小数的一种方式：

类型	占用的存储空间（单位：字节）	取值范围
DECIMAL(M, D)	取决于M和D	取决于M和D

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

当DECIMAL类型不指定精度和标度时，其默认为DECIMAL(10,0)，表示有10个整数位，0个小数位，其范围：-9999999999~9999999999。当数据的精度超出了定点数类型的精度范围时，则MySQL同样会进行四舍五入处理。
DECIMAL 的存储空间并不是固定的，和M、D的取值有关。

由于定点数类型精准、没有误差的特性，使得其适合于对精度要求极高的场景（比如涉及金额计算的场景）。

我们来创建一张新表：

mysql> CREATE TABLE test_decimal_1( 
f1 DECIMAL, 
f2 DECIMAL(5,2) ,
f3 DECIMAL(10,6));
Query OK, 0 rows affected (0.03 sec)

mysql> desc test_decimal_1;
+-------+---------------+------+-----+---------+-------+
| Field | Type          | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+-------+
| f1    | decimal(10,0) | YES  |     | NULL    |       |
| f2    | decimal(5,2)  | YES  |     | NULL    |       |
| f3    | decimal(10,6) | YES  |     | NULL    |       |
+-------+---------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

首先，表结构佐证了第一点：当DECIMAL类型不指定精度和标度时，其默认为DECIMAL(10,0)，表示有10个整数位，0个小数位，其范围：-9999999999~9999999999。

f2的类型是decimal(5,2)，表示其整数位有三位，小数位有两位。f3的类型是decimal(10,6)，表示其整数位有四位，小数位有六位。

首先我们对f1和f2分别赋值123.12：

mysql> insert into test_decimal_1 (f1,f2) values(123.12,123.12);
Query OK, 1 row affected, 1 warning (0.01 sec)

mysql> select * from test_decimal_1;
+------+--------+------+
| f1   | f2     | f3   |
+------+--------+------+
|  123 | 123.12 | NULL |
+------+--------+------+
1 row in set (0.00 sec)

由于f1的小数位是0，因此被四舍五入为123保存，而f2是正好满足两位小数的，因此可以完整保存下来。

下面我们增加小数位，对f1和f2分别赋值123.123:

mysql> update test_decimal_1 set f1=123.123,f2=123.123;
Query OK, 0 rows affected, 2 warnings (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 2

mysql> select * from test_decimal_1;
+------+--------+------+
| f1   | f2     | f3   |
+------+--------+------+
|  123 | 123.12 | NULL |
+------+--------+------+
1 row in set (0.00 sec)

此时f1仍然四舍五入保持不变，但是f2由于小数位只能精确到2位，因此后面的3也被四舍五入了。

当超出范围时会报错，相信这很好理解：

mysql> update test_decimal_1 set f1=1234.123,f2=1234.123;
ERROR 1264 (22003): Out of range value for column 'f2' at row 1

总结：与浮点数有一个相同的特性，那就是整数位超出范围会直接报错，小数位超出范围会四舍五入。

我们最后再来看下f3，f3的类型是decimal(10,6)，表示其整数位有四位，小数位有六位。当我们赋值f3为32.214412，看看会不会跟float(10,6)一样出现误差问题：

mysql> update test_decimal_1 set f3=32.214412;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from test_decimal_1;
+------+--------+-----------+
| f1   | f2     | f3        |
+------+--------+-----------+
|  124 | 123.13 | 32.214412 |
+------+--------+-----------+
1 row in set (0.00 sec)

可以看到f3分毫不差地保存了下来，这就是与浮点数最大的不同了。

四、浮点数的计算误差问题

当我们对数据库中的小数进行一些计算时，我们会发现很多奇怪的现象。

我们创建一张表，插入三条double类型的数据，对其进行求和运算：

mysql> CREATE TABLE test_double2( f1 DOUBLE );
Query OK, 0 rows affected (0.03 sec)

mysql> desc test_double2;
+-------+--------+------+-----+---------+-------+
| Field | Type   | Null | Key | Default | Extra |
+-------+--------+------+-----+---------+-------+
| f1    | double | YES  |     | NULL    |       |
+-------+--------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> INSERT INTO test_double2 VALUES(0.47),(0.44),(0.19);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from test_double2;
+------+
| f1   |
+------+
| 0.47 |
| 0.44 |
| 0.19 |
+------+
3 rows in set (0.00 sec)

mysql> select sum(f1) from test_double2;
+--------------------+
| sum(f1)            |
+--------------------+
| 1.0999999999999999 |
+--------------------+
1 row in set (0.00 sec)

按理说：0.47+0.44+0.19 = 1.1，而不是奇怪的1.0999999999999999。此时我们将类型改为decimal就可以解决此问题了。

mysql> CREATE TABLE test_decimal_2( f1 decimal(5,2) );
Query OK, 0 rows affected (0.03 sec)

mysql> desc test_decimal_2;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| f1    | decimal(5,2) | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
1 row in set (0.01 sec)

mysql> INSERT INTO test_decimal_2 VALUES(0.47),(0.44),(0.19);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from test_decimal_2;
+------+
| f1   |
+------+
| 0.47 |
| 0.44 |
| 0.19 |
+------+
3 rows in set (0.00 sec)

mysql> select sum(f1) from test_decimal_2;
+---------+
| sum(f1) |
+---------+
|    1.10 |
+---------+
1 row in set (0.00 sec)

这里输出了我们预期的1.10，因此为了保证精确度，尤其是金融行业，请务必不要使用浮点数造成不必要的被迫离职烦恼。

五、DECIMAL存储空间如何计算

上面我们提了一句：DECIMAL 的存储空间并不是固定的，和M、D的取值有关，那么到底是如何计算的呢？我们这里展开说一说。

我们说定点数是一种精确的小数，为了达到精确的目的我们就不能把它转换成二进制小数之后再存储(因为有很多十进制小数转为二进制小数后需要进行舍入操作，导致二进制小数表示的数值是不精确的)。

其实转念一想，所谓的小数只是把两个十进制整数用小数点分割开来而已，我们只要把小数点左右的两个十进制整数给存储起来，那不就是精确的了么。比方说对于十进制小数2.38来说，我们可以把这个小数的小数点左右的两个整数，也就是2和38分别保存起来，那么不就相当于保存了一个精确的小数么？

在mysql官方文档中介绍了DECIMAL是如何存储的：https://dev.mysql.com/doc/refman/5.7/en/precision-math-decimal-characteristics.html

Values for DECIMAL columns are stored using a binary format that packs nine decimal digits into 4 bytes. The storage requirements for the integer and fractional parts of each value are determined separately. Each multiple of nine digits requires 4 bytes, and any remaining digits left over require some fraction of 4 bytes.

翻译过来，最核心的一句话是第一句已标粗的语句，说的是：Decimal 使用二进制格式存储，将每9位十进制数存储为4个字节。

但是我们的小数往往不足9位数，或者说位数不可能都正好是9的倍数，也都需要4个字节吗？显然不是，下面给出了一个位数对应的所需要存储的空间：

组中包含的十进制位数	占用存储空间大小（单位：字节）
1或2	1
3或4	2
5或6	3
7或8或9	4

什么意思呢？

1、字段decimal(18,9)，18-9=9，这样整数部分和小数部分都是9，那两边分别占用4个字节，一共占用8个字节；
2、字段decimal(20,6)，20-6=14，其中小数部分为6，就对应上表中的3个字节，而整数部分为14，14-9=5，就是4个字节再加上表中的3个字节，所以一共加起来是3+4+3=10个字节。

所以应该可以理解为什么说DECIMAL 的存储空间并不是固定的、和M、D的取值有关了。

也可以大概看出来，与浮点数相比，定点数可能需要更多的空间来存储数据，所以如果不是在某些需要存储精确小数的场景下，一般的小数用浮点数表示就足够了。