SQLite使用||拼接的LIKE查询无法命中索引问题咨询

SQLite的查询优化器对LIKE走前缀索引的判定规则非常死板：只有当LIKE右侧的模式串是编译阶段就能确定的常量，且%出现在常量末尾时，优化器才会自动把LIKE条件转换成字段 >= 前缀 AND 字段 < 前缀上界的范围查询，从而命中B树索引。
只要你在LIKE右侧用了||做拼接，哪怕拼接的两边都是写死的常量，优化器也会把整个右侧判定为运行时才能计算的表达式，不会提前做常量求值，自然匹配不到LIKE的索引优化规则，直接退化成全表扫描。
你之前写'?1%'识别不到参数是符合SQLite语法逻辑的：参数占位符必须是独立的语法单元，被单引号包裹的?1只会被当成普通字符串内容，不会被解析成待绑定的参数。

方案1：应用层拼接模式串后再做参数绑定（推荐） #

这是性能最好、兼容性最强的方案，完全不会触发索引失效问题。
写SQL的时候不要在语句里拼%，直接把LIKE的模式串整体作为参数绑定即可：

-- SQL语句仅保留占位符
select * from users where login like ?1 limit 10;

举个例子，如果你要查前缀为username的用户，直接在业务代码里把参数值拼成username%，再绑定给?1，执行时就会正常走login_index的范围扫描，和你直接写常量字符串的执行计划完全一致。

方案2：SQL内手动改写为等价范围查询 #

如果因为场景限制不能在应用层拼串，可以直接绕过LIKE的优化限制，手动写等价的范围条件：

select * from users 
where login >= ?1 
  and login < ?1 || X'FF'
limit 10;

这里X'FF'是SQLite支持的最大单字节值，?1 || X'FF'刚好大于所有以?1为前缀的字符串，和like ?1 || '%'的逻辑完全等价。这种写法下优化器能直接识别范围条件，正常命中login字段上的普通B树索引，和你当前开启case_sensitive_like=on的配置完全匹配。

方案3：调高优化等级触发常量折叠（不推荐） #

新版本SQLite可以通过PRAGMA optimization_level=3开启激进优化，让优化器在生成执行计划阶段提前计算常量拼接表达式的结果，有概率让||拼接的LIKE条件命中索引。但这个行为在不同SQLite版本上表现不一致，兼容性差，生产环境不建议依赖。

内容的提问来源于stack exchange，提问作者userQWERTY