sql - 为什么 SQLite 在使用 'IS' 而不是 '=' 时不使用索引？

Question

此查询不会使用索引:SELECT * FROM baz WHERE id IS '123'
http://sqlfiddle.com/#!7/7e299/2

此查询将使用索引:SELECT * FROM baz WHERE id='123'
http://sqlfiddle.com/#!7/7e299/1

为什么？？

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

Answer 1

不需要支持此类查询的索引。

首先，这就是 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

对于 IS运算符(operator)， NULL values are special :

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).

在你的问题中，

SELECT * FROM baz WHERE id IS '123'

和

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

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

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

其中括号中的表达式始终为假，因此无用。

所以 IS和 IS NOT仅在与 NULL 一起使用时才有用操作数。对于常规文字操作数，它与 = 相同(或 !=)。和 IS NULL可以通过一个索引来满足。