Fixed-Point types (exact value)-decimal, numeric

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 (SAVE) exact precision (precision), for example with monetary data. in MySQL, numeric is implemented (Implementation) 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 (meaningful) digits that are stored for values, and the scale represents the number of digits that can be stored following the decimal point.

Standard SQLRequires that decimal (999.99) 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.

In standard SQL, the syntax decimal (m) is equivalent to decimal (M, 0). Similarly, the syntaxDecimal 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

As follows:

mysql> create table tb (a float,b decimal);Query OK, 0 rows affectedmysql> 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 (forced) 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 (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 is the maximum number (precision) of a number ). The range is 1 ~ 65 (in older MySQL versions, the allowed range is 1 ~ 254 ).

• D is the number (scale) to the right of the decimal point ). The range is 0 ~ 30, but cannot exceed M.

Decimal type: This type is used to store precise decimals. In MySQL and later versions, the decimal type supports precise calculation. Because the CPU does not support direct decimal computation, in MySQL and later versions, the MySQL server implements high-precision decimal computation. Relatively speaking, the CPU directly supports native floating point computing, so the floating point operation is obviously faster.

Precision can be specified for both the floating point and decimal types.

For a decimal column, you can specify the maximum number of digits allowed before and after the decimal point.. This will affect the column space consumption.

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 (decimal) 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.

For example, decimal () Stores 9 numbers on both sides of the decimal point, using a total of 9 Bytes: the number before the decimal point uses 4 bytes, And the number after the decimal point uses 4 bytes, the decimal point occupies one byte.

Leftover digits number of bytes

0 0

1-2 1

3-4 2

5-6 3

7-9 4

For example, the decimal (20, 10) column has 10 digits on each side of the decimal point. For each part, the 9-digit number requires 4 bytes, and the remaining 1-digit number requires 1 byte.

Decimal columns in MySQL 5.6 do not permit (allowed) values larger than the range implied by the column definition. for example, a decimal (999) column supports a range of-999. A decimal (M, d) column permits at most (max) m-D digits to the left of the decimal point(Maximum number of M-D digits to the left of the decimal point).

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

The precision is 10, and the number to the right of the decimal point has five digits.

decimal(10,5)

Insert 1234.1234, successfully inserted (number in the normal range)

mysql> insert into ta (a,b) values (1,1234.1234);Query OK, 1 row affectedmysql> select * from ta where a = 1;+---+-----------+| a | b         |+---+-----------+| 1 | 1234.1234 |+---+-----------+1 row in set

Insert 123456.12345 (number of digits to the left of the decimal point is greater than the M-D)

mysql> insert into ta (a,b) values (2,123456.12345);Query OK, 1 row affectedmysql> select * from ta where a = 2;+---+-------------+| a | b           |+---+-------------+| 2 | 99999.99999 |+---+-------------+1 row in set

There are 6 digits on the left of the decimal point, out of the M-D range, where the maximum range represented by decimal () is inserted directly.

Insert-12345.12345 (normal number)

mysql> insert into ta (a,b) values (3,-12345.12345);Query OK, 1 row affectedmysql> select * from ta where a = 3;+---+--------------+| a | b            |+---+--------------+| 3 | -12345.12345 |+---+--------------+1 row in set

Insert-123456.12345 (number of digits on the left of the decimal point is greater than the M-D)

mysql> insert into ta (a,b) values (4,-123456.12345);Query OK, 1 row affectedmysql> select * from ta where a = 4;+---+--------------+| a | b            |+---+--------------+| 4 | -99999.99999 |+---+--------------+1 row in set

The left part of the decimal point exceeds the M-D, so the minimum number represented by decimal (10, 5) is inserted directly.

Insert 12345.1234598, decimal part greater than D

mysql> insert into ta (a,b) values (5,12345.1234598);Query OK, 1 row affectedmysql> select * from ta where a = 5;+---+-------------+| a | b           |+---+-------------+| 5 | 12345.12346 |+---+-------------+1 row in set

The fractional part is rounded to the nearest integer ....

==== End ====

Fixed-Point types (exact value)-decimal, numeric