MySQL数据类型DECIMAL用法

你的名字 2022-05-09 14:54 522阅读 0赞

### MySQL数据类型DECIMAL用法 ###

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

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

<table> 
 <tbody> 
  <tr> 
   <td> <p>1</p> </td> 
   <td> <p><code>column_name&nbsp;&nbsp;</code><code>DECIMAL</code><code>(P,D);</code></p> </td> 
  </tr> 
 </tbody> 
</table>

在上面的语法中：

*  `P`是表示有效数字数的精度。 `P`范围为`1〜65`。
 *  `D`是表示小数点后的位数。 `D`的范围是`0`~`30`。MySQL要求`D`小于或等于(`<=`)`P`。

`DECIMAL(P，D)`表示列可以存储`D`位小数的`P`位数。十进制列的实际范围取决于精度和刻度。

与INT数据类型一样，`DECIMAL`类型也具有`UNSIGNED`和`ZEROFILL`属性。 如果使用`UNSIGNED`属性，则`DECIMAL UNSIGNED`的列将不接受负值。

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

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

<table> 
 <tbody> 
  <tr> 
   <td> <p>1</p> </td> 
   <td> <p><code>amount&nbsp;</code><code>DECIMAL</code><code>(6,2);</code></p> </td> 
  </tr> 
 </tbody> 
</table>

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

MySQL允许使用以下语法：

<table> 
 <tbody> 
  <tr> 
   <td> <p>1</p> </td> 
   <td> <p><code>column_name&nbsp;</code><code>DECIMAL</code><code>(P);</code></p> </td> 
  </tr> 
 </tbody> 
</table>

这相当于：

<table> 
 <tbody> 
  <tr> 
   <td> <p>1</p> </td> 
   <td> <p><code>column_name&nbsp;</code><code>DECIMAL</code><code>(P,0);</code></p> </td> 
  </tr> 
 </tbody> 
</table>

在这种情况下，列不包含小数部分或小数点。

此外，我们甚至可以使用以下语法。

<table> 
 <tbody> 
  <tr> 
   <td> <p>1</p> </td> 
   <td> <p><code>column_name&nbsp;</code><code>DECIMAL</code><code>;</code></p> </td> 
  </tr> 
 </tbody> 
</table>

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

## MySQL DECIMAL存储 ##

MySQL分别为整数和小数部分分配存储空间。 MySQL使用二进制格式存储`DECIMAL`值。它将`9`位数字包装成`4`个字节。

对于每个部分，需要`4`个字节来存储`9`位数的每个倍数。剩余数字所需的存储如下表所示：

例如，`DECIMAL(19,9)`对于小数部分具有`9`位数字，对于整数部分具有`19`位= `10`位数字，小数部分需要`4`个字节。 整数部分对于前`9`位数字需要`4`个字节，`1`个剩余字节需要`1`个字节。`DECIMAL(19,9)`列总共需要`9`个字节。

## MySQL DECIMAL数据类型和货币数据 ##

经常使用`DECIMAL`数据类型的货币数据，如价格，工资，账户余额等。如果要设计一个处理货币数据的数据库，则可参考以下语法 -

<table> 
 <tbody> 
  <tr> 
   <td> <p>1</p> </td> 
   <td> <p><code>amount&nbsp;</code><code>DECIMAL</code><code>(19,2);</code></p> </td> 
  </tr> 
 </tbody> 
</table>

但是，如果您要遵守公认会计原则(GAAP)规则，则货币栏必须至少包含`4`位小数，以确保舍入值不超过`$0.01`。 在这种情况下，应该定义具有`4`位小数的列，如下所示：

<table> 
 <tbody> 
  <tr> 
   <td> <p>1</p> </td> 
   <td> <p><code>amount&nbsp;</code><code>DECIMAL</code><code>(19,4);</code></p> </td> 
  </tr> 
 </tbody> 
</table>

## MySQL DECIMAL数据类型示例 ##

**第一步**，创建一个名为`test_order`的新表，其中包含三列：`id`，`description`和`cost`。

CREATE TABLE test\_order (  
    id INT PRIMARY KEY AUTO\_INCREMENT,  
    description VARCHAR(255),  
    cost DECIMAL(19, 4) NOT NULL  
);

**第二步**，将资料插入test\_order表。

INSERT INTO test\_order (description, cost)  
VALUES ('Bicycle', 500.34),  
    ('Seat', 10.23),  
    ('Break', 5.21);

**第三步**，从test\_order表查询数据。

`SELECT` `* ``from` `test_order`

![70][]

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

ALTER TABLE test\_order  
    MODIFY COLUMN cost DECIMAL(19, 4) ZEROFILL;

**第五步**，再次查询test\_order表。

![70 1][]

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

因为zerofill，当我们插入负值会报

![70 2][]

第六步、修改DECIMAL类型长度和小数位数，则表里的原始数据会发生截取：

> mysql> ALTER TABLE test\_order MODIFY cost DECIMAL(5,2) zerofill;  
> Query OK, 3 rows affected (0.06 sec)  
> Records: 3  Duplicates: 0  Warnings: 0
> 
> mysql> select \* from test\_order;  
> \+----+-------------+--------+  
> | id | description | cost   |  
> \+----+-------------+--------+  
> |  1 | Bicycle     | 500.34 |  
> |  2 | Seat        | 010.23 |  
> |  3 | Break       | 005.21 |  
> \+----+-------------+--------+  
> 3 rows in set (0.00 sec)

第七步、验证以下结论：

**-- 整数的位数必须小于等于P-D，不然报错。小数的位数可以大于D位。多出D位时会做四舍五入，截取到D位。即当数值在其取值范围之内，小数位多了，则四舍五入后直接截断多出的小数位。**

**-- 以上均不包括小数点、符号的位数。数字的总长度是P位，保存后的小数位最多是D位。如果保存后是整数，小数位不会补0。**

**如下表所示：P=5，D=2**

> mysql> show create table test\_order \\G;  
> \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
>        Table: test\_order  
> Create Table: CREATE TABLE \`test\_order\` (  
>   \`id\` int(11) NOT NULL AUTO\_INCREMENT,  
>   \`description\` varchar(255) DEFAULT NULL,  
>   \`cost\` decimal(5,2) unsigned zerofill DEFAULT NULL, -- 取值范围是 -999.99 到 999.99,  
>   PRIMARY KEY (\`id\`)  
> ) ENGINE=InnoDB AUTO\_INCREMENT=4 DEFAULT CHARSET=latin1  
> 1 row in set (0.00 sec)

**1）小数部分多出位数四舍五入：**

> mysql> INSERT INTO test\_order(description,cost) values('car',123.2123456);  
> Query OK, 1 row affected, 1 warning (0.00 sec)
> 
> mysql> show warnings;  
> \+-------+------+-------------------------------------------+  
> | Level | Code | Message                                   |  
> \+-------+------+-------------------------------------------+  
> | Note  | 1265 | Data truncated for column 'cost' at row 1 |  
> \+-------+------+-------------------------------------------+  
> 1 row in set (0.00 sec)
> 
> mysql> select \* from test\_order;  
> \+----+-------------+--------+  
> | id | description | cost   |  
> \+----+-------------+--------+  
> |  1 | Bicycle     | 500.34 |  
> |  2 | Seat        | 010.23 |  
> |  3 | Break       | 005.21 |  
> **|  4 | car         | 123.21 |**  
> \+----+-------------+--------+  
> 4 rows in set (0.00 sec)

**2）整数的位数必须小于等于P-D，不然报错，**

若数值在其取值范围之外，则直接报Out of range value错误。

**mysql> INSERT INTO test\_order(description,cost) values('cars',3333.2692);**

ERROR 1264 (22003): Out of range value for column 'cost' at row 1

**3）小数部分多出位数四舍五入：**

> mysql> INSERT INTO test\_order(description,cost) values('cars',33.2692) ;  
> Query OK, 1 row affected, 1 warning (0.01 sec)
> 
> mysql> show warnings;  
> \+-------+------+-------------------------------------------+  
> | Level | Code | Message                                   |  
> \+-------+------+-------------------------------------------+  
> | Note  | 1265 | Data truncated for column 'cost' at row 1 |  
> \+-------+------+-------------------------------------------+  
> 1 row in set (0.00 sec)
> 
> mysql> select \* from test\_order;  
> \+----+-------------+--------+  
> | id | description | cost   |  
> \+----+-------------+--------+  
> |  1 | Bicycle     | 500.34 |  
> |  2 | Seat        | 010.23 |  
> |  3 | Break       | 005.21 |  
> |  4 | car         | 123.21 |  
> **|  5 | cars        | 033.27 |**  
> \+----+-------------+--------+  
> 5 rows in set (0.00 sec)

参考：[https://www.cnblogs.com/owenma/p/7097602.html][https_www.cnblogs.com_owenma_p_7097602.html]

[70]: /images/20220506/769d7e53f4e14632930186df4bfc2094.png
[70 1]: /images/20220506/a00f57e5f5394a809807a05cc45cc6f5.png
[70 2]: /images/20220506/a24badb5c0444e0c8136b87d73e85399.png
[https_www.cnblogs.com_owenma_p_7097602.html]: https://www.cnblogs.com/owenma/p/7097602.html