关于函数索引的Sqlite glob(Sqlite glob on a function index)

关于函数索引的Sqlite glob(Sqlite glob on a function index)

使用最新的SQLite，我可以在文本字段的确定性函数上建立索引（例如MYFUNC（MyCol））。 但每当我尝试对此进行全局搜索时，例如

select * from myTable where MYFUNC(MyCol) glob 'Z*'

它似乎没有使用索引，而是在表中的每一行上慢慢调用MYFUNC。 根据https://www.sqlite.org/optoverview.html，您不能在带有索引的glob表达式中使用重载的内置函数，但它并不表示您不能使用非重载自定义函数。 这实际上是另一个限制，还是我做错了什么？

（这似乎是一件非常普遍的事情，例如，通过使用标准化字段格式的MYFUNC，允许在名称字段上搜索而不会对重音，前缀等敏感。

Using the latest SQLite, I can make an index on a deterministic function of a text field (e.g. MYFUNC(MyCol)). But whenever I try to do a glob search on this, e.g.

select * from myTable where MYFUNC(MyCol) glob 'Z*'

it does not appear to use the index, but instead slowly calls MYFUNC on every row in the table. According to https://www.sqlite.org/optoverview.html you cannot use overloaded built-in functions in glob expressions with an index, but it doesn't say you can't use non-overloading custom function. Is this actually another limitation, or am I doing something wrong?

(it seems like a very common thing to want to do, e.g. to allow searching on name fields without sensitivity to accents, prefixes, etc, by using a MYFUNC that standardizes field formatting).

最满意答案

您的自定义函数可能会使索引无效。 即使它理论上没有，我也不希望sqlite能够解决这个问题。

实际上，您链接的文档确切地说：

这个用途有很多条件： 1. LIKE或GLOB运算符的左侧必须是具有TEXT亲和性的索引列的名称。 2. [...]

因此，将任何函数应用于列将阻止sqlite使用索引。

做你想做的事情的方法是在你的表中有一个额外的列：称之为MyCol_clean或其他什么。 确保在MyCol更改时更新它（您可以使用触发器执行此操作）。

添加新列（当然在那里创建索引），您可以有效地搜索您的表，还有额外的好处， MYFUNC在查询期间根本不需要评估MYFUNC 。

Your custom function could be making the index useless. Even if it theoretically doesn't, I wouldn't expect sqlite to be able to figure that out.

Actually, the docs you link say exactly that:

There are many conditions on this use: 1. The left-hand side of the LIKE or GLOB operator must be the name of an indexed column with TEXT affinity. 2. [...]

So applying any function to the column will prevent sqlite from utilizing the index.

The way to do what you're trying to do is to have an extra column in your table: call it MyCol_clean or something. Make sure to update it whenever MyCol changes (you can do this with triggers).

With the new column added (creating an index on there instead, of course), you can efficiently search your table, with the added bonus of not having to evaluate MYFUNC at all during queries.