如何在 SQL Server 2005 中对 INSERT 和 UPDATE 上定义为浮点的列进行舍入
我正在开发一个使用 float 数据类型来存储只能是小数点后两位（美元和美分）的值的数据库。
只要更新浮动列的人进行一轮操作，使用浮动似乎就可以正常工作。 当然，这种情况并不总是发生，当完成求和时，它总是与显示的内容相差几美分，因为显示格式设置为仅显示两位小数。 该数据库有数百个使用浮点的表，如果能够自动舍入浮点列将会很有帮助。
这可以做到吗？
能否在 INSERT 和 UPDATE 上使用 TRIGGER 对列进行 ROUND？
如果可以的话，你能展示一下如何编写触发器吗？你会推荐它吗？
还有其他想法吗？
我们使用 SQL Server 2005。
这是一篇提出问题的帖子，使用浮动或会计应用程序美元金额的小数？，我喜欢这样的回答
“您确实应该考虑使用某种类型的定点/任意精度数字包（例如，Java BigNum、python 小数模块），否则您将陷入受伤的世界”。
I am working on a database that uses the float data type to store values that should only be two decimal positions (dollars and cents).
Using float appears to work OK, as long as the person updating the float column does a ROUND. Of course, this does not always happen and then when a SUM is done it is always off a few pennies from what is displayed because the display is formatted to show only two decimal positions. This database has hundreds of tables using float and it would be helpful to be able to automatically ROUND the float columns.
Can this be done?
Can a TRIGGER be used on INSERT and UPDATE to do a ROUND on the Column?
If it can, could you show how you would code the TRIGGER and would you recommend it?
Any other ideas?
We are using SQL Server 2005.
Here is a post that asks the question, Use Float or Decimal for Accounting Application Dollar Amount?, and I like the one response that said
"You should really consider using some type of fixed point / arbitrary-precision number package (e.g., Java BigNum, python decimal module) otherwise you'll be in for a world of hurt".
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
如果您可以控制系统，您是否可以使用 DECIMAL /MONEY/NUMERIC 数据类型而不是 FLOAT？
毕竟，这就是您提到的问题的要点。
如果没有，并且像我们许多人一样，您被迫生活在那个痛苦的世界中，那么您的触发因素建议就会起作用。 然而，我更愿意以不同的方式控制它......
- 不允许应用程序或用户直接写入表
- 通过 SP 强制访问
我还见过一个系统，其中对一个（输入/保存）表进行写访问。 然后，该表上有一个触发器，用于将数据复制（并格式化/验证）到真实表中。
If you have control over the system, are you able to used the DECIMAL /MONEY/NUMERIC data type instead of a FLOAT?
It is, after all, the point of the question you referenced.
If not, and like many of us you are forced to live in that world of pain, your triggers suggestion would work. I would prefer, however, to control that in a different way...
- Don't allow apps or users direct write access to the tables
- Force access through SPs
I have also seen a system where write access is given to one (input/holding) table. This table then has a trigger on it to copy (and format/validate) the data into the real table.
你可以做触发器，这根本不是对它们的不合理使用。 预插入和预更新触发器应该可以正常工作。
此外，您可以进行一次性修复，例如：
语法可能并不完美，但您应该明白这一点。
但我不确定我是否理解你的“少了几分钱”的话。 您必须对相当大量的浮点数进行求和才能使误差累积到 0.01。 您使用的列定义是什么？
当然，货币类型小数列定义之一对于完美的准确性会更好，但您可能会失去 DBMS 之间的可移植性。
You can do triggers, and this is not an unreasonable use of them at all. A pre-insert and pre-update trigger should work fine.
In addition, you can do a one-shot fix, something like:
The syntax may not be perfect, but you should get the idea.
But I'm not sure I understand your "off by a few pennies" remark. You would have to sum a rather large number of floats for the errors to accumulate to 0.01. What is the column definition you're using?
Of course, one of the money-type decimal column definitions would be better for perfect accuracy, but you may lose portability between DBMS'.
我认为你不能通过四舍五入得到你想要的东西。 对浮点数进行四舍五入无法准确可靠地工作：无论您做什么，您都将尝试对二进制数应用十进制四舍五入，而除了 2 的幂之外，这对于任何其他数字都不会精确
。只能通过使用专门为此目的设计的十进制数据类型来获得任意子整数数量的精确数据库存储的十进制表示。 这些数据类型通常使用某种形式的打包十进制存储（因此效率低于二进制），例如 2009 存储为 0x2009。
如果您使用 CONVERT 函数会发生什么情况？ 您是否尝试过使用类似的方法进行查询？
从长远来看，我认为我会考虑在表中添加一列具有所需类型的列，并通过插入/更新后触发器维护其值，然后随着时间的推移切换所有代码以使用该列。
然后我会追捕并惩罚那些认为将金额存储在浮动货币中是个好主意的人......
I don't think you can get exactly what you want by rounding. Rounding a float won't work accurately and dependably: whatever you do, you're going to be trying to apply a decimal rounding to a binary number and that just isn't going to be exact for anything but a power of 2.
You can only get accurate database-stored decimal representations of arbitrary sub-integer quantities by using a decimal datatype that's expressly designed for the purpose. These datatypes typically utilise some form of packed-decimal storage (so less efficient than binary) where, for example 2009 is stored as
0x2009.What happens if you use the CONVERT function? Have you tried querying using something like this?
Long-term, I think I'd consider adding a column to my table that has the desired type and maintaining its value through post-insert/update triggers, then over time switch all my code to use that column instead.
Then I'd hunt down and punish the individuals who thought it was a good idea to store money amounts in a float...