Tuesday, October 14, 2014

Work around for solving round off issues when converting existing MySql Column from FLOAT to DOUBLE



Today I have noticed a strange behaviour of mysql database when I tried to convert mysql column type from FLOAT to DOUBLE.

I had a MySql table "employee_project_rates" with a column "basic_hourly_rate" of FLOAT datatype. All the existing records of this table were having values with two decimal places. I wanted to add a new record having a value with more than 10 decimal places. I couldn't do it as the FLOAT datatype won't allow this much decimal places.
So, I had decided to convert the datatype from FLOAT to DOUBLE.

After making this datatype change, I have noticed that basic_hourly_rate value in all the existing records were automatically changed to value with many decimal places. The decimal values beyond the 2 digits are having some random numbers.

I couldn't understand why this kind of change occurs. If you know the reason behind this kind of change, you can share your thoughts through the comments.

Anyway, this change is not acceptable as it had changed the values of existing records.

I came to know that the only way to handle this issue is, we have to solve this issue by adding new column with desired datatype and then adding the values again to the new column.

So, I tried below query after adding a new column basic_hourly_rate_double.

update `employee_project_rates` set basic_hourly_rate_double= basic_hourly_rate

But this approach is not useful, as the values in basic_hourly_rate_double are NOT same as the values in basic_hourly_rate. basic_hourly_rate_double was having many decimal values with random numbers.

So, I used below query to address this issue.

update `employee_project_rates` set basic_hourly_rate_double=ROUND(basic_hourly_rate,2)

After running this query I deleted the basic_hourly_rate column and then renamed basic_hourly_rate_double to basic_hourly_rate.

The approach solved all the issues. Now basic_hourly_rate column of existing records were having unchanged data (i-e values with 2 decimal places) and I can add any new record with values having many decimal places.


You can subscribe to our Email posts, and you can subscribe to our blog feed.

No comments:

Search This Blog