Shulou(Shulou.com)06/01 Report--

Foreword:

When we need to store decimals and have precision requirements, such as storage amount, we usually consider using the DECIMAL field type. Most students may only have a slight understanding of the DECIMAL type, but the details are not very clear. This article will tell you how to use the DECIMAL field type from scratch.

Introduction to 1.DECIMAL Typ

DECIMAL was introduced from MySQL 5. 1, and the declaration syntax for columns is DECIMAL (MMagne D). NUMERIC is synonymous with DECIMAL, and if the field type is defined as NUMERIC, it is automatically converted to DECIMAL.

For the declaration syntax DECIMAL (MQuery D), the value range of the argument is as follows:

M is the maximum number of digits (precision), ranging from 1 to 65. Can not be specified, the default value is 10. D is the number of places to the right of the decimal point (decimal places). The range is 0 to 30 and cannot be greater than M, but is not specified, and the default value is 0.

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

2.DECIMAL uses the actual combat

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

# create a table with DECIMAL field to verify that decimal defaults to decimal (1010) mysql > create table decimal_tb (col1 decimal,col2 decimal (5)) Query OK, 0 rows affected (0.04 sec) mysql > show create table decimal_tb\ Gmail * 1. Row * * Table: decimal_tbCreate Table: CREATE TABLE `decimal_ tb` (`col1` decimal (10mem0) DEFAULT NULL `col2` decimal (5pm 2) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf81 row in set (0.00 sec) # insert data test # conclusion: an error will be reported if it is beyond the storage range The shortage of decimal places will automatically fill in 0, the first digit 0 will be automatically ignored, and the excess of decimal places will be truncated and rounded. Mysql > insert into decimal_tb (col1,col2) values (100100); Query OK, 1 row affected (0.05sec) 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.01sec) mysql > insert into decimal_tb (col2) values (09.9); Query OK, 1 row affected (0.01sec) mysql > select * from decimal_tb +-+-+ | col1 | col2 | +-+-+ | 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 1mysql > 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 range is 1 to 65, D range is 0 to 30, and D is not greater than Mmysql > alter table decimal_tb add column col3 decimal (6 sec 6); Query OK, 0 rows affected (0.12 sec) Records: 0 Duplicates: 0 Warnings: 0mysql > alter table decimal_tb add column col4 decimal (6 Warnings 7) ERROR 1427 (42000): For float (MMagol D), double (MMagne D) or decimal (MMagne D), M must be > = D (column 'col4'). MySQL > alter table decimal_tb add column col4 decimal (65 sec 2); Query OK, 0 rows affected (0.11 sec) Records: 0 Duplicates: 0 Warnings: 0mysql > alter table decimal_tb add column col4 decimal (66 Magazine 2); ERROR 1426 (42000): Too-big precision 66 specified for' col4'. Maximum is 65.mysql > alter table decimal_tb add column col5 decimal (60 sec 30); Query OK, 0 rows affected (0.13 sec) Records: 0 Duplicates: 0 Warnings: 0mysql > alter table decimal_tb add column col6 decimal (60 specified for column 31); ERROR 1425 (42000): Too big scale 31 specified for column 'col6'. Summary of Maximum is 30.3.DECIMAL usage

The above content from the actual combat, introduced the use of DECIMAL type and matters needing attention, you may also know these floating-point types float, double, these two can also store decimals, but here is not too much introduction, just remind you that float, double type can not ensure accuracy, it is easy to produce errors, especially in the sum calculation, all when storing decimals, especially when involving the amount of recommended DECIMAL type. Here's a summary of what you should pay attention to when using DECIMAL:

In DECIMAL, the range of M is 1 to 65 and the range of D is 0 to 30. M defaults to 10, D defaults to 0, and D is not greater than M. DECIMAL (5d2) can store from-999.99 to 999.99. If you exceed the storage range, an error will be reported. When storing a value, the lack of decimal places will automatically fill in 0, and the first digit 0 will be automatically ignored. If the decimal place exceeds, it will be truncated, an alarm will be generated, and it will be rounded. When using the DECIMAL field, it is recommended that the MPerry D parameter be specified manually and assigned as needed.

Summary:

This article is relatively simple and practical, read through, you will probably understand the use of DECIMAL field scenarios and matters needing attention, in fact, for common field types, we only need to understand their use scenarios and matters needing attention. When we build a table, we can quickly select the appropriate field type is our goal, for example, when we need to store decimals, we can use the DECIMAL type and select the appropriate precision according to business needs. In this way, our work will be easy to carry on.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.