SQLite的参数化查询的奇怪行为 [英] Strange behaviour of parameterized SQLite query

问题描述

的（随时改进，如果你能想到的东西更好的问题的标题。）的

问：考虑以下SQLite的查询：

  SELECT COUNT（*）
  FROM（SELECT 1 AS值UNION SELECT 2 AS值）
 LT其中value＆=？
 

当我使用 1 作为参数，我的期望的查询产生 1 ，但它产生 2 。为什么会这样？

---

其他信息

这是一个最小的工作示例重现该问题（Android版）：

 光标C = db.rawQuery（
        SELECT COUNT（*）+
        FROM（SELECT 1 AS值UNION SELECT 2 AS值）+
        其中value＆LT; =，
        新的String [] {将String.valueOf（1）}）;c.moveToFirst（）;
Log.i（，结果：+将String.valueOf（c.getInt（0）））;
 

它的可能的有该参数是作为字符串传递，但是，唉，有没有其他的方式来传递参数的SQLite API实际上做的，所以我想我没有做任何错误在这里，它的的SQLite的工作的价值相应地转换。我还观察到使用查询的非参数化的版本（这可能是也可能不是相关的问题）时，以下内容：

  SELECT COUNT（*）
  FROM（SELECT 1 AS值UNION SELECT 2 AS值）
 其中value＆LT; = 1  - 收益1SELECT COUNT（*）
  FROM（SELECT 1 AS值UNION SELECT 2 AS值）
 其中value＆LT; =1 - 产量2
 

解决方案

这是 rawQuery 文档中说：

  

[...]你可以包含？s在查询中，将由来自selectionArgs两个值来代替条款。 的值将被绑定为字符串。

和，引述 SQLite的文档：

  

一个比较的结果取决于的存储类
  操作数，根据以下规则[...]

  
  

  
• 整数或REAL值小于任何文字或BLOB值。

  

由于两个1和2的是的整数，他们都小于'1'（文本值）。这就是为什么这个语句：

  SELECT 2'='1'
 

... SQLite中返回1。

您也许应该使用...

 其中value＆LT; = CAST（'1'AS INTEGER）
 

...来代替。或者你可以使用一个事实，即所有的数学运算投两个操作数为数字存储类与其中value＆LT;？= + ，但这是不干净，IMO

请注意，在这个查询：

  SELECT * FROM myTable的WHERE _id＆LT; ？
 

的价值会得到其亲和力调整为 _id 的亲和力的列的 - 因此他们将被作为比较的数字，如果 _id 是数字。

(Feel free to improve the question title if you can think of something better.)

Question: Consider the following SQLite query:

SELECT COUNT(*)
  FROM (SELECT 1 AS value UNION SELECT 2 AS value)
 WHERE value <= ?

When I use 1 as the parameter, I expect the query to yield 1, yet it yields 2. Why does this happen?

---

Additional information:

This is a minimal working example to reproduce the issue (Android):

Cursor c = db.rawQuery(
        "SELECT COUNT(*) " +
        "  FROM (SELECT 1 AS value UNION SELECT 2 AS value) " +
        " WHERE value <= ? ",
        new String[] {String.valueOf(1)});

c.moveToFirst();
Log.i("", "Result: " + String.valueOf(c.getInt(0)));

It might have to do with the fact that the parameter is passed as a string, but, alas, there is no other way to pass parameters to the SQLite API, so I guess I'm not doing anything "wrong" here and it's SQLite's job to convert the value appropriately. I've also observed the following when using a non-parameterized version of the query (this might or might not be relevant for the issue):

SELECT COUNT(*)
  FROM (SELECT 1 AS value UNION SELECT 2 AS value)
 WHERE value <= 1              -- yields 1

SELECT COUNT(*)
  FROM (SELECT 1 AS value UNION SELECT 2 AS value)
 WHERE value <= '1'            -- yields 2

解决方案

It's said in the rawQuery documentation:

[...] You may include ?s in where clause in the query, which will be replaced by the values from selectionArgs. The values will be bound as Strings.

And, quoting the SQLite doc:

The results of a comparison depend on the storage classes of the operands, according to the following rules [...]

• An INTEGER or REAL value is less than any TEXT or BLOB value.

As both 1 and 2 are integers, they're both less than '1' (TEXT value). That's why this statement:

SELECT 2 <= '1'

... returns 1 in SQLite.

You should probably use ...

WHERE value <= CAST('1' AS INTEGER)

... instead. Or you can use the fact that all mathematical operators cast both operands to the NUMERIC storage class with WHERE value <= + ?, but this is less clean, imo.

Note that in this query:

SELECT * FROM myTable WHERE _id < ?

... the value of ? will get its affinity adjusted to the affinity of _id column - hence they will be compared as numbers, if _id is NUMERIC.

这篇关于SQLite的参数化查询的奇怪行为的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！