Mysql 简明教程

MySQL - DECIMAL

The MySQL Decimal Data Type

MySQL DECIMAL 数据类型用于存储带有小数点的数值。它允许进行精确的计算，可以配置为在小数点前后存储指定数量的数位。

The MySQL DECIMAL data type is used to store numeric values with decimal points. It allows for precise calculations and can be configured to store a specified number of digits before and after the decimal point.

我们经常将此数据类型用于需要精确精度的列，例如雇员的工资、雇员公积金余额等。

We often use this datatype for the columns that require exact precision such as salaries of employees, employee PF balances, etc.

Syntax

以下是在定义一个数据类型为 DECIMAL 的列的语法：

Following is the syntax to define a column whose data type is DECIMAL −

column_name  DECIMAL(P,D);

其中，

Where,

  1. P is called precision which specifies the total number of significant digits that can be stored in the column, both to the left and right of the decimal point. The range of P is 1 to 65.

  2. D is a scale that specifies the maximum number of digits that can be stored after the decimal point. The range of D should be between 0 and 30 and D is less than or equal to (⇐) P.

例如，如果我们定义一个列为 DECIMAL(10,2)，它最多可以存储 10 位数的数字，最多可有 2 位数字位于小数点右侧。

For instance, if we define a column as DECIMAL(10,2), it can store numbers with up to 10 digits, and up to 2 digits to the right of the decimal point.

Attributes

DECIMAL 关键字有两个属性： UNSIGNED 和 ZEROFILL 。

The DECIMAL keyword has two attributes: UNSIGNED and ZEROFILL.

  1. UNSIGNED − When used, it indicates that the column does not accept negative values.

  2. ZEROFILL − If used, it pads the number with zeros to the specified width.

Precision and Scale

在以下查询中，我们定义了一个名为 SALARY 的列，其数据类型为 DECIMAL，指定精度为 5，刻度为 3：

In the following query, we define a SALARY column with the DECIMAL data type, specifying a precision of 5 and a scale of 3 −

SALARY decimal(5,3)

此定义意味着 SALARY 列可以存储值，该值最多为 5 位数，包括小数点右边的 3 位数。此列的范围为 99.999 到 -99.999。

This definition means that the SALARY column can store values with up to 5 digits in total, including 3 digits to the right of the decimal point. The range for this column would be from 99.999 to -99.999.

No Decimal Places

在此处，SALARY 列不包含小数部分或小数点。以下两个查询相同 -

In here, the SALARY column contains no fractional part or decimal point. The following two queries are same −

SALARY DECIMAL(5);
SALARY DECIMAL(5,0);

这两个声明都表明 SALARY 列可以存储没有小数点的整数值。

Both declarations indicate that the SALARY column can store values as whole numbers without decimal places.

Default Precision

如果您省略精度值，则默认精度 P 将为 10 -

If you omit the precision value, the default precision P will be 10 −

SALARY DECIMAL;

MySQL DECIMAL Storage

MySQL 使用可以优化存储的二进制格式来存储“DECIMAL”数据类型的值。具体而言，MySQL 将 9 个数字打包到 4 个字节中。整型部分和小数部分分开分配存储，每组 9 个数字使用 4 个字节。任何剩余数字都需要额外的存储空间。

MySQL stores values of the "DECIMAL" data type using a binary format that optimizes storage. Specifically, MySQL packs 9 digits into 4 bytes. Storage is allocated separately for the integer and fractional parts, with 4 bytes used for each set of 9 digits. Any remaining digits require additional storage.

剩余（剩余）数字所需的存储空间在以下表中演示：

The storage required for remaining (leftover) digits is demonstrated in the following table:

考虑一个 DECIMAL(30,9) 列，它对小数部分有 9 位数字，对整数部分有 30 - 9 = 21 位数字。在这种情况下，小数部分占据 4 个字节。整数部分前 18 位数字占用 8 个字节，而对于剩余的 3 位数字，它需要额外的 2 个字节。因此， DECIMAL(30,9) 列总共需要 14 个字节。

Consider a DECIMAL(30,9) column, which has 9 digits for the fractional part and 30 - 9 = 21 digits for the integer part. In this case, the fractional part takes 4 bytes. The integer part takes 8 bytes for the first 18 digits, and for the leftover 3 digits, it requires an additional 2 bytes. Therefore, the DECIMAL(30,9) column requires a total of 14 bytes.

Example

为了进一步理解这一点，让我们使用以下查询创建一个名为 EMPLOYEES 的表 -

To further understand this, let us create a table named EMPLOYEES using the following query −

CREATE TABLE EMPLOYEES (
   ID int NOT NULL AUTO_INCREMENT,
   NAME varchar(30) NOT NULL,
   SALARY decimal(14,4) NOT NULL,
   PRIMARY KEY (ID)
 );

使用以下查询，我们正在向上面创建的表中插入一些记录 -

Using the following query, we are inserting some records into the above created table −

INSERT INTO EMPLOYEES (NAME, SALARY) VALUES
("Krishna", 150050.34),
("Kalyan", 100000.65);

得到的 EMPLOYEES 表如下 -

The EMPLOYEES table obtained is as follows −

使用以下查询，我们正在在“SALARY”列中包含 ZEROFILL 属性 -

Using the following query, we are including the ZEROFILL attribute in the "SALARY" column −

ALTER TABLE EMPLOYEES
MODIFY SALARY decimal(14, 4) zerofill;

以下是以上查询的输出：

Following is the output of the above query −

Query OK, 2 rows affected, 1 warning (0.03 sec)
Records: 2  Duplicates: 0  Warnings: 1

在这里，我们正在尝试在 SALARY 列中包括 ZEROFILL 属性之后，从 EMPLOYEES 表中获取所有记录 -

Here, we are trying to fetch all the records from the EMPLOYEES tables after including the ZEROFILL attribute on SALARY column −

SELECT * FROM EMPLOYEES;

记录将显示基于“SALARY”列中指定范围填充的零 -

The records will display zeros padded based on the range specified in the "SALARY" column −

Decimal Datatype Using a Client Program

我们还可以使用客户端程序创建小数数据类型列。

We can also create column of the decimal datatype using the client program.

Syntax

Example

以下是这些程序 −

Following are the programs −