如果查询包含constenation语法“ ||”，则sqlite停止使用索引。
这是常规查询的查询计划。它使用索引应应有的：
sqlite> PRAGMA case_sensitive_like=on;
sqlite> explain query plan select * from users where login like 'username%' limit 10;
QUERY PLAN
`--SEARCH users USING INDEX login_index (login>? AND login<?)
这是相同查询的查询计划，但是使用Concatenation ||语法：
sqlite> PRAGMA case_sensitive_like=on;
sqlite> explain query plan select * from users where login like 'username' || '%' limit 10;
QUERY PLAN
`--SCAN users
相同的查询不使用索引并切换整个扫描整个表。
我要使用串联的原因||语法是因为否则用户名不会被识别为参数，即select * select * select * select *从登录的用户中select *？1 ％'限制10不起作用 - sqlite认为查询具有0个参数。
This is a query plan for a regular LIKE query. It uses an index as it should:
sqlite> PRAGMA case_sensitive_like=on;
sqlite> explain query plan select * from users where login like 'username%' limit 10;
QUERY PLAN
`--SEARCH users USING INDEX login_index (login>? AND login<?)
This is a query plan for the same query, but that uses concatenation || syntax:
sqlite> PRAGMA case_sensitive_like=on;
sqlite> explain query plan select * from users where login like 'username' || '%' limit 10;
QUERY PLAN
`--SCAN users
The same query doesn't use an index and switches to scanning whole table.
The reason I want to use concatenation || syntax is because otherwise username won't be recognized as a parameter, i.e. select * from users where login like '?1%' limit 10 doesn't work - sqlite considers that query has 0 parameters.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
那是正常的，有记录的行为。
喜欢optimization 可以使用；他们之中：您正在使用表达式。
您可以尝试的是将
％附加到您要绑定到准备和执行查询的代码中的参数的字符串末尾，而不是在SQL语句中：login of like？，用户名％作为该参数。That's normal, documented behavior. The
LIKEoptimization has a lot of rules and caveats about when it can be used; among them:You're using an expression.
What you can try is appending the
%to the end of the string you're binding to the parameter in the code that prepares and executes the query, not in the SQL statement:login LIKE ?, withusername%as that parameter.