Fixed-Point Types (Exact Value) - DECIMAL, NUMERIC

 DECIMAL(M,D)

The DECIMAL and NUMERIC types store exact numeric data values. These types are used when it is important to preserve（保存） exact precision（精度）, for example with monetary data. In MySQL, NUMERIC is implemented（实现） as DECIMAL, so the following remarks about DECIMAL apply equally to NUMERIC.

MySQL 5.6 stores DECIMAL values in binary format. 

In a DECIMAL column declaration, the precision and scale can be (and usually is) specified; for example:

salary DECIMAL(5,2)

In this example, 5 is the precision and 2 is the scale. The precision represents the number of significant（有意义的） digits that are stored for values, and the scale represents the number of digits that can be stored following the decimal point.

Standard SQL requires that DECIMAL(5,2) be able to store any value with five digits and two decimals, so values that can be stored in the salary column range from -999.99 to 999.99.

In standard SQL, the syntax DECIMAL(M) is equivalent to DECIMAL(M,0). Similarly, the syntax DECIMAL is equivalent to DECIMAL(M,0), where the implementation is permitted to decide the value of M. MySQL supports both of these variant forms of DECIMAL syntax. The default value of M is 10.

salary DECIMAL

如下所示：

mysql> create table tb (a float,b decimal);
Query OK, 0 rows affected

mysql> describe tb;
+-------+---------------+------+-----+---------+-------+
| Field | Type          | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+-------+
| a     | float         | YES  |     | NULL    |       |
| b     | decimal(10,0) | YES  |     | NULL    |       |
+-------+---------------+------+-----+---------+-------+
2 rows in set

If the scale is 0, DECIMAL values contain no decimal point or fractional part.

The maximum number of digits for DECIMAL is 65, but the actual range for a given DECIMAL column can be constrained（被强迫的） by the precision or scale for a given column. When such a column is assigned a value with more digits following the decimal point than are permitted by the specified scale, the value is converted to that scale. (The precise behavior is operating system-specific, but generally the effect is truncation to the permissible number of digits.)

The declaration syntax for a DECIMAL column is DECIMAL(M,D). The ranges of values for the arguments in MySQL 5.6 are as follows:

• M is the maximum（最大值） number of digits (the precision（精度）). It has a range of 1 to 65. (Older versions of MySQL permitted a range of 1 to 254.)

• D is the number of digits to the right of the decimal point (the scale). It has a range of 0 to 30 and must be no larger than M.

• M是数字的最大数（精度）。其范围为1～65（在较旧的MySQL版本中，允许的范围是1～254）。

• D是小数点右侧数字的数目（标度）。其范围是0～30，但不得超过M。

DECIMAL类型：该类型用于存储精确的小数。在mysql5.0和更高的版本中，DECIMAL类型支持精确计算。因为CPU不支持对DECIMAL的直接计算，所以在mysql5.0以及更高版本中，mysql服务器自身实现了DECIMAL的高精度计算。相对而言，CPU直接支持原生浮点计算，所以浮点运算明显更快。

浮点和DECIMAL类型都可以指定精度。

对于DECIMAL列，可以指定小数点前后所允许的最大位数。这会影响列的空间消耗。

Values for DECIMAL columns in MySQL 5.6 are stored using a binary format that packs nine decimal digits into 4 bytes. The storage requirements for the integer and fractional（小数的） parts of each value are determined separately. Each multiple of nine digits requires 4 bytes, and any remaining digits left over require some fraction of 4 bytes. The storage required for remaining digits is given by the following table.

例如：DECIMAL(18，9)小数点两边将各存储9个数字，一共使用9个字节：小数点前的数字用4个字节，小数点后的数字用4个字节，小数点本身占一个字节。

Leftover Digits     Number of Bytes

0                        0

1–2                     1

3–4                     2

5–6                     3

7–9                     4

例如：DECIMAL(20,10)列在小数点的每一侧均有10位数字。对于每一部分，9位数字需要4字节，剩余的1位数字需要1字节。

DECIMAL columns in MySQL 5.6 do not permit（允许） values larger than the range implied by the column definition. For example, a DECIMAL(3,0) column supports a range of -999 to 999. A DECIMAL(M,D) column permits at most（最多） M - D digits to the left of the decimal point（小数点左侧最多M-D个数字）.

mysql> create table ta (a float,b decimal(10,5));
Query OK, 0 rows affected
mysql> describe ta;
+-------+---------------+------+-----+---------+-------+
| Field | Type          | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+-------+
| a     | float         | YES  |     | NULL    |       |
| b     | decimal(10,5) | YES  |     | NULL    |       |
+-------+---------------+------+-----+---------+-------+
2 rows in set

精度为10，小数点右侧数字有5位。

decimal(10,5)

插入1234.1234，成功插入（正常范围内的数字）

mysql> insert into ta (a,b) values (1,1234.1234);
Query OK, 1 row affected

mysql> select * from ta where a = 1;
+---+-----------+
| a | b         |
+---+-----------+
| 1 | 1234.1234 |
+---+-----------+
1 row in set

插入123456.12345（小数点左侧数字数目大于M-D）

mysql> insert into ta (a,b) values (2,123456.12345);
Query OK, 1 row affected

mysql> select * from ta where a = 2;
+---+-------------+
| a | b           |
+---+-------------+
| 2 | 99999.99999 |
+---+-------------+
1 row in set

小数点左侧部分有6个数字，超出了M-D的范围，这里直接插入了decimal(10,5)所能表示的最大范围。

插入-12345.12345（正常数字）

mysql> insert into ta (a,b) values (3,-12345.12345);
Query OK, 1 row affected

mysql> select * from ta where a = 3;
+---+--------------+
| a | b            |
+---+--------------+
| 3 | -12345.12345 |
+---+--------------+
1 row in set

插入-123456.12345（小数点左侧数字数目大于M-D）

mysql> insert into ta (a,b) values (4,-123456.12345);
Query OK, 1 row affected

mysql> select * from ta where a = 4;
+---+--------------+
| a | b            |
+---+--------------+
| 4 | -99999.99999 |
+---+--------------+
1 row in set

小数点左侧部分超过了M-D，所以直接插入了decimal(10,5)所能表示的最小数字

插入12345.1234598，小数部分大于D

mysql> insert into ta (a,b) values (5,12345.1234598);
Query OK, 1 row affected

mysql> select * from ta where a = 5;
+---+-------------+
| a | b           |
+---+-------------+
| 5 | 12345.12346 |
+---+-------------+
1 row in set

小数部分直接四舍五入。。。。

====END====

Fixed-Point Types (Exact Value) - DECIMAL, NUMERIC