Use float data type with caution in database (reprint)

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)