MySQL DECIMAL vs FLOAT: Exact and Approximate Numbers

Numbers with a fractional part come in two kinds: exact (DECIMAL) and approximate (FLOAT, DOUBLE). The difference is the source of many bugs.

Why approximate numbers are approximate

Computers store FLOAT and DOUBLE in binary. Some decimal fractions, such as 0.1, cannot be written exactly in binary, just as 1/3 cannot be written exactly in decimal. The tiny errors are invisible until you compare or add up many numbers.

query.sql

SQL

1
2

SELECT 0.1 + 0.2 = 0.3 AS decimal_literals,
       0.1e0 + 0.2e0 = 0.3e0 AS float_literals;

Output

OUTPUT

+------------------+----------------+
| decimal_literals | float_literals |
+------------------+----------------+
|                1 |              0 |
+------------------+----------------+
1 row in set

Plain 0.1 in MySQL is a DECIMAL literal, so the sum is exact. 0.1e0 is a floating-point literal, and 0.1e0 + 0.2e0 does not equal 0.3e0.

A column makes the difference visible:

query.sql

SQL

1
2
3

CREATE TABLE amounts (id INT, as_float FLOAT, as_double DOUBLE, as_decimal DECIMAL(10,2));
INSERT INTO amounts VALUES (1, 0.1, 0.1, 0.10), (2, 0.2, 0.2, 0.20);
SELECT SUM(as_float) AS float_sum, SUM(as_double) AS double_sum, SUM(as_decimal) AS decimal_sum FROM amounts;

Output

OUTPUT

+---------------------+---------------------+-------------+
| float_sum           | double_sum          | decimal_sum |
+---------------------+---------------------+-------------+
| 0.30000000447034836 | 0.30000000000000004 |        0.30 |
+---------------------+---------------------+-------------+
1 row in set

The FLOAT and DOUBLE sums are close to 0.3 but not exactly 0.3. The DECIMAL sum is exactly 0.30.

DECIMAL(precision, scale)

DECIMAL(10,2) means 10 digits in total, 2 after the decimal point. The largest value is 99999999.99.

query.sql

SQL

1
2
3

CREATE TABLE prices (amount DECIMAL(6,2));
INSERT INTO prices VALUES (1234.5), (0.999), (9999.99);
SELECT amount FROM prices;

Output

OUTPUT

+---------+
| amount  |
+---------+
| 1234.50 |
|    1.00 |
| 9999.99 |
+---------+
3 rows in set

The value 1234.5 is stored as 1234.50. The value 0.999 has too many decimal places, and MySQL rounds it to 1.00 (and gives a note in the warnings). A number with too many digits before the point is an error in strict mode:

query.sql

SQL

INSERT INTO prices VALUES (12345.67);

Output

OUTPUT

ERROR 1264 (22003): Out of range value for column 'amount' at row 1

Which one to use

Use	Type	Why
Money, tax, prices	`DECIMAL(10,2)` or, with more precision, `DECIMAL(19,4)`	Exact. Sums and comparisons are right.
Percentages, rates	`DECIMAL(5,2)`	Exact and predictable
Measurements (temperature, distance, sensor values)	`DOUBLE`	Approximation is natural, and the range is large
Science, statistics, machine-learning scores	`DOUBLE` (or `FLOAT` to save space)	Speed and range
Coordinates	`DECIMAL(9,6)` or `DOUBLE`	`DECIMAL(9,6)` gives about 10 cm of precision for latitude and longitude

Rule: if a person will count it or pay with it, use DECIMAL.

Another way to store money

Some systems store money as integers in the smallest unit: amount_paise INT, where ₹12.50 is 1250. It avoids all decimal questions and is fast. Document the unit clearly (column comment, Invisible Columns and Table Comments). Both approaches are valid. Never use FLOAT for money.

Do not compare floats with =

query.sql

SQL

1
2
3

CREATE TABLE readings (v DOUBLE);
INSERT INTO readings VALUES (0.3e0), (0.1e0 + 0.2e0);
SELECT v, v = 0.3 AS equals_point_three, ABS(v - 0.3) < 0.000001 AS close_enough FROM readings;

Output

OUTPUT

+---------------------+--------------------+--------------+
| v                   | equals_point_three | close_enough |
+---------------------+--------------------+--------------+
|                 0.3 |                  1 |            1 |
| 0.30000000000000004 |                  0 |            1 |
+---------------------+--------------------+--------------+
2 rows in set

Compare floating-point numbers with a small tolerance, not with =.

info

MySQL 8.4 change

Using AUTO_INCREMENT on a FLOAT or DOUBLE column was deprecated for years and was removed in MySQL 8.4: CREATE TABLE x (id FLOAT AUTO_INCREMENT PRIMARY KEY) now fails with error 1063, while MySQL 8.0 still accepts it. Use an integer column. Also avoid the old FLOAT(M,D) and DOUBLE(M,D) syntax, which is deprecated. Write plain FLOAT or DOUBLE, and round when you display.