Precision Issues with Float

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: float精确问题

| username: TiDBer_a0wVPNxf

The field type is float(8,2). I don’t know why the approximate value of 131072.32 is 131072.31.

| username: Billmay表妹 | Original post link

Based on the information you provided, the field type float(8,2) indicates that the field is a floating-point number, occupying a total of 8 bytes of storage space and retaining two decimal places.

In the storage and computation of floating-point numbers, due to the limitations of the binary representation of floating-point numbers, precision loss may occur. This is because the binary representation of floating-point numbers cannot accurately represent certain decimal fractions.

In your example, 131072.32 is a decimal fraction, but it may not be accurately represented in the binary representation of floating-point numbers. Therefore, when storing this decimal fraction in a field of type float(8,2), an approximate value may appear.

To represent decimals more accurately, you might consider using the decimal type instead of the float type. The decimal type stores numbers as strings and can accurately represent the specified number of decimal places.

For example, you can change the field type to decimal(8,2), which will use 8 bytes of storage space and retain two decimal places. This way, 131072.32 will be stored and represented accurately.

Please note that using the decimal type may result in some performance loss because it requires more storage space and computational resources. Therefore, when choosing a data type, please weigh it against actual needs and performance requirements.

| username: Kongdom | Original post link

This should be a known float precision issue.

131072.32 converted to binary
100000000000000000.0101000111101011100001010001111010111000010100011111
Truncate to 24 bits
100000000000000000.010100
Convert back to decimal
131072.3125
Round according to precision
131072.31

Float and double may lose precision, while decimal does not, so it is recommended to use decimal to store monetary values.

| username: 大飞哥online | Original post link

It’s better to use decimal for money, the precision is accurate.

| username: TiDBer_a0wVPNxf | Original post link

So float is converted to binary for storage, but decimal is not?

| username: 像风一样的男子 | Original post link

The rounding mode of MySQL database: decimal uses round half up, while float and double use round half to even.
Round down: if the digit is less than or equal to 4, it is rounded down.
Round up: if the digit is greater than or equal to 6, it is rounded up.
Round half to even: if the digit is 5, check the previous digit. If it is even, it remains the same; if it is odd, it is rounded up. If the digit after 5 is greater than 0, it is rounded up; if it is 0, it is rounded down.

| username: TiDBer_a0wVPNxf | Original post link

A float occupies 4 bytes in memory, while a double occupies 8 bytes in memory. The storage of floating-point numbers follows the IEEE754 standard, where the exponent is represented using a biased notation and the mantissa is represented using the original value, with the leading 1 omitted. Therefore, the mantissa for float is 24 bits, and for double, it is 64 bits.

For float: Exponent in biased notation = 011111111 + offset
For double: Exponent in biased notation = 01111111111 + offset

————————————————
Copyright Notice: This article is an original piece by the CSDN blogger “xiebs_”, and it follows the CC 4.0 BY-SA copyright agreement. Please include the original source link and this statement when reprinting.
Original link: 4、浮点数的秘密_double尾数-CSDN博客

| username: Kongdom | Original post link

Yes, so generally it’s better to use decimal.

| username: TiDBer_小阿飞 | Original post link

Learned.

| username: Kongdom | Original post link

:handshake: :handshake: :handshake:

| username: system | Original post link

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.