我怎样才能“修剪”？ C# 双倍于将存储在 sqlite 数据库中的值？
我注意到，当我在 sqlite 数据库中存储双精度值（例如 x = 0.56657011973046234），然后稍后检索它时，我得到 y = 0.56657011973046201。根据 sqlite 规范 和 .NET 规范（我最初都懒得去读：）这是预期的且正常的。
我的问题是，虽然高精度并不重要，但我的应用程序处理用户输入/选择代表基本 3D 信息的双精度数，然后对它们运行模拟以找到结果。此输入可以保存到 sqlite 数据库中，以便稍后重新加载和重新运行。
之所以会出现这种混乱，是因为一旦存储和重新加载，新创建的一系列输入显然会以与相同输入略有不同的方式进行模拟（因为双精度值已更改）。这是合乎逻辑的，但并不可取。
我还没有完全弄清楚如何处理这个问题，但与此同时，我想将用户输入限制/限制为可以精确存储在 sqlite 数据库中的值。因此，如果用户输入 0.56657011973046234，它实际上会转换为 0.56657011973046201。
然而，我无法弄清楚，给定一个数字，数据库中将存储什么值，缺乏实际存储和从数据库中检索它，这看起来很笨拙。有没有既定的方法来做到这一点？
I noticed that when I store a double value such as e.g. x = 0.56657011973046234 in an sqlite database, and then retrieve it later, I get y = 0.56657011973046201. According to the sqlite spec and the .NET spec (neither of which I originally bothered to read :) this is expected and normal.
My problem is that while high precision is not important, my app deals with users inputting/selecting doubles that represent basic 3D info, and then running simulations on them to find a result. And this input can be saved to an sqlite database to be reloaded and re-run later.
The confusion occurs because a freshly created series of inputs will obviously simulate in slightly different way to those same inputs once stored and reloaded (as the double values have changed). This is logical, but not desireable.
I haven't quite come to terms of how to deal with this, but in the meantime I'd like to limit/clamp the user inputs to values which can be exactly stored in an sqlite database. So if a user inputs 0.56657011973046234, it is actually transformed into 0.56657011973046201.
However I haven't been able to figure out, given a number, what value would be stored in the database, short of actually storing and retrieving it from the database, which seems clunky. Is there an established way of doing this?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（6）
答案可能是将双精度值存储为 17 位有效数字字符串。看看 SQLite 如何处理实数与文本之间的差异（为了简单起见，我将使用命令行界面进行说明）：
以真实亲和力存储它是问题的原因 - SQLite 只返回 15 位的近似值。如果您将其存储为文本，则可以使用 C# 程序检索原始字符串并将其转换回原始双精度值。
The answer may be to store the double values as 17 significant digit strings. Look at the difference between how SQLite handles real numbers vs. text (I'll illustrate with the command line interface, for simplicity):
Storing it with real affinity is the cause of your problem -- SQLite only gives you back a 15 digit approximation. If instead you store it as text, you can retrieve the original string with your C# program and convert it back to the original double.
Double round 有一个带有指定位数的参数的实现。使用此方法将舍入为 14 位（例如）： rval = Math.Round(Val, 14)
然后在从数据库接收值时以及在模拟开始时舍入，即。那么值匹配吗？
有关详细信息：
http://msdn.microsoft.com/en-us/library/ 75ks3aby.aspx
如果您不比较数据库中的值，只是存储它们，那么另一个想法是：为什么不简单地将它们存储为二进制数据？那么所有的位都会被逐字存储和恢复？
Double round has an implementation with a parameter that specifies the number of digits. Use this to round to 14 digits (say) with: rval = Math.Round(Val, 14)
Then round when receiving the value from the database, and at the beginning of simulations, ie. So at the values match?
For details:
http://msdn.microsoft.com/en-us/library/75ks3aby.aspx
Another thought if you are not comparing values in the database, just storing them : Why not simply store them as binary data? Then all the bits would be stored and recovered verbatim?
假设 SQL Lite 和 .NET 都正确实现了 IEEE 规范，如果在两侧使用相同的浮点类型，您应该能够获得相同的数字结果（因为从数据库传递时该值不应更改）到 C#，反之亦然）。
目前，您在 SQL Lite 中使用 8 字节 IEEE 浮点（单）(*)，在 C# 中使用 16 字节浮点（双）。 C#中的
float类型对应于8字节IEEE标准，因此使用该类型代替double可以解决问题。(*) SQL Lite 文档说 REAL 是一个浮点值，存储为 8 字节 IEEE 浮点数。
Assuming that both SQL Lite and .NET correctly implement the IEEE specification, you should be able to get the same numeric results if you used the same floating point type on both of the sides (because the value shouldn't be altered when passed from database to C# and vice versa).
Currently you're using 8-byte IEEE floating point (single) (*) in SQL Lite and 16-byte floating-point in C# (double). The
floattype in C# corresponds to the 8-byte IEEE standard, so using this type instead ofdoublecould solve the problem.(*) The SQL Lite documentation says that REAL is a floating point value, stored as an 8-byte IEEE floating point number.
您可以使用字符串将 # 存储在数据库中。就我个人而言，我已经完成了 winwaed 建议的在存储之前和从数据库获取之后进行舍入的操作（使用 numeric()）。
我记得被银行家四舍五入烧伤了，但这可能只是不符合规范。
You can use a string to store the # in the db. Personally I've done what winwaed suggested of rounding before storing and after fetching from the db (which used numeric()).
I recall being burned by bankers rounding but it could just be that didn't meet spec.
您可以将双精度值存储为字符串，并在将双精度值转换为字符串时使用往返格式，保证在解析时生成相同的值：
You can store the double as a string, and by using the round-trip formatting when converting the double to a string, it's guaranteed to generate the same value when parsed:
如果您希望十进制输入值能够往返，则必须将它们限制为 15 位有效数字。如果您希望 SQLite 内部双精度浮点值进行往返，那么您可能会运气不佳；这需要打印至少 17 位有效数字，但据我所知，SQLite 最多打印 15 位（编辑：
也许 SQLite 专家可以确认这一点？我刚刚阅读了源代码并跟踪了它 - 我是正确的，精度限制为 15 位。）我在 Windows 上的 SQLite 命令界面中测试了您的示例。我插入了0.56657011973046234，然后选择返回0.566570119730462。在 C 中，当我将 0.566570119730462 分配给 double 并将其打印为 17 位数字时，我得到 0.56657011973046201；这与您从 C# 获得的值相同。 0.56657011973046234 和 0.56657011973046201 映射到不同的浮点数，因此换句话说，SQLite double 不会往返。
If you want the decimal input values to round-trip, then you'll have to limit them to 15 significant digits. If you want the SQLite internal double-precision floating-point values to round-trip, then you might be out of luck; that requires printing to a minimum of 17 significant digits, but from what I can tell, SQLite prints them to a maximum of 15 (EDIT:
maybe an SQLite expert can confirm this?I just read the source code and traced it -- I was correct, the precision is limited to 15 digits.)I tested your example in the SQLite command interface on Windows. I inserted 0.56657011973046234, and select returned 0.566570119730462. In C, when I assigned 0.566570119730462 to a double and printed it to 17 digits, I got 0.56657011973046201; that's the same value you get from C#. 0.56657011973046234 and 0.56657011973046201 map to different floating-point numbers, so in other words, the SQLite double does not round-trip.