引言

或许很多同学都很好奇为什么在数据库里要引入decimal这一种数据类型来表示小数？使用float和double这两种数据类型来表示小数为什么不可以？
那是因为float和double这两种类型可能会出现精度问题
如果本文出现了错误，还请路过的大佬在评论区指出，您的批评是我前进的动力！谢谢！

decimal数据类型

参考文献：https://cloud.tencent.com/developer/article/1559660
DECIMAL从MySQL 5.1引入，列的声明语法是DECIMAL(M,D)。NUMERIC与DECIMAL同义，如果字段类型定义为NUMERIC，则将自动转成DECIMAL。

对于声明语法DECIMAL(M,D)，自变量的值范围如下：

M是最大位数（精度），范围是1到65。可不指定，默认值是10。
D是小数点右边的位数（小数位）。范围是0到30，并且不能大于M，可不指定，默认值是0。
例如字段 salary DECIMAL(5,2)，能够存储具有五位数字和两位小数的任何值，因此可以存储在salary列中的值的范围是从-999.99到999.99。

# 创建具有DECIMAL字段的表 验证decimal默认是decimal(10,0)
mysql> create table decimal_tb (col1 decimal,col2 decimal(5,2));
Query OK, 0 rows affected (0.04 sec)
mysql> show create table decimal_tb\G
*************************** 1. row ***************************
       Table: decimal_tb
Create Table: CREATE TABLE `decimal_tb` (
  `col1` decimal(10,0) DEFAULT NULL,
  `col2` decimal(5,2) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

# 插入数据测试
# 结论：超出存储范围会报错，小数位不足会自动补0，首位数字为0自动忽略，小数位超出会截断 并按四舍五入处理。
mysql> insert into decimal_tb (col1,col2) values (100,100);
Query OK, 1 row affected (0.05 sec)
mysql> insert into decimal_tb (col2) values (1.23);
Query OK, 1 row affected (0.01 sec)
mysql> insert into decimal_tb (col2) values (10.2);
Query OK, 1 row affected (0.01 sec)
mysql> insert into decimal_tb (col2) values (09.9);
Query OK, 1 row affected (0.01 sec)
mysql> select * from decimal_tb;
+------+--------+
| col1 | col2   |
+------+--------+
|  100 | 100.00 |
| NULL |   1.23 |
| NULL |  10.20 |
| NULL |   9.90 |
+------+--------+
4 rows in set (0.00 sec)
mysql> insert into decimal_tb (col2) values (9999);
ERROR 1264 (22003): Out of range value for column 'col2' at row 1
mysql> insert into decimal_tb (col2) values (12.233); 
Query OK, 1 row affected, 1 warning (0.01 sec)
mysql> show warnings;
+-------+------+-------------------------------------------+
| Level | Code | Message                                   |
+-------+------+-------------------------------------------+
| Note  | 1265 | Data truncated for column 'col2' at row 1 |
+-------+------+-------------------------------------------+
1 row in set (0.00 sec)
mysql> insert into decimal_tb (col2) values (12.2300);
Query OK, 1 row affected (0.01 sec)

# 变量范围测试
# 结论：M范围是1到65，D范围是0到30，且D不大于M
mysql> alter table decimal_tb add column col3 decimal(6,6);
Query OK, 0 rows affected (0.12 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> alter table decimal_tb add column col4 decimal(6,7); 
ERROR 1427 (42000): For float(M,D), double(M,D) or decimal(M,D), M must be >= D (column 'col4').
mysql> alter table decimal_tb add column col4 decimal(65,2);
Query OK, 0 rows affected (0.11 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> alter table decimal_tb add column col4 decimal(66,2);
ERROR 1426 (42000): Too-big precision 66 specified for 'col4'. Maximum is 65.
mysql> alter table decimal_tb add column col5 decimal(60,30); 
Query OK, 0 rows affected (0.13 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> alter table decimal_tb add column col6 decimal(60,31);
ERROR 1425 (42000): Too big scale 31 specified for column 'col6'. Maximum is 30.

decimal使用总结：
上面的内容从实战出发，介绍了DECIMAL类型的使用方法及注意事项，你可能也知道float、double这些浮点数类型，这两个同样可以存储小数，但这里不过多介绍，只是提醒大家float、double类型无法确保精度，很容易产生误差，特别是在求和计算的时候，所有当存储小数，特别是涉及金额时推荐使用DECIMAL类型。这里总结下使用DECIMAL应该注意的事项：

DECIMAL(M,D)中，M范围是1到65，D范围是0到30。
M默认为10，D默认为0，D不大于M。
DECIMAL(5,2)可存储范围是从-999.99到999.99，超出存储范围会报错。
存储数值时，小数位不足会自动补0，首位数字为0自动忽略。
小数位超出会截断，产生告警，并按四舍五入处理。
使用DECIMAL字段时，建议M，D参数手动指定，并按需分配。

float与double精度问题的探索

为什么说是探索？因为俺其实对于这个问题理解的并不是很透彻。

/* 首先我们新建一张测试表，并设置两个属性num1和num2
数据类型分别是float(10,6)与double(10,6)
第二个参数的意思小数位数是6位*/
mysql> select * from tt where num1=32.213998;
Empty set (0.00 sec)

mysql> create table test (
    -> num1 float(10,6),
    -> num2 double(10,6)
    -> );
Query OK, 0 rows affected, 2 warnings (0.16 sec)

/*接着我们想表格里插入以下数据：
2	2
1.1	1.1
2.11	2.11
32.214	32.214
41.4513	41.4513
5.21452	5.21452
6.214522	6.214522
7.1421457	7.1421457
mysql> insert into test(num1,num2) value(2,2),
    -> (1.1,1.1),
    -> (2.11,2.11),
    -> (32.214,32.214),
    -> (41.4513,41.4513),
    -> (5.21452,5.21452),
    -> (6.214522,6.214522),
    -> (7.1421457,7.1421457);
Query OK, 8 rows affected (0.05 sec)
Records: 8  Duplicates: 0  Warnings: 0

/*查看当前的表里的数据*/
mysql> select * from test;
+-----------+-----------+
| num1      | num2      |
+-----------+-----------+
|  2.000000 |  2.000000 |
|  1.100000 |  1.100000 |
|  2.110000 |  2.110000 |
| 32.214001 | 32.214000 |
| 41.451302 | 41.451300 |
|  5.214520 |  5.214520 |
|  6.214522 |  6.214522 |
|  7.142146 |  7.142146 |
+-----------+-----------+
8 rows in set (0.00 sec)

/*我们发现一个很有趣的问题：
对于2	1.1	2.11	等这种小数位数不够6的会往后添加数字使其近似于插入的数据，那如果是decimal类型的呢？
这里我在将表格扩展一列num3属性，使其数据类型为decimal，并向其插入刚才的数据，并显示出来：
mysql> alter table test add num3 decimal(10,6);
Query OK, 0 rows affected (0.31 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> insert into test(num3) value(2),(1.1),(2.11),(32.214),(41.4513),(5.21452),(6.214522),(7.1421457);
Query OK, 8 rows affected, 1 warning (0.10 sec)
Records: 8  Duplicates: 0  Warnings: 1

mysql> select * from test;
+-----------+-----------+-----------+
| num1      | num2      | num3      |
+-----------+-----------+-----------+
|  2.000000 |  2.000000 |      NULL |
|  1.100000 |  1.100000 |      NULL |
|  2.110000 |  2.110000 |      NULL |
| 32.214001 | 32.214000 |      NULL |
| 41.451302 | 41.451300 |      NULL |
|  5.214520 |  5.214520 |      NULL |
|  6.214522 |  6.214522 |      NULL |
|  7.142146 |  7.142146 |      NULL |
|      NULL |      NULL |  2.000000 |
|      NULL |      NULL |  1.100000 |
|      NULL |      NULL |  2.110000 |
|      NULL |      NULL | 32.214000 |
|      NULL |      NULL | 41.451300 |
|      NULL |      NULL |  5.214520 |
|      NULL |      NULL |  6.214522 |
|      NULL |      NULL |  7.142146 |
+-----------+-----------+-----------+
16 rows in set (0.00 sec)


/*
单精度的float类型：我们作如下的一下尝试：
*/
mysql> select * from test where num1=32.214000;
+-----------+-----------+------+
| num1      | num2      | num3 |
+-----------+-----------+------+
| 32.214001 | 32.214000 | NULL |
+-----------+-----------+------+
1 row in set (0.00 sec)

mysql> select * from test where num1=32.214002;
+-----------+-----------+------+
| num1      | num2      | num3 |
+-----------+-----------+------+
| 32.214001 | 32.214000 | NULL |
+-----------+-----------+------+
1 row in set (0.00 sec)
mysql> select * from test where num1=32.213999;
+-----------+-----------+------+
| num1      | num2      | num3 |
+-----------+-----------+------+
| 32.214001 | 32.214000 | NULL |
+-----------+-----------+------+
1 row in set (0.00 sec)

/*
我们发现对于插入的32.214，在float类型下可以使用其近似值查找，这说明这个数在存储时时以近似值的性质存储；虽然浮点数是固定的，但是存在不确切的值
但是在decimal数据类型中就不存在近似值存储这一说：
*/
/*对于数字精度小于设置的精度的时候，float和double会尽可能使用近似值来存取来保证精度，所以这里会出现32.214001与41.451302
但是decimal只会往后自动补0*/
mysql> select * from test where num3=32.214;
+------+------+-----------+
| num1 | num2 | num3      |
+------+------+-----------+
| NULL | NULL | 32.214000 |
+------+------+-----------+
1 row in set (0.00 sec)

mysql> select * from test where num3=32.214000;
+------+------+-----------+
| num1 | num2 | num3      |
+------+------+-----------+
| NULL | NULL | 32.214000 |
+------+------+-----------+
1 row in set (0.00 sec)

mysql> select * from test where num3=32.214001;
Empty set (0.00 sec)

mysql> select * from test where num3=32.214002;
Empty set (0.00 sec)
/*
如果存入的小数精度大于设置精度就如这里的7.1421457
是一个7位小数，就进行截断即四舍五入，这样的数字查找到的是四舍五入后的，并且确切：
*/
mysql> select * from test where num1=7.142146;
+----------+----------+------+
| num1     | num2     | num3 |
+----------+----------+------+
| 7.142146 | 7.142146 | NULL |
+----------+----------+------+
1 row in set (0.00 sec)

mysql> select * from test where num1=7.1421457;
Empty set (0.00 sec)
/*对于decimal类型的数据，存入的是7.1421457，超过了设置的精度。
因此会四舍五入，变成7.142146，但是对于decimal类型的数据
存入数据库的会后都是以字符串的形式从存储，改变当前属性的精度，也不会产生精度的扩展或者截断问题。
例如这里我将num3的精度延长到8：重新读取*/
mysql> alter table test modify num3 decimal(10,8);
Query OK, 16 rows affected (0.78 sec)
Records: 16  Duplicates: 0  Warnings: 0

mysql> select * from test;
+-----------+-----------+-------------+
| num1      | num2      | num3        |
+-----------+-----------+-------------+
|  2.000000 |  2.000000 |        NULL |
|  1.100000 |  1.100000 |        NULL |
|  2.110000 |  2.110000 |        NULL |
| 32.214001 | 32.214000 |        NULL |
| 41.451302 | 41.451300 |        NULL |
|  5.214520 |  5.214520 |        NULL |
|  6.214522 |  6.214522 |        NULL |
|  7.142146 |  7.142146 |        NULL |
|      NULL |      NULL |  2.00000000 |
|      NULL |      NULL |  1.10000000 |
|      NULL |      NULL |  2.11000000 |
|      NULL |      NULL | 32.21400000 |
|      NULL |      NULL | 41.45130000 |
|      NULL |      NULL |  5.21452000 |
|      NULL |      NULL |  6.21452200 |
|      NULL |      NULL |  7.14214600 |
+-----------+-----------+-------------+
16 rows in set (0.00 sec)
/*
我们发现7.142146不再改变，不会返回到原来的7.1421457.
*/
# mysql # 数据库 # 经验分享
Logo
华为云开发者联盟

为开发者提供学习成长、分享交流、生态实践、资源工具等服务，帮助开发者快速成长。

更多推荐

cover

华为云Stack8.3面向香港正式发布，六大亮点激发云上跃迁

avatar 华为云开发者联盟
cover

GaussDB SQL查询语句执行过程解析

avatar 华为云开发者联盟
cover

6个实例带你解读TinyVue 组件库跨框架技术

avatar 华为云开发者联盟