Application of MySQL Decimal

It is possible for a program to know that the float type is a floating point number (that is, a decimal type), but there is a downside to float, and when the data you give is an integer, it is treated as an integer. So we have a natural problem accessing the currency value, My default value is: 0.00 and the actual storage is 0, the same as my access currency is 12.00, the actual storage is 12. Fortunately, MySQL offers two data types: NUMERIC and decimal, which can be easily addressed by the two data types: NUMERIC and The DECIMAL type is implemented by MySQL in the same type, which is allowed in the SQL92 standard. They are used to hold values that have important requirements for accurate precision, such as money-related data. When declaring a column with one of them, the precision and numeric range can (usually) be specified; For example: Salary decimal (5,2) In this example, 5 (precision (precision)) represents the number of important decimal digits, 2 (data range (scale)) Represents the number of digits after the decimal point. In this case, therefore, the salary column can store a range of values from 99.99 to 99.99. (In fact, the value that MySQL can store in this column can be up to 999.99, because it doesn't store a positive sign). Translator Note: Influence of M and D on the value range of decimal (M, d) Type description value range (MySQL < 3.23) range (MySQL >= 3.23)

=================================================

Float,decimal accuracy comparison float,double prone to error, when the accuracy requirements are relatively high, it is recommended to use decimal to save, decimal in MySQL memory is a string storage, used to define the currency requirements of high precision data. In data migration, float (m,d) is a non-standard definition and should not be used in this way. M is precision and D is the scale. Mysql>  create table t1 (C1 float (10,2),  c2 decimal (10,2), c3 float);  // 10 does not include the decimal point mysql>  insert into t1 values (1234567.23,  1234567.23,1234567.23);mysql>  select * from t1; data display is related to the client tools used 1. In Toad for The display in  mysql is c1                     c2                             c31234567          1234567.23                     12345702. In mysql query  in browserThe display is c1                     c2                             c31234567.25     1234567.23        1.23457e+0063. Removing data from the program 1234567           1234567.23         1234570mysql> insert into t1 values (9876543.21, 9876543.12,9876543.21); 1. In Toad The display in  for mysql is c1                     c2                              C39876543       9876543.12            98765402. In MySQL The display in  query browser is c1                     c2                              c39876543.00   9876543.12        9.87654e+0063. Remove data from the program 9876543         9876543.12         9876540mysql>  insert into t1 values (1.21, 1.12,1.21); 1. Display as c1              in Toad for mysql        c2                             c31.21                 1.12                        1.212. In MySQL The display in  query browser is c1                     c2                             c31.21                1.12                           1.213. Removing data from the program 1.21                 1.12                         1.21mysql>  insert into t1 values ( 1.2, 1.2,1.2); 1. The display in Toad for mysql is c1                     c2                              c31.2                   1.20                         1.22. Display as C1 in Mysql query browser                      c2                             c31.20                 1.20                          1.23. Extracting data from a program 1.2                     1.20                         1.2mysql>  insert into t1 values ( 9876543.216, 9876543.126,9876543.216); 1. The display in Toad for mysql is c1                     c2                              c39876543        9876543.13            98765402. Display as c1      in Mysql query browser                c2                              c39876543.00   9876543.13           9.87654e+0063. Removing data from the program 9876543          9876543.13          9876540mysql>   Insert into t1 values (1.216, 1.126,1.216); 1. Display as c1   in Toad for mysql                   c2                              c31.22                 1.13                         1.2162. In mysql query  The display in browser is c1                     c2                             c31.22                1.13                         1.2163. Removing data from the program 1.22                1.13                          1.216 does not define the accuracy and scale of fload, double, storage is stored by the value given, which is related to the OS and current hardware. decimal defaults to decimal (10,0) Because of the error problem, in the program, less with floating-point numbers do = comparison, you can do range comparison. If numeric comparisons, it is best to use the decimal type. accuracy, the symbol does not count: Mysql> insert into t1 values ( -98765430.21, -98765430.12);mysql>  select * from t1;c1                        c2          -98765432.00        -98765430.12

This article is from the "it--Brother" blog, please make sure to keep this source http://402753795.blog.51cto.com/10788998/1827568

Application of MySQL Decimal