MySQL conversion from float (10,2) to fixed(10,2)

Question

I have a database that contains financial data stored as floats. Naturally, this can present a real issue at random times (eg, when there is an exact value that cannot be approximated closely enough with a floating-point algorithm).

My question is:

• Will using ALTER TABLE table MODIFY field fixed(10,2) preserve the fixed value that is currently shown by MySQL when you select the field?
• Is there any possibility that the value would be something other than that?

Answer 1

BAD NEWS : I do not think the value will be preserved !!!

I wrote this post (July 25, 2011) showing how sensitive floating point numbers are to conversions.

GOOD NEWS : Here is how you can safely compare the data conversion:

If your table has these characteristics

• table is called moneytable
• field you wish to convert is called moneyfield
• primary key is called moneyid

Then run these commands:

DROP TABLE IF EXISTS moneytabletest;
CREATE TABLE moneytabletest LIKE moneytable;
ALTER TABLE moneytabletest MODIFY moneyfield fixed(10,2);
INSERT INTO moneytabletest SELECT * FROM moneytable;
SELECT A.moneyid,A.moneyfield olddata,B.moneyfield newdata
FROM moneytable A LEFT JOIN moneytabletest B USING (moneyid)
WHERE A.moneyfield <> B.moneyfield;

Here is the bottom line: If any rows come back from the LEFT JOIN query, the conversion will be bad. You will have to modify the definition of moneyfield, reload the moneytabletest table, and run the LEFT JOIN query again and again until no rows come back from the LEFT JOIN query. Once zero(0) rows come back, you will know what conversion will be safe.

Give it a Try !!!

Answer 2

Based on Rolando's answer, I came up with an easier method to do a staged conversion, and then compare. In the tables in question, I first added a new field of the same type. The I mirrored the values, ran the conversion, and compared the resulting values. eg, for Amount float(10,2) NOT NULL I used:

ALTER TABLE `table` ADD `AmountStaged` float(10,2) NOT NULL;
UPDATE `table` SET `AmountStaged` = `Amount`;
ALTER TABLE `table` MODIFY `AmountStaged` decimal(10,2) NOT NULL;
SELECT * FROM `table` WHERE round(`Amount`,2) <> `AmountStaged`;

Amazingly, with several tables (some upwards of 30k rows), there was only one value that did not match. Luckily, the deleted flag was set on that particular record, so it was irrelevant anyway. So upon getting the favorable results, the final step was two commands:

ALTER TABLE `table` DROP `Amount`;
ALTER TABLE `table` CHANGE `AmountStaged` `Amount` decimal(10,2) NOT NULL;

EDIT: The zero downtime alternative would be to drop the AmountStaged column and convert the Amount column, as the conversion should be identical to the one we ran to test.

ALTER TABLE `table` MODIFY `Amount` decimal(10,2) NOT NULL;
ALTER TABLE `table` DROP `AmountStaged`;