sqlite - LIKE 语句可以优化成不做全表扫描吗？

Question

我想通过树路径从表中获取子树。

path 列存储如下字符串:

foo/
foo/bar/
foo/bar/baz/

如果我尝试选择以特定路径开头的所有记录:

EXPLAIN QUERY PLAN SELECT * FROM f WHERE path LIKE "foo/%"

它告诉我该表已被扫描，即使 path 列已编入索引 :(

有什么方法可以让 LIKE 使用索引而不扫描表吗？

我找到了一种方法来实现我想要的闭包表，但是它更难维护并且写入速度非常慢......

Answer 1

为了能够在 SQLite 中为 LIKE 使用索引，

1. 表格列必须有 TEXT affinity ，即有一种 TEXT 或 VARCHAR 或类似的东西；和

2. 索引必须声明为 COLLATE NOCASE(直接声明，或者因为该列已声明为 COLLATE NOCASE):

   > CREATE TABLE f(path TEXT);
   > CREATE INDEX fi ON f(path COLLATE NOCASE);
   > EXPLAIN QUERY PLAN SELECT * FROM f WHERE path LIKE 'foo/%';
   0|0|0|SEARCH TABLE f USING COVERING INDEX fi (path>? AND path<?)
   

可以使用 case_sensitive_like PRAGMA 删除第二个限制。 ，但这会改变 LIKE 的行为。或者，可以使用区分大小写的比较，方法是将 LIKE 'foo/%' 替换为 GLOB 'foo/*'。