MySQL 数据类型 DECIMAL 用法

关注

发布于: 2020 年 05 月 13 日

前言：

当我们需要存储小数，并且有精度要求，比如存储金额时，通常会考虑使用 DECIMAL 字段类型，可能大部分同学只是对 DECIMAL 类型略有了解，其中的细节还不甚清楚，本篇文章将从零开始，为你讲述 DECIMAL 字段类型的使用场景及方法。

1.DECIMAL 类型简介

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。

2.DECIMAL 使用实战

下面我们将创建测试表来验证 DECIMAL 字段类型的使用：

# 创建具有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_tbCreate Table: CREATE TABLE `decimal_tb` (  `col1` decimal(10,0) DEFAULT NULL,  `col2` decimal(5,2) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf81 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不大于Mmysql> 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.

复制代码

3.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 参数手动指定，并按需分配。

总结：

本文比较简单实用，通读下来，你大概会明白 DECIMAL 字段的使用场景及注意事项，其实对于常见的字段类型，我们只需要了解其使用场景及注意事项即可，当我们建表时，能够快速选出合适的字段类型才是我们的目的，比如当我们需要存储小数时，能够使用 DECIMAL 类型并且根据业务需要选择合适的精度，这样我们的工作将很容易开展下去。