CODEWITHSUNDEEP
mysql
Phuong Le
Phuong Le

Reputation: 487

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

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

Upvotes: 7

Views: 6799

Answers (2)

O. Jones
O. Jones

Reputation: 108676

Another way to to this, which will work if your giga-row table has ID numbers or some other workable primary key. This will allow you to upgrade your precision without much downtime.

Create a new table -- call it precision_value -- with two columns:

id       BIGINT NOT NULL pk
dmetric  DOUBLE (or DECIMAL(10,3))

Change your queries that need higher precision to something like this.

SELECT a.id, ..., 
       COALESCE(b.dmetric, a.metric) dmetric,
       ...
  FROM your_big_table a
  LEFT JOIN `precision_value` b ON a.id = b.id

This use of LEFT JOIN and COALESCE will look for each value first in your new table, then in your existing table. If the higher-precision value is present, you'll get it back from the new table, otherwise you'll get the original value. It might be convenient to create a view for this.

Then, insert your newly arriving high precision data values, new observations, etc, into your new table as well as your existing one

 INSERT INTO your_big_table (metric, whatever) VALUES ('1.234567890', 'whatever');
 INSERT INTO precision_values (id, dmetric) VALUES (LAST_INSERT_ID(), '1.234567890')

This new table is basically a lookaside table for higher-precision data.

Finally, round your output appropriately to your application. For example, if you want three decimal places, this will do it.

SELECT a.id, ..., 
       ROUND(COALESCE(b.dmetric, a.metric),3) dmetric,

Upvotes: 1

O. Jones
O. Jones

Reputation: 108676

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.

Upvotes: 10

Related Questions