将SQL列从浮点型更改为十进制类型。

Question

CREATE TABLE [dbo].[TES_Tracks](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Rate] [float] NULL,
[TOL] [datetime] NOT NULL
)

我希望将汇率列更改为decimal(28,6)。该表中已经有了很多以浮点格式存储的数据，我担心会有数据丢失。我该如何操作？

- CoderKK

---

ALTER TABLE [dbo].[TES_Tracks] ALTER COLUMN [Rate] DECIMAL(28,6) - Lamak

你可以先查询是否有浮点值行，使用 SELECT * FROM [dbo].[TES_Tracks] WHERE [Rate] < 0.000001; 然后，你可以决定如何处理这些数据。 - Joseph B

你的数值是否有小数点后超过6位的数字？ - Lamak

@JosephB 是的，我有那些。四舍五入也可以。但是我该怎么做呢？ - CoderKK

@CoderKK 你可以使用CAST函数来进行更新。请参见我下面的答案。 - Joseph B

Answer 1

未经测试，但这也许值得一试...

ALTER TABLE [dbo].[TES_Tracks] ADD [RateNew] DECIMAL(28,6);

UPDATE [dbo].[TES_Tracks] set RateNew = Cast(Rate as Decimal(28,6));

由于SQL Server可以隐式地处理十进制数和浮点数，因此如果您存在数据损失，这将为您获取行。

Select * From [dbo].[TES_Tracks] where Rate <> RateNew;

那么，如果您对我们的服务感到满意......

ALTER TABLE [dbo].[TES_Tracks] DROP COLUMN [Rate];

然后将RateNew列重命名为Rate

EXEC sp_RENAME 'TES_Tracks.RateNew' , 'Rate', 'COLUMN'

- bsivel

---

谢谢你的回答。但是删除列速率不是一个选项，因为它已经在许多其他地方使用了。因此，在我的情况下，你的方法有点容易出错。 - CoderKK

在这种情况下，为什么不使用新的（临时）列来重新填充Rate，然后将其更改为十进制。换句话说，按照bsivel的建议添加RateNew和UPDATE。然后按照Lamak的建议更改表的数据类型。然后，如果列更改导致任何您不喜欢的舍入问题，请比较Rate和RateNew并从RateNew更新。最后，删除RateNew。 - FumblesWithCode

Answer 2

你可以简单地更新汇率数据，然后更改列数据类型。

首先，您可以使用以下查询验证CAST（仅针对小数部分小于0.000001的行）。

SELECT 
  [Rate],
  CAST([Rate] as decimal(28, 6)) Rate_decimal
FROM [dbo].[TES_Tracks]
WHERE [Rate] - FLOOR([Rate]) < 0.000001;

一旦您确认 CAST 表达式正确无误，就可以使用 UPDATE 语句应用它。同样地，您只能更新那些具有 [Rate] - FLOOR([Rate]) 的行，从而获得良好的性能。

UPDATE [dbo].[TES_Tracks]
SET [Rate] = CAST([Rate] as decimal(28, 6))
WHERE [Rate] - FLOOR([Rate]) < 0.000001;

ALTER TABLE [dbo].[TES_Tracks] ALTER COLUMN [Rate] DECIMAL(28,6);

这样，您就不需要删除“Rate”列。

SQL Fiddle演示

- Joseph B

---

谢谢！那会有所帮助。 - CoderKK

@JoesephB 可能是一个愚蠢的问题，但我不明白为什么你在 where 子句中使用了 [Rate] - FLOOR([Rate])。 - CoderKK

@CoderKK 不用担心。使用[Rate] - FLOOR([Rate])来获取Rate的小数部分（例如，如果Rate=15.000001，则[Rate] - FLOOR([Rate]) = 0.000001）。 - Joseph B

我只是尝试了 ALTER TABLE [dbo].[TES_Tracks] ALTER COLUMN [Rate] DECIMAL(28,6) 并且它起作用了，所以我的问题是这种简单方法和更冗长的方法之间有什么区别？ - Gary Bao 鲍昱彤

我认为这个逻辑是不正确的 - 在你的update语句中的cast语句将会舍去你的浮点数非常小的小数部分，但当系统试图将这个截断的十进制值持久化回你的列时，它将会隐式地再次转换为浮点数 - 没有保证你生成的十进制数可以映射到一个浮点数中而不引入另一个小的小数部分。换句话说，update步骤不仅是无用的，而且可能实际上更新数据值为新的值。 - youcantryreachingme

此外，您的 where 子句应该查看差异的绝对值。有可能您的浮点数的小数部分恰好低于一个精确的十进制值。您的 select 背后的概念是正确的 - 但我怀疑您在查看仅有6位小数时实际上不会看到任何值的差异。相反，看一下新的十进制值和原始浮点数之间的差异可能更有意义。最终，alter 语句是要采取的方法，但这里的说明并没有真正解决如何确信哪些数据会丢失的问题。 - youcantryreachingme