sql - MySQL:有效地查找列的内容是字符串开头的行

Question

我有一个 MySQL 表，其中一列包含字符串前缀。例如，这些前缀可以是 Unix 文件系统上的顶级目录:

my_table:    
+---------+
| prefix  |
+---------+
|  /usr/  |
|  /bin/  |
|  /var/  |
|  /lib/  |
+---------+

我如何编写一个查询来有效地查找此表中所有行，其中 prefix 列的值是给定字符串的开头？

例如给定字符串 '/usr/bin/cat' 我如何编写一个查询来找到包含 '/usr/' 的行，这是开始'/usr/bin/cat'。

我的第一个猜测是这样使用 LIKE:

SELECT * FROM my_table
WHERE '/usr/bin/cat' LIKE CONCAT(prefix, '%')

但恐怕这个查询不会使用我在前缀列上的索引。

我还想到了以下内容:

SELECT * FROM my_table
WHERE prefix <= '/usr/bin/cat' ORDER BY prefix DESC LIMIT 1

按字典顺序检索等于或紧接在'/usr/bin/cat' 之前的前缀。然后我可以验证 prefix 是否真的以 '/usr/bin/cat' 开头。

但这只适用于单行，我想知道这是否是最佳解决方案。

编辑: 我以根目录为例，但我想知道是否也有处理任意字符串的方法。也许这些字符串不包含路径分隔符，或者前缀可能有几层深。说:'/usr/lib'。

编辑: 看来我的第二个查询是假的。 '/usr/' 小于 '/usr/bin/cat' 但 '/usr/a' 也是如此。该查询仍然比大表上的全表扫描快得多，但要使其正常工作，我必须获取更多行并遍历它们，直到找到第一个实际前缀。

所以索引似乎可以帮助进行这种前缀搜索，但我仍然不知道利用它的最佳方式。

Answer 1

-- 情况:我们不知道在哪里可以切割字符串。

-- 但我们必须知道前缀的最大长度。

-- 编辑: 这也有助于了解前缀的最小长度 - 以消除许多我们不想找到的误报。 (最少 = 2 个字符)。

-- 这肯定会使用索引:在这个例子中它是 max.8 个字符。 x = 8-- 在您的应用程序中，只需尝试生成这样的 SQL 查询:-- 没有全表扫描，只是 (x - min +1) 次使用索引。希望这足够快! :)

SELECT * FROM my_table WHERE prefix = '/u'

 UNION

SELECT * FROM my_table WHERE prefix = '/us'

 UNION

SELECT * FROM my_table WHERE prefix = '/usr'

 UNION

SELECT * FROM my_table WHERE prefix = '/usr/'

 UNION

SELECT * FROM my_table WHERE prefix = '/usr/b'

 UNION

SELECT * FROM my_table WHERE prefix = '/usr/bi'

 UNION

SELECT * FROM my_table WHERE prefix = '/usr/bin';