Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?
share|improve this question
    
Yes. There is a possibility that the value would be different than that. But you "have to" change it to decimal to avoid issues in the future. –  shantanuo Dec 15 '11 at 14:18

3 Answers 3

up vote 5 down vote accepted

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

share|improve this answer
    
Thanks Rolando; I ended up using a similar but simpler method to do a convert and compare. I will add it as a separate answer. –  Bryan Agee Dec 15 '11 at 15:45
    
@BryanAgee : Looking forward to seeing your answer !!! –  RolandoMySQLDBA Dec 15 '11 at 15:59

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`;
share|improve this answer
    
I'm not sure if this has performance implications on a live system vs. Rolando's method, but it was plenty fast under our load –  Bryan Agee Dec 15 '11 at 16:20
1  
I like this answer because there is less overall clutter. One table to play with in exchange for adding and dropping a column. In terms of purpose, still apples to apples. +1 !!! –  RolandoMySQLDBA Dec 15 '11 at 17:05

Thanks to all answers above!

I have over 130, 972 rows total, there are several float columns with money-like data in. So today i need to convert those colums to decimal, because we have some troubles with precision. I used one of solutions above, but the result wasn't exactly that i need, as author said:

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.

Another one was lucky and the difference records was marked as deleted... In my case after this operations:

ALTER TABLE `target_table` ADD `dummy_price` decimal(20,2) NOT NULL;
UPDATE `target_table` SET `dummy_price` = CAST(`float_price` as DECIMAL(20,2));
SELECT `float_price`, `dummy_price`  FROM `target_table` WHERE round(`float_price`, 2) <> `dummy_price`;

... i got 87 rows, that didn't match. So i decided just update it like this:

UPDATE `target_table` 
SET `dummy_price` = round(`float_price`, 2) 
WHERE round( `float_price` , 2 ) <> `dummy_price`

Well, after that i got all i need. All rounded data from float_price is eq to dummy_price, i think its enough.

I think i'll help someone.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.