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

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?

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 !!!

StackExchange Database Administrators Q#9187, answer score: 8