Use float data type in the database with caution _ MySQL

Source: Internet
Author: User
Use float data type bitsCN.com in the database with caution

Use float data types with caution in the database

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.

But is float applicable 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 below:

SQL code

Select rate from t_refresh where id = 1;

Returns: 59.95

However, if you increase the value by one billion times:

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.

Therefore, in some business scenarios, we need to replace the float data type with numeric or decimal.

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 numeric or decimal is used

SQL 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 computing scenarios with low precision requirements and a large numerical range.

For scenarios with high precision requirements such as finance and statistics, numeric or decimal is required.

BitsCN.com
Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.