[object Object]

The task

I was asked to change a MySQL column from FLOAT to DECIMAL(10,2). It sounded like a simple ALTER TABLE, but I needed to understand whether converting the column would preserve the original monetary values.

Why FLOAT can be a problem

FLOAT is an approximate, binary floating-point type. Many decimal fractions cannot be represented exactly in binary, and a FLOAT also has limited precision. This makes it a poor choice for values that require exact decimal arithmetic, such as money.

For example, a FLOAT column may not preserve a large value to the cent:

CREATE TABLE money_float (
  id INT AUTO_INCREMENT PRIMARY KEY,
  amount FLOAT
);

INSERT INTO money_float (amount) VALUES (1000000.25), (123456789.99);
SELECT * FROM money_float;

The exact displayed result can depend on the MySQL version and client, but a single precision FLOAT cannot represent every cent at that magnitude. Inspect and validate the actual values in your database before making a conversion.

Why decimal fractions are approximated

• FLOAT represents values using binary floating-point.
• Fractions such as 0.1 have repeating binary expansions.
• The stored value is therefore an approximation with limited precision.

This is a property of binary floating-point, not unique to MySQL. DOUBLE has more precision than FLOAT, but it is also approximate.

When to use DECIMAL

MySQL's DECIMAL type stores fixed-point decimal values with a chosen precision and scale, making it suitable for exact decimal arithmetic within that range:

CREATE TABLE money_decimal (
  id INT AUTO_INCREMENT PRIMARY KEY,
  amount DECIMAL(15, 2)
);

INSERT INTO money_decimal (amount) VALUES (1000000.25), (123456789.99);
SELECT * FROM money_decimal;

Choose precision and scale to fit the largest values and number of fractional digits your application needs.

Why ALTER may not recover the original value

Changing a FLOAT column to DECIMAL converts the value currently stored in the FLOAT column. It cannot infer the original decimal value if precision was already lost during insertion or calculation.

Before migrating, back up the data, inspect representative values, and compare them with an authoritative source if one is available. Decide on an explicit rounding or correction strategy for values that need repair; do not assume the type conversion can restore lost information.

Lessons learned

• Avoid FLOAT and DOUBLE for monetary values that need exact decimal arithmetic.
• Use DECIMAL(precision, scale) with a range that fits your domain.
• Validate existing data before converting a column, because a type change cannot recover precision that is already gone.

Final thought

For amounts where every cent matters, choose a fixed-point type deliberately and verify how values are written, calculated, and migrated throughout the application.