Ruby BigDecimal 和 MySQL 小数精度不匹配
我使用 Rails 3.0.3 在 MySQL 数据库中存储一些纬度和经度。这是我用来创建表的迁移的一部分（注意给定精度的十进制值）：
create_table :dummies do |t|
t.decimal :something, :precision => 13, :scale => 10
end
以下 RSpec 示例应该说明哪里出了问题。
我使用 BigDecimal 进行一些计算，结果 my_value 是一个具有较大精度的数字（大于迁移中指定的精度）。我将对象存储在数据库中并再次检索它。
将原始值与数据库值进行比较失败，因为它们的精度不同，因此不再是相同的数字：
it 'should be equals before and after save' do
my_value = BigDecimal('4.123456789') * 5000 # more precise than defined in the migration
dummy = Dummy.new(:something => my_value)
location.save!
Dummy.first.something.should == dummy.something
end
我明白为什么会发生这种情况（MySQL 中小数的精度！= BigDecimal），但谁能告诉我在将 my_value BigDecimal 写入数据库之前，如何限制它的精度以确保它遵守数据库约束？
谢谢！
I'm storing some latitudes and longitudes in a MySQL database using Rails 3.0.3. Here is a part of the migration I used to create the table (note the decimal value with a given precision):
create_table :dummies do |t|
t.decimal :something, :precision => 13, :scale => 10
end
The following RSpec example should illustrate where it's going wrong.
I use a BigDecimal for some calculations and the resulting my_value is a number with a large precision (larger than the one specified in the migration). I store the object in the database and retrieve it again.
Comparing the original value with the database value fails as their precision is not the same so it is no longer the same number:
it 'should be equals before and after save' do
my_value = BigDecimal('4.123456789') * 5000 # more precise than defined in the migration
dummy = Dummy.new(:something => my_value)
location.save!
Dummy.first.something.should == dummy.something
end
I understand why this is happening (the precision of the decimal in MySQL != the BigDecimal one) but can anyone tell me how I can limit the precision of the my_value BigDecimal before I write it to the database to make sure it respects the database constraints?
Thanks!
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
在 mySQL 中，使用 FLOAT 来存储这些地理坐标。将它们存储为高精度十进制根本没有意义。请记住，1/60 度的纬度是一海里，因此一英尺（约 1/3 米）约为 3 微度。 (3e-06)
IEEE 单精度浮点的 epsilon（误差）约为 6e-08
GPS，并且地理编码不是那么详细，即使您处于 179.9996 度也是如此。
如果您正在制作非常详细的 200 比例地形图或类似的东西，您可能已经知道必须使用高精度投影，例如通用横轴墨卡托投影或兰伯特投影或类似投影，因为您超出了近似投影的限制。地球作为一个球体。但是，如果您正在开发商店查找类型的应用程序，则您不需要、不想要也无法获得这种精度。
如果您必须在 Ruby 程序中使用十进制，请在存储到 mySQL 之前转换为浮点型。
以下是 Randall Munroe 对地理坐标精度的解释。
In mySQL, use FLOAT for storing these geo-coordinates. It simply makes no sense to store them as high-precision decimal. Keep in mind that 1/60th of a degree of latitude is a nautical mile, so a foot (~ 1/3 of a meter) is around 3 microdegrees. (3e-06)
The epsilon for IEEE single precision floating point (the error) is about 6e-08
GPS and geocoding aren't that detailed, even when you're at 179.9996 degrees.
If you're making highly detailed 200-scale topo maps or something like that, you probably already know you have to use a high-precision projection like universal transverse Mercator or Lambert or some such, because you're beyond the limit of approximating the earth as a sphere. But, if you're doing a store-finder type of app, you don't need, don't want, and can't get, that kind of precision.
If you MUST use decimal in your Ruby program, convert to float before storing to mySQL.
Here's Randall Munroe's explanation of geocoordinate precision.
我会尝试类似的东西
I'd try something like