为什么SQLite在使用“IS”而不是“=”时不使用索引？

This query will not use the index: SELECT * FROM baz WHERE id IS '123'

此查询将不使用索引:SELECT * FROM baz WHERE id IS'123'

This query will use the index: SELECT * FROM baz WHERE id='123'

此查询将使用索引:SELECT * FROM baz WHERE id ='123'

Why??

EDIT: I realize there is the stupid answer of expanding the IS to get the correct effect (i.e. making the query SELECT * FROM baz WHERE id='123' OR '123' IS NULL, but I am still curious why this is the case.

编辑:我意识到有一个愚蠢的答案扩展IS以获得正确的效果(即使查询SELECT * FROM baz WHERE id ='123'或'123'为空,但我仍然很好奇为什么会这样。

1 个解决方案

#1

There's no need to support indexing for such queries.

没有必要支持此类查询的索引。

First, that's just how the query optimizer works:

首先,这就是查询优化器的工作原理:

To be usable by an index a term must be of one of the following forms:

  column = expression
  column > expression
  column >= expression
  column < expression
  column <= expression
  expression = column
  expression > column
  expression >= column
  expression < column
  expression <= column
  column IN (expression-list)
  column IN (subquery)
  column IS NULL

For the IS operator, NULL values are special:

对于IS运算符,NULL值是特殊的:

The IS and IS NOT operators work like = and != except when one or both of the operands are NULL. In this case, if both operands are NULL, then the IS operator evaluates to 1 (true) and the IS NOT operator evaluates to 0 (false). If one operand is NULL and the other is not, then the IS operator evaluates to 0 (false) and the IS NOT operator is 1 (true).

除非一个或两个操作数都为NULL,否则IS和IS NOT运算符的工作方式类似于=和!=。在这种情况下,如果两个操作数均为NULL,则IS运算符求值为1(true),IS NOT运算符求值为0(false)。如果一个操作数为NULL而另一个操作数不为NULL,则IS运算符的计算结果为0(false),IS NOT运算符为1(true)。

In your question,

在你的问题中,

SELECT * FROM baz WHERE id IS '123'

and

SELECT * FROM baz WHERE id='123' OR id IS NULL

are not the same thing. (The latter can be satisfied using two index lookups.) It should be something like

不是一回事。 (后者可以使用两个索引查找来满足。)它应该是类似的

SELECT * FROM baz WHERE id='123' OR (id IS NULL AND '123' IS NULL)

where the parenthesized expression is always false and therefore useless.

括号表达式始终为false,因此无用。

So IS and IS NOT is really only useful when used with NULL operand. With regular literal operand it is the same as = (or !=). And IS NULL can be satisfied by an index.

因此IS和IS NOT仅在与NULL操作数一起使用时才有用。对于常规的文字操作数,它与=(或!=)相同。并且索引可以满足IS NULL。

#1