SQLite Like 与 Substr
我想比较文本字段的最后 4 个字符。
就性能而言，我应该这样做：
select * from table where col like '%last';
或者
select * from table where substr(col,-4)='last';
I want to compare the last 4 characters of a Text field.
Performance wise, should I do:
select * from table where col like '%last';
OR
select * from table where substr(col,-4)='last';
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
使用结尾通配符时，您将无法利用索引：
另一种方法是将字符串的反向版本存储在另一列中，为反向内容列建立索引，然后使用 LIKE （或更好的，GLOB 函数[因为在几个 SQLite 实现中 LIKE() 已被重写，这也阻止了索引使用]传递您正在查找的字符串的反向版本例如，如果您正在查找。对于以以下结尾的 URL .edu 您将在反向列中搜索以“ude.”开头的值。
You won't be able to take advantage of an index when using the ends-with wildcard:
An alternative would be to store a reversed version of the string in another column, index that reverse-content column, and then use LIKE (or better, the GLOB function [because in several SQLite implementations LIKE() has been overridden, which also prevents index use] passing a reversed version of the string you're looking for. For example, if you were looking for URLs that ended with .edu you would search the reversed-column for values starting-with 'ude.'.
我尝试在查询之前使用
EXPLAIN来查看虚拟机操作，使用substr()函数的操作码比使用的操作码多 3 个 OP 代码喜欢。这并不意味着它一定会变慢，但表明它可能会变慢。I tried using
EXPLAINbefore the queries, to see the virtual machine operations, and the one with thesubstr()function had 3 more OP codes than the one withLIKE. That doesn't mean it is necessarily slower, but is an indication it might be.我不知道哪个具有更好的性能，但第一个更容易阅读，并且我预计性能不会有任何显着差异。
I don't know which has the better performance, but the first one is much better to read and I would not expect any significant differences in performance.
优化器概述页面第 4 节解释了 LIKE 运算符旨在优化此类表达式
col LIKE '不以百分号%开头的内容'。The optimizer overview page, section 4, explains that the LIKE operator is designed to optimize expressions of the kind
col LIKE 'something that does not begin with a percent sign%'.