逃生通配符（％，_）SQLite中LIKE不牺牲指数用？ [英] Escape wildcards (%, _) in SQLite LIKE without sacrificing index use?

问题描述

我有一对夫妇与SQLite的查询问题。其实我开始认为SQLite是不是表的设计超过10行，真的，SQLite是一个噩梦。

I have a couple of issues with SQLite query. Actually I start thinking that SQLite is not designed for tables with more then 10 rows, really, SQLite is a nightmare.

下面的查询

SELECT * FROM [Table] WHERE [Name] LIKE 'Text%'

它工作正常。 EXPLAIN 显示，使用索引，并在大约结果返回 70ms的。

It works fine. EXPLAIN shows that the index is used and result is returned after about 70ms.

现在我需要运行的.NET SQLite的驱动程序这个查询，所以我改变了查询

Now I need to run this query from .NET SQLite driver, so I'm changing query

SELECT * FROM [Table] WHERE [Name] LIKE @Pattern || '%'

指数不被使用。当我在任意的SQLite工具，下面的查询中不使用索引，以及

Index is not used. When I run the following query in any SQLite tool the index is not used as well

SELECT * FROM [Table] WHERE [Name] LIKE 'Text' || '%'

所以我想SQLite没有任何一种实现preprocessing逻辑。

So I guess SQLite doesn't have any kind of preprocessing logic implemented.

确定。让我们试着去解决它，我还是绑定变量，执行以下操作

OK. Let's try to solve it, I'm still binding variables and doing the following

SELECT * FROM [Table] WHERE [Name] LIKE @Pattern

但现在我追加％通配符我的模式字符串的结尾，像这样

But now I append % wildcard symbol to the end of my pattern string, like this

command.Parameters.Add(new SQLiteParameter("@Pattern", pattern + '%'));

它的工作原理很慢。我不能说为什么，因为当我运行SQLite的工具，这个查询它工作正常，但是当我从绑定.NET $ C $此变量c它的工作原理很慢。

It works very slow. I can't say why, because when I run this query from SQLite tool it works fine, however when I bind this variable from .NET code it works slow.

确定。我还在努力解决这个问题。我摆脱的模式参数绑定，并动态地构建这种情况。

OK. I'm still trying to solve this. I'm getting rid of the pattern parameter binding and building this condition dynamically.

pattern = pattern.Replace("'", "''");
pattern = pattern.Replace("%", "\\%");
where = string.Format("LIKE '{0}%' ESCAPE '\\'", pattern);

指数不被再次使用。它不使用，因为 ESCAPE 。我看到，当我运行

Index is not used again. It's not used because of ESCAPE. I see that when I run

EXPLAIN QUERY PLAN SELECT * FROM [Table] WHERE [Name] LIKE 'Text%' ESCAPE '\'

当我删除 ESCAPE 它开始再次使用索引，查询完成在60-70ms。

As soon as I remove ESCAPE it starts using index again and the query finishes in 60-70ms.

更新

下面是结果。

EXPLAIN QUERY PLAN
SELECT * FROM [RegistryValues]
WHERE
     [ValueName] LIKE 'windir%' ESCAPE '\' 

扫描表RegistryValues​​（〜3441573行）

和一个没有 ESCAPE

EXPLAIN QUERY PLAN
SELECT * FROM [RegistryValues]
WHERE
     [ValueName] LIKE 'windir%'

使用INDEX IdxRegistryValues​​ValueNameKeyIdKeyHiveFileId（ValueName的＆GT ;?和ValueName的＆LT ;?）检索表RegistryValues​​（〜31250行）

更新

刚刚发现这个

http://www.sqlite.org/optoverview.html

4.0 LIKE优化

ESCAPE子句不能出现在LIKE操作符

所以，我应该怎么办呢？

So what should I do then?

难道我的理解对不对？使用 LIKE 运营商的SQLite 我不能搜索包含字符串通配符。说通配符我的意思是 _ ％ ^ ！

Do I understand it right? I can't search string containing wildcards using LIKE operator in SQLite. By saying wildcards I mean _ % ^ !

这是不可能的，只是因为我无法逃避它们。 其实我可以，但在这种情况下，我不能使用索引，所以查询将不会是有效的。

It's impossible simply because I can't escape them. Actually I can, but I can't use indexes in this case, so the query will not be efficient.

我说得对不对？

推荐答案

这是指数只能用 LIKE 子句中使用时，％为末，这样的SQLite可以将其改写为两个简单的比较（如图所示 EXPLAIN 输出）。

An index can only be used with a LIKE clause when the % is at the end so that SQLite can rewrite it to two simple comparisons (as shown in the EXPLAIN output).

因此​​，要获得同样的效果，自己编写的比较。 这就需要你构建一些是保证比较比任何匹配值'放大'字符串（提防非ASCII字符）。 相反的：

Therefore, to get the same effect, write the comparisons yourself. This requires that you construct some string that is guaranteed to compare 'larger' than any of the matched values (beware of non-ASCII characters). Instead of:

... WHERE Name LIKE 'Text%'

使用：

... WHERE Name BETWEEN 'Text' AND 'Textzzzzzzzzzzzzz'

，或者如参数：

or, as parameter:

... WHERE Name BETWEEN @Pattern AND @Pattern || 'zzzzzzzzzz'

（这个结构永远不需要逃逸。）

(This construct never needs escapes. :)

这篇关于逃生通配符（％，_）SQLite中LIKE不牺牲指数用？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！