Use the float data type with caution in the database most programming languages support a float or double data type. There are also data types for the same keywords in the database, so many developers naturally use float as a field type where floating-point numbers are needed. www.2cto.com But is it true that float can be applied to all business scenarios? The float type encodes real data using the binary format in accordance with the IEEE 754 standard, and for some decimals, such as the 59.95,float type, stores the value closest to 59.95 in binary and equals 59.950000762939 in decimal notation. Of course, some databases can somehow compensate for the inaccuracy of this data, and the results of the query can output the values we expect. As shown below: sql code select rate from T_refresh where id =1; returns:59.95 However, if you expand this value by 1 billion times times: www.2cto.com sql code select rate * 1000000000 from T_refresh where id =1; return:59950000762.939 This may not be the same as the result you expect 59950000000.000. In the example above, the error is within one out of 10,000 and is sufficient for the partial operation. However, in some operations, such errors are not tolerated, such as comparisons of operations: SQL code select * from t_refresh where rate = 59.95 result:empty set;no rows Match because the actual storage value for rate is a little bit larger than 59.95. In the case of compounding in financial projects, multiple floating-point multiplication is required, and the use of the float type results in constant error accumulation. www.2cto.com Therefore, in some business scenarios, we need to replace the float data type with numeric or decimal. Compared to the float type, numeric and decimal store accurate values, and if you insert in a 59.95, the actual deposit is also 59.95. So in the example above, if using 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 suitable for scientific operation scenarios with low precision and large numerical range
In the case of high accuracy requirements such as finance and statistics, it is necessary to use numeric or decimal
Use float data type with caution in database (reprint)