SQLite查询where子句与浮点数字失败？ [英] SQLite query where clause with floating point numbers fails?

问题描述

  private static final String DATABASE_CREATE = 
创建表+ DATABASE_TABLE +（
 + KEY_ID +整数主键自动增量，
 + KEY_FLOAT +REAL，+ ... 
 ... 
 
 content.put（KEY_FLOAT，37.3f）; 
 db.insert（DATABASE_TABLE，null，content）; 
  

查询表时：

  Cursor cursor = db.query（false， DATABASE_TABLE，
 new String [] {KEY_ID，KEY_FLOAT，...}，
 KEY_LATITUDE += 37.3，
 null，null，null，null，null）; 
  

如果我将float的值更改为37.0，它会正常工作，返回游标。

我已经测试了一段时间，将数据库列规范从REAL更改为float等。只要我有任何分数小数点后的部分，游标返回空。怎么回事？

预先感谢！

解决方案

37.0在二进制浮点中是完全可表示的，所以你没有任何正常的问题。 37.3是 not 完全可以表示 - 你的查询是不匹配的，因为数据库中的值不是37.3。 p>

• 使用基于小数的数字类型，如果SQLite中存在这种情况（我会在一分钟内查看）


• 让您的查询使用宽容，例如纬度> 37.29，纬度<37.31。
  

编辑：我刚刚检查了<一个href =http://www.sqlite.org/datatype3.html =noreferrer>文档，它看起来像SQLite不支持十进制非整数类型。我会留下上面的建议，因为这将是相关的问题，但在其他数据库相同。

编辑：帕斯卡的解决方案，有效地滚动自己的数字类型是一个很好的一个在很多情况下。计算出你想要的精度水平，并相应地进行乘除运算...所以如果你想精确到2个小数位，在存储一个值时乘以100，然后在取出它之后除以100。当然，您的查询需要采用相乘的形式。

I'm putting a float in an Android based SQLite database, like so:

private static final String DATABASE_CREATE = 
    "create table " + DATABASE_TABLE + " ("  
                    + KEY_ID + " integer primary key autoincrement, "
                    + KEY_FLOAT  + " REAL, " + ...
...

content.put(KEY_FLOAT, 37.3f);
db.insert(DATABASE_TABLE, null, content);

When I query the table:

Cursor cursor = db.query(false, DATABASE_TABLE, 
      new String[] { KEY_ID, KEY_FLOAT, ... },
      KEY_LATITUDE + "=37.3",
      null, null, null, null, null);

the cursor comes back empty. If I change the value of the float to 37.0 it works properly, returning the record in the cursor.

I've tested this at some length, changing the database column spec from "REAL" to "float", etc. AS long as I have any fractional portion after the decimal point, the cursor returns empty. What is going on?

Thanks in advance!

解决方案

37.0 is exactly representable in binary floating point, so you don't have any of the normal issues. 37.3 is not exactly representable - your query isn't matching because the value in the database isn't exactly 37.3.

Options:

• Use a decimal-based numeric type, if such a thing exists in SQLite (I'll check in a minute)
• Make your query use a tolerance, e.g. "LATITUDE > 37.29 AND LATITUDE < 37.31". Comparing binary floating point values for exact equality is just asking for trouble, I'm afraid.

EDIT: I've just checked the docs, and it looks like SQLite doesn't support decimal non-integer types. I'll leave that suggestion above as it would be relevant for the same issue in other databases though.

EDIT: Pascal's solution of effectively rolling your own numeric type is a good one in many cases. Work out what level of precision you want, and multiply/divide accordingly... so if you want 2 decimal places of precision, multiply by 100 when you store a value and then divide by 100 after fetching it. Your queries will need to be in the "multiplied" form, of course.

这篇关于SQLite查询where子句与浮点数字失败？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！