Use float data types with caution in the database

Use float data in databases with caution. Most programming languages Support float or double data types. The database also has data types with the same keywords. Therefore, many developers naturally use float as the field type where floating point numbers are needed. Www.2cto.com, but does float actually apply to all business scenarios? The float type uses binary format to encode real data according to the IEEE 754 standard. For some decimal places, such as 59.95, the float type stores the value closest to 59.95 in binary, which is expressed in decimal format equal to 59.950000762939. Of course, some databases can make up for the inaccuracy of such data in some way, and the query results can output the expected values. As shown in the following figure: the SQL code select rate from t_refresh where id = 1; Returns: 59.95 However, If you increase this value by one billion times: www.2cto.com SQL code select rate * 1000000000 from t_refresh where id = 1; Return: 59950000762.939 this may be different from the expected result 59950000000.000. In the above example, the error is within one thousandth of a thousand, which is sufficient for some operations. However, in some operations, such errors cannot be tolerated, such as comparison operations: SQL code select * from t_refresh where rate = 59.95 Result: empty set; no rows match, because the actual storage value of rate is a little larger than 59.95. Another example is to calculate compound interest in financial projects. Multiple floating-point multiplication operations are required. Using the float type will cause errors to accumulate. Www.2cto.com therefore, in some business scenarios, we need to use numeric or decimal to replace the float data type. Compared with the float type, numeric and decimal store precise values. If you insert a value of 59.95, the actual storage is 59.95. So in the above example, if you use numeric or decimalSql code select rate * 1000000000 from t_refresh where id = 1; Return: 59950000000 SQL code select id from t_refresh where rate = 59.95 Return: 1 Conclusion: float is applicable to scenarios with low precision requirements and high precision requirements for financial and statistical computing scenarios with a large Numerical range. numeric or decimal is required.