MySQL定点数类型DECIMAL用法详解

一、MySQL DECIMAL 的使用

DECIMAL 数据类型用于在数据库中存储精确的数值，我们经常将该数据类型用于保留准确精确度的列，例如会计系统中的货币数据。

要定义数据类型为DECIMAL的列，请使用以下语法：

1
column_name DECIMAL(P,D);

在上面的语法中：

  • P是表示有效数字数的精度。P范围为1〜65。
  • D是表示小数点后的位数。D的范围是0~30。MySQL要求D小于或等于(<=)P。
  • DECIMAL(P，D)表示列可以存储D位小数的P位数。十进制列的实际范围取决于精度和刻度。

关键字INT是INTEGER的同义词，关键字DEC是DECIMAL的同义词。

与INT数据类型一样，DECIMAL类型也具有UNSIGNED和ZEROFILL属性。 如果使用UNSIGNED属性，则DECIMAL UNSIGNED的列将不接受负值。【取值范围只有正数部分，而不是像整数类型一样取值范围翻倍】

如果使用ZEROFILL，MySQL将把显示值填充到0以显示由列定义指定的宽度。 另外，如果我们对DECIMAL列使用ZEROFILL，MySQL将自动将UNSIGNED属性添加到列。

以下示例使用DECIMAL数据类型定义的一个叫作amount的列。

1
amount DECIMAL(6,2);

在此示例中，amount列最多可以存储6位数字，小数位数为2位; 因此，amount列的范围是从-9999.99到9999.99。

MySQL允许使用以下语法：

1
column_name DECIMAL(P);

这相当于：在这种情况下，列不包含小数部分或小数点。此外，我们甚至可以使用以下语法。

1
column_name DECIMAL;

在这种情况下，P的默认值为10。

二、DECIMAL 的存储方式以及占用空间

DECIMAL的存储方式和其他数据类型都不同，它是以字符串形式存储的。假设一个字段为DECIMAL(3,0)，当我们存入100时，实际上存入的1、0、0这三个字符拼接而成的字符串的二进制值，由于一个数字字符占用1个字节，所以共占用三个字节的空间。然后还要存入描述该数据的元数据，元数据固定占用2个字节，所以共占用5个字节。

总结：DECIMAL(M,N)会占用(M+2)个字节。

三、DECIMAL数据类型示例

首先，创建一个名为test_order的新表，其中包含三列：id，description和cost。

1
2
3
4
5
CREATE TABLE test_order (
 id INT AUTO_INCREMENT PRIMARY KEY,
 description VARCHAR(255),
 cost DECIMAL(19,4) NOT NULL
);

第二步，将资料插入test_order表。

1
2
INSERT INTO test_order(description,cost)
VALUES('Bicycle', 500.34),('Seat',10.23),('Break',5.21);

第三步，从test_order表查询数据。

1
2
3
SELECT * from test_order
查询结果：
![img](https://img.jbzj.com/file_images/article/202102/2021020610262042.png)

第四步，更改cost列以包含ZEROFILL属性。

1
2
ALTER TABLE test_order
MODIFY cost DECIMAL(19,4) zerofill;

第五步，再次查询test_order表。

1
查询结果：

如上所见，在输出值中填充了许多零。

因为zerofill，当我们插入负值会报错：Out of range value for column ...

其它插入测试结论：

  • 当数值在其取值范围之内，小数位多了，则四舍五入后直接截断多出的小数位。
  • 若数值在其取值范围之外，则直接报Out of range value错误。

四、精度问题测试

FLOAT、DOUBLE、DECIMAL 这三种数据类型的精度问题测试

1、建表语句

1
2
3
4
5
6
DROP TABLE IF EXISTS `test_float`;
CREATE TABLE test_float(
`f1` FLOAT,
`f2` DOUBLE,
`f3` DEC(10,2)
);

2、插入测试数据

1
INSERT INTO test_float(f1,f2,f3) VALUES(0.47,0.47,0.47),(0.44,0.44,0.44),(0.19,0.19,0.19);

3、查询结果

1
SELECT SUM(f1),SUM(f2),SUM(f3) FROM test_float;

查询结果如下：

img

FLOAT、DOUBLE 类型求和的结果都出现了精度损失，但是 DOUBLE 的精度更高。他们求和的结果都无限接近 1.10。

4、比较结果

执行如下sql可知：

1
SELECT SUM(f2) = 1.1,SUM(f2) = 1.1,SUM(f3) = 1.1 FROM test_float;

img

0 表示 false；1 表示 true。