从浮动(10,2)到固定(10，2)的MySQL转换

Question

我有一个数据库，其中包含以浮动形式存储的金融数据。当然，这可以在随机时间出现一个真实的问题(例如，当一个精确的值不能用浮点算法逼近时)。

我的问题是

• 当您选择字段时，使用ALTER TABLE table MODIFY field fixed(10,2)是否会保留MySQL当前显示的固定值？
• 是否有任何可能的价值会是其他的东西呢？

Answer 1

坏消息:我不认为这个价值会被保留！

我写了这篇文章(2011年7月25日)显示浮点数对转换有多敏感。

好消息:下面是如何安全地比较数据转换：

如果您的表具有以下特征

• 表称为moneytable
• 您希望转换的字段称为moneyfield
• 主键称为moneyid

然后运行以下命令：

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;

下面是底线:如果从LEFT JOIN查询返回任何行，则转换将是错误的。您必须修改moneyfield的定义，重新加载moneytabletest表，并一次又一次地运行LEFT JOIN查询，直到LEFT JOIN查询中没有任何行返回为止。一旦返回零(0)行，您将知道什么转换是安全的。

试试看!！

Answer 2

基于Rolando的回答，我想出了一个更简单的方法来进行分阶段的转换，然后进行比较。在所讨论的表中，我首先添加了一个相同类型的新字段。I镜像值，运行转换，并比较结果值。例如，对于Amount float(10,2) NOT NULL，我使用：

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`;

令人惊讶的是，对于几个表(一些超过30k行)，只有一个值不匹配。幸运的是，已删除的标志被设置在特定的记录上，所以无论如何它都是无关紧要的。因此，在获得有利结果后，最后一步是两个命令：

ALTER TABLE `table` DROP `Amount`;
ALTER TABLE `table` CHANGE `AmountStaged` `Amount` decimal(10,2) NOT NULL;

编辑:零停机选择是删除AmountStaged列并转换Amount列，因为转换应该与我们运行测试的相同。

ALTER TABLE `table` MODIFY `Amount` decimal(10,2) NOT NULL;
ALTER TABLE `table` DROP `AmountStaged`;

Answer 3

感谢上面所有的答案！

我有超过130,972行总数，有几个浮点列与货币一样的数据。所以今天我需要把这些列转换成十进制，因为我们在精度上有一些问题。我使用了上述解决方案之一，但结果并不完全是我所需要的，正如作者所言：

您必须修改moneyfield的定义，重新加载moneytabletest表，并一次又一次地运行“左联接”查询，直到左联接查询中没有任何行返回为止。一旦返回零(0)行，您将知道什么转换是安全的。

另一个是幸运的，差异记录被标记为删除.就我而言，在这一行动之后：

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`;

..。我排了87排，这不匹配。所以我决定把它更新如下：

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

在那之后我得到了我所需要的一切。从float_price到dummy_price的所有四舍五入的数据都是eq，我认为这就足够了。

我想我会帮助别人的。