Why 1 + 1 = 1.999999999 in MySQL? Understanding Floating-Point Errors

🧮 MySQL Calculation Issue: Why 1 + 1 = 1.999999999?

If you’ve ever seen something like 1 + 1 = 1.999999999 in MySQL, you’re not alone — and no, MySQL isn’t broken. This is a floating-point precision issue common in many programming and database systems.

Let’s break it down.

---

🔍 Why is this happening?

MySQL uses IEEE 754 floating-point arithmetic for decimal numbers when you use data types like FLOAT or DOUBLE. These types cannot always represent decimal numbers exactly, leading to small rounding errors.

So, when you write:

SELECT 1 + 1.0;

You might expect 2.0, but due to floating-point precision limits, the actual stored value could be something like 1.9999999990687, especially with certain math operations.

---

⚠️ Example Problem

SELECT 0.1 + 0.2;
-- Output: 0.30000000000000004

This is a classic floating-point issue — not just in MySQL but in JavaScript, Python, and many other systems.

---

✅ How to Fix It

Here are several ways to avoid or fix this issue in MySQL:

1. Use DECIMAL Instead of FLOAT or DOUBLE

If you’re doing financial calculations or need exact precision, use the DECIMAL type:

CREATE TABLE payments (
  amount DECIMAL(10, 2)
);

DECIMAL stores exact numeric values as strings internally — no floating-point rounding!

---

2. Use ROUND() to Control Output

You can round the result to a specific number of decimal places:

SELECT ROUND(1 + 1.0, 2);
-- Output: 2.00

This won’t eliminate the underlying issue, but it hides the noise.

---

3. Format Numbers for Display

If you just want clean display results, use FORMAT():

SELECT FORMAT(1 + 1.0, 2);
-- Output: "2.00" (as a string)

---

🧠 Summary

Problem	Cause	Solution
1 + 1 = 1.999999999	Floating-point precision	Use DECIMAL instead
0.1 + 0.2 = 0.30000000000000004	IEEE 754 rounding issues	Use ROUND() or FORMAT()
Inaccurate financial calculations	Using FLOAT or DOUBLE	Use DECIMAL(10, 2)

---

🔚 Final Advice

• Use FLOAT/DOUBLE only when performance is more important than precision (e.g., scientific data).
• Use DECIMAL for money, totals, or exact math.
• Always round or format outputs if they are for end-users.

---

Would you like a SQL script example for switching a FLOAT column to DECIMAL, or a short LinkedIn post to share this tip?