In-depth analysis of MySQL data type DECIMAL

Preface:

When we need to store decimals and have precision requirements, such as storing amounts, we usually consider using the DECIMAL field type. Perhaps most students only have a slight understanding of the DECIMAL type, and the details are not very clear. This article will start from scratch and tell you about the usage scenarios and methods of the DECIMAL field type.

1. Introduction to DECIMAL Type

DECIMAL was introduced in MySQL 5.1. The column declaration syntax is DECIMAL(M,D). NUMERIC is synonymous with DECIMAL. If the field type is defined as NUMERIC, it will be automatically converted to DECIMAL.

For the declaration syntax DECIMAL(M,D), the value ranges of the independent variables are as follows:

• M is the maximum number of digits (precision), ranging from 1 to 65. This value can be left unspecified. The default value is 10.
• D is the number of digits to the right of the decimal point (decimal places). The range is 0 to 30 and cannot be greater than M. It can be left unspecified and the default value is 0.
  

For example, the salary DECIMAL(5,2) field can store any value with five digits and two decimal places, so the range of values ​​that can be stored in the salary column is from -999.99 to 999.99.

2. Actual use of DECIMAL

Next we will create a test table to verify the use of the DECIMAL field type:

# Create a table with a DECIMAL field and verify that the default value of decimal is decimal(10,0)
mysql> create table decimal_tb (col1 decimal,col2 decimal(5,2));
Query OK, 0 rows affected (0.04 sec)
mysql> show create table decimal_tb\G
*************************** 1. row ***************************
    Table: decimal_tb
Create Table: CREATE TABLE `decimal_tb` (
 `col1` decimal(10,0) DEFAULT NULL,
 `col2` decimal(5,2) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

# Insert data test# Conclusion: If the storage range is exceeded, an error will be reported. If the decimal places are insufficient, 0 will be automatically added. If the first digit is 0, it will be automatically ignored. If the decimal places exceed the range, the number will be truncated and rounded.
mysql> insert into decimal_tb (col1,col2) values ​​(100,100);
Query OK, 1 row affected (0.05 sec)
mysql> insert into decimal_tb (col2) values ​​(1.23);
Query OK, 1 row affected (0.01 sec)
mysql> insert into decimal_tb (col2) values ​​(10.2);
Query OK, 1 row affected (0.01 sec)
mysql> insert into decimal_tb (col2) values ​​(09.9);
Query OK, 1 row affected (0.01 sec)
mysql> select * from decimal_tb;
+------+--------+
| col1 | col2 |
+------+--------+
| 100 | 100.00 |
| NULL | 1.23 |
| NULL | 10.20 |
| NULL | 9.90 |
+------+--------+
4 rows in set (0.00 sec)
mysql> insert into decimal_tb (col2) values ​​(9999);
ERROR 1264 (22003): Out of range value for column 'col2' at row 1
mysql> insert into decimal_tb (col2) values ​​(12.233); 
Query OK, 1 row affected, 1 warning (0.01 sec)
mysql> show warnings;
+-------+------+-------------------------------------------+
| Level | Code | Message |
+-------+------+-------------------------------------------+
| Note | 1265 | Data truncated for column 'col2' at row 1 |
+-------+------+-------------------------------------------+
1 row in set (0.00 sec)
mysql> insert into decimal_tb (col2) values ​​(12.2300);
Query OK, 1 row affected (0.01 sec)

# Variable range test# Conclusion: M ranges from 1 to 65, D ranges from 0 to 30, and D is not greater than M
mysql> alter table decimal_tb add column col3 decimal(6,6);
Query OK, 0 rows affected (0.12 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table decimal_tb add column col4 decimal(6,7); 
ERROR 1427 (42000): For float(M,D), double(M,D) or decimal(M,D), M must be >= D (column 'col4').
mysql> alter table decimal_tb add column col4 decimal(65,2);
Query OK, 0 rows affected (0.11 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table decimal_tb add column col4 decimal(66,2);
ERROR 1426 (42000): Too-big precision 66 specified for 'col4'. Maximum is 65.
mysql> alter table decimal_tb add column col5 decimal(60,30); 
Query OK, 0 rows affected (0.13 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table decimal_tb add column col6 decimal(60,31);
ERROR 1425 (42000): Too big scale 31 specified for column 'col6'. Maximum is 30.

3. Summary of DECIMAL usage

The above content introduces the usage and precautions of the DECIMAL type from a practical perspective. You may also know floating-point types such as float and double, which can also store decimals, but I will not introduce them in detail here. I just want to remind you that float and double types cannot ensure accuracy and are prone to errors, especially when performing summation calculations. Therefore, it is recommended to use the DECIMAL type when storing decimals, especially when involving amounts. Here is a summary of what you should pay attention to when using DECIMAL:

• In DECIMAL(M,D), the range of M is 1 to 65, and the range of D is 0 to 30.
• The default value of M is 10, the default value of D is 0, and D is not greater than M.
• The storage range of DECIMAL(5,2) is from -999.99 to 999.99. If the storage range is exceeded, an error will be reported.
• When storing values, insufficient decimal places will be automatically padded with 0, and the first digit will be automatically ignored if it is 0.
• If the number exceeds the decimal point, it will be truncated, an alarm will be generated, and the number will be rounded up.
• When using DECIMAL fields, it is recommended that the M and D parameters be specified manually and allocated as needed.
  

Summarize:

This article is relatively simple and practical. After reading it, you will probably understand the usage scenarios and precautions of DECIMAL fields. In fact, for common field types, we only need to understand their usage scenarios and precautions. When we build a table, our goal is to be able to quickly select the appropriate field type. For example, when we need to store decimals, we can use the DECIMAL type and select the appropriate precision according to business needs, so that our work will be easy to carry out.

The above is the detailed content of the in-depth analysis of the MySQL data type DECIMAL. For more information about the MySQL data type DECIMAL, please pay attention to other related articles on 123WORDPRESS.COM!