SQL 设置决定如何存储浮点数
祝大家美好的一天。 我喜欢我的测试环境和生产环境之间的奇怪差异。 我有一个将数据存储为浮点数的表。 我在两者上运行以下脚本，得到不同的结果：
insert into myTable(myFloat)
select top 1 26.1295 as foo
Select myFloat from myTable
Server A = 26.1295
Server B = 26.129499435424805
服务器设置之间是否存在导致此问题的差异？ 任何想法都会非常有帮助。
谢谢
Good day to all. I fond an odd difference between my test environment and my production environment. I have a table which stores data as a float. I run the following script on both with different results:
insert into myTable(myFloat)
select top 1 26.1295 as foo
Select myFloat from myTable
Server A = 26.1295
Server B = 26.129499435424805
Is there a difference between the server setup that would cause this? any thoughts would be greatly helpful.
Thanks
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
浮点数是根据 IEEE 754 标准存储的，这就是发生这种情况的原因。
如果您需要精确到该小数点，请使用
小数数据类型（或数字，相同）。 但请注意，它比浮点数慢一点，因为计算机本身可以进行浮点数学运算，但一些额外的开销会用于十进制/数字计算。Floats are stored according to the IEEE 754 standard, which is why this happens.
If you need the precision to that decimal point, use the
decimaldata type (ornumeric, same thing). Do note that it is a bit slower than a float, however, since computers can do floating point math natively, but some extra overhead goes into decimal/numeric calculations.实际的数据类型是什么？如何显示这些值？ 它实际上可能是以不同方式显示的相同值。
如果您指的是 MSSQL 数据类型
float，它对应于 .NET 框架中的double数据类型，它的精度约为 15 位数字。如果您指的是 MSSQL 数据类型
real，它对应于 .NET 框架中的float数据类型，它的精度约为 7 位数字。由于任一数据类型的精度有限，因此您不能指望获得分配给它的准确值。 存储的值是数据类型可以表示的最接近的值，因此通常会在精度结束处四舍五入。 如果将值 26.1295 存储在单精度字段中，则它实际上可能最终为 26.129499435424805，因为这是您可以使用该字段的精度获得的最接近值。
当您取回值时如何查看该值取决于您如何显示它。 通常，将值转换回文本表示形式的代码也会进行舍入，以便它在精度结束之前停止，并且您永远看不到差异。
如果您将值作为单精度数字存储在数据库中，但在从数据库读取时将其转换为双精度数字，则您可能会看到存储的值，而不是在精度边缘舍入的值。 加宽转换会提高精度，但无法添加更多信息。
What is the actual data type, and how are you displaying the values? It might actually be the same value displayed in different ways.
If you mean the MSSQL data type
float, that corresponds to thedoubledata type in the .NET framework, it has a precision of about 15 digits.If you mean the MSSQL data type
real, that corresponds to thefloatdata type in the .NET framework, it has a precision of about 7 digits.As either data type has a limited precision, you can't expect to get the exact value back that you assign to it. The value stored is the closest value that the data type can represent, so it will often be rounded off where the precision ends. If you store the value 26.1295 in a single precision field, it may actually end up as 26.129499435424805 as that is as close that you can get with the precision of the field.
How you see the value when you get it back depends on how you display it. Usually the code that turns the value back into a text representation also has rounding in it so that it stops before the precision ends, and you never see the difference.
If you store the value as a single precision number in the database, but convert it to a double precision number when you read it from the database, you may see the value as it's stored instead of rounded at the edge of the precision. The widening conversion will increase the precision but it can not add any more information.
永远不要考虑使用 float 来进行任何计算。 它不是精确的数据类型，您的计算中将会出现错误。 您需要更改结构以使用数字或十进制数据类型（不要忘记留出一些额外的位置来对值进行除法和乘法）。
Never consider using float for anything that you will do calculations on. It is not an exact datatype and you will have errors in your calculations. You need to change your structure to use a numeric or decimal datatype (don't forget to allow a few extra places for doing division and multiplication on the values).