I need to change data type from float to decimal of a database currently got 1 billion records

Question

I need to change data type from float to decimal of a database currently got 1 billion records. Is there any unforeseen issues? Thanks

Answer 1

This is going to take a long time and take a lot of careful planning. You should plan for multiple days if you do it very carefully. If you just do an ALTER TABLE it might run for a week and then crash. I'm not kidding.

Can you avoid doing this? Can you create a view that shows the float column as decimal? You would be smart to try to avoid it.

If you must go forward with this, you should try adding a new column to the table rather than altering the existing one. Suppose your old column is called metric and the new one is called dmetric. Further suppose that your metric column is defined NOT NULL.

Then create the new column so it does allow NULLs.

Then put an index on the new column.

Then run this UPDATE query a half million times or so, until it processes no rows.

UPDATE table SET dmetric = metric
 WHERE dmetric IS NULL
 LIMIT 2000

That will do the conversion in reasonably sized chunks, and keep transactions (if you're in the innodb world) from getting too large.

Do try this on a copy of the table.