有效地查询包含子字符串的列 [英] Effectively query on column that includes a substring

问题描述

给定一个字符串列，其值类似于 / 123/12/34/56/5/ ，查询所有的最佳方式是什么？包含给定数字的记录（例如 12 ）？

Given a string column with a value similar to /123/12/34/56/5/, what is the optimal way of querying for all the records that include the given number (12 for example)?

我的顶部解决方案head是：

The solution from top of my head is:


SELECT id FROM things WHERE things.path LIKE'％/ 12 /％'


但是由于领先的％，AFAIK此查询无法使用列上的索引。

But AFAIK this query can't use indexes on the column due to the leading %.

必须有更好的东西。它是什么？

There must be something better. What is it?

使用PostgreSQL，但更喜欢可以在其他数据库中使用的解决方案。

Using PostgreSQL, but would prefer the solution that would work across other DBs too.

推荐答案

在PostgreSQL 9.1中，你可以利用 pg_trgm 模块并用它构建一个GIN索引。

In PostgreSQL 9.1 you could utilize the pg_trgm module and build a GIN index with it.

CREATE EXTENSION pg_trgm; -- once per database

CREATE INDEX things_path_trgm_gin_idx ON things USING gin (path gin_trgm_ops);

您的 LIKE 表达式甚至可以使用此索引如果没有左锚定。

Your LIKE expression can use this index even if it is not left-anchored.

详见来自depesz的演示。

将其标准化如果你可以。

这篇关于有效地查询包含子字符串的列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！