DECIMAL(M,D) stores an exact number of up to 65 digits, D of them after the point: the bookshop's DECIMAL(8,2) prices reach 999,999.99 in 4 bytes. FLOAT (4 bytes) and DOUBLE (8 bytes) store approximate binary fractions, and most decimals, 0.1 among them, have no exact binary form.
CREATE TABLE money (d DECIMAL(8,2), f FLOAT);
INSERT INTO money VALUES (0.1, 0.1), (0.2, 0.2), (39.90, 39.90);
SELECT SUM(d), SUM(f), SUM(f = 39.9) AS f_hits, 0.1 + 0.2 = 0.3 AS exact,
0.1e0 + 0.2e0 = 0.3e0 AS approx, ROUND(2.5) AS r_dec, ROUND(2.5e0) AS r_dbl FROM money;
INSERT INTO money (d) VALUES (12.345), (1234567.8);
INSERT INTO money (d) VALUES (12.345);
SELECT d FROM money WHERE f IS NULL;Output
+--------+--------------------+--------+-------+--------+-------+-------+ | SUM(d) | SUM(f) | f_hits | exact | approx | r_dec | r_dbl | +--------+--------------------+--------+-------+--------+-------+-------+ | 40.20 | 40.200001530349255 | 0 | 1 | 0 | 3 | 2 | +--------+--------------------+--------+-------+--------+-------+-------+ ERROR 1264 (22003): Out of range value for column 'd' at row 2 Note (Code 1265): Data truncated for column 'd' at row 1 +-------+ | d | +-------+ | 12.35 | +-------+
The FLOAT sum drifts and the stored 39.90 no longer equals 39.9. ROUND() rounds exact values half away from zero but approximate ones to even. An extra DECIMAL decimal place is rounded with only a note, even in strict mode, while an extra integer digit fails the whole statement. Use DECIMAL for money, DOUBLE for measurements. FLOAT(7,4) digit counts and UNSIGNED on these types are deprecated; use CHECK (price >= 0) (UNIQUE, NOT NULL, CHECK).