CREATE DATABASE float_test;
USE float_test;
CREATE TABLE float_table (float_field FLOAT);
INSERT INTO float_table VALUES (21666.66);
SELECT * FROM float_table;
DROP DATABASE float_test;

This outputs 21666.7, despite a 4-bytes float largely being able to store 21666.66 with much more precision (.html) : enter image description here

So what is going on here ? Does MySQL not handle float in a standard way ?

CREATE DATABASE float_test;
USE float_test;
CREATE TABLE float_table (float_field FLOAT);
INSERT INTO float_table VALUES (21666.66);
SELECT * FROM float_table;
DROP DATABASE float_test;

This outputs 21666.7, despite a 4-bytes float largely being able to store 21666.66 with much more precision (https://www.h-schmidt/FloatConverter/IEEE754.html) : enter image description here

So what is going on here ? Does MySQL not handle float in a standard way ?

• mysql
• floating-point
• precision

Share Improve this question Follow edited Mar 3 at 13:59 O. Jones 109k1717 gold badges131131 silver badges183183 bronze badges asked Mar 3 at 8:37 ArcusArcus 2177 bronze badges 9

• db-fiddle/f/fZp6DZ4kTQjcbYsuiL7EuV/0 – mr mcwolf Commented Mar 3 at 8:55
• @mr mcwolf dbfiddle.uk/FwBvBpLV – P.Salmon Commented Mar 3 at 9:10
• dev.mysql/doc/refman/8.4/en/problems-with-float.html – P.Salmon Commented Mar 3 at 9:11
• 2 @P.Salmon, The database stores and serves the data correctly. What the visit is is the behavior of the client. – mr mcwolf Commented Mar 3 at 9:27
• 1 The value STORED and the value DISPLAYED is not the same. dbfiddle.uk/8OBcWyvU – Akina Commented Mar 3 at 9:28

 |  Show 4 more comments

3 Answers 3

Sorted by: Reset to default 2

The discrepancy comes from how MySQL displays float values versus how they're actually stored internally.

This summarises what's happening:

1. MySQL correctly stores 21666.66 as a binary floating-point number according to IEEE 754 When retrieving and displaying the value, MySQL rounds the result for display purposes By default, MySQL only shows a specific number of significant digits

2. The binary representation of 21666.66 in IEEE 754 is actually something like 21666.66015625 (not exactly 21666.66) due to the fundamental limitation of binary floating-point representation. When MySQL displays this value, it applies a rounding rule and shows 21666.7.

If you want to see more precision, you can use the DECIMAL data type instead, which is designed for exact decimal representation

MySQL displays float with 6 digits of precision. When you see 21666.7, this is just the default display, and it doesn't affect the actual value 21666.66, which is stored in memory.

You can use DOUBLE instead of FLOAT if you need more precision in calculations

CREATE TABLE double_table (double_field DOUBLE);

MySQL's default "precision" for FLOAT is 6, meaning 6 digits. 21666.66 has 7 digits and hence is rounded to 21666.7.

SELECT CAST(float_field AS DECIMAL(10,3)) AS f
FROM float_table

This definition of precision clashes with the understanding as the number of digits after the decimal separator. But considering that the number of bits is proportional to the number of decimal digits...

---

Asked for a reference for MySQL

MySQL

FLOAT(M, D)

M is the total number of digits and D is the number of digits following the decimal point. If M and D are omitted, values are stored to the limits permitted by the hardware. A single-precision floating-point number is accurate to approximately 7 decimal places.

This is the closest I found to the magic 7.