我需要改变数据类型从浮点到小数的数据库目前有10亿的记录。

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

我需要将数据类型从浮点数改为十进制，目前有10亿条记录。是否有不可预见的问题?谢谢

2 个解决方案

#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.

如果您必须继续这样做，您应该尝试向表添加一个新的列，而不是修改现有的列。假设您的旧列被称为metric，而新列被称为dmetric。进一步假设度量列定义为非空。

Then create the new column so it does allow NULLs.

然后创建新的列，以便它允许null。

Then put an index on the new column.

然后在新列上放置一个索引。

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

然后运行这个更新查询大约50万次，直到它不处理任何行。

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.

这将以合理大小的块进行转换，并防止事务(如果您在innodb世界中)变得过大。

Do try this on a copy of the table.

请在表格的副本上试试这个。

#2

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.

另一种实现此目的的方法是，如果giga-row表具有ID号或其他可使用的主键，那么该方法将有效。这将允许您在不停机的情况下升级您的精度。

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

创建一个新表——称为precision_value——包含两列:

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.

这个新表基本上是一个用于更高精度数据的lookaside表。

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,

#1