在MySql中，找到具有给定前缀的字符串 [英] In MySql, find strings with a given prefix
问题描述
在MySql中，我想定位记录之一，其中一列中的字符串值以查询字符串开头(或与查询字符串相同).该列以适当的排序规则索引.但是，该列上没有全文本搜索索引.
In MySql, I want to locate records where the string value in one of the columns begins with (or is the same as) a query string. The column is indexed with the appropriate collation order. There is no full-text search index on the column though.
一个好的解决方案将:
-
使用该列上的索引.需要遍历表中所有记录的解决方案还不够好(表中有几百万条记录)
Use the index on the column. Solutions that need to iterate over all the records in the table aren't good enough (several million records in the table)
使用具有任何字符值的字符串.一些列值包含标点符号.查询字符串可能也是如此.如果您的解决方案包含正则表达式字符或类似字符，请记住这一点.字符串是UTF-8编码的，但是如果您的解决方案仅适用于ASCII，那么它仍然有用.
Work with strings with any character values. Some of the column values contain punctuation characters. The query string might too. Keep this in mind if your solution includes regex characters or similar. The strings are UTF-8 encoded, but if your solution only works with ASCII it could still be useful.
我目前最接近的是
SELECT * FROM TableName WHERE ColumnName BETWEEN query AND <<query+1>>
其中<<query+1>>被预先计算为按排序顺序在字典上跟随query的位置.例如，如果query是"o hai"，则<<query+1>>是"o haj".
Where <<query+1>> is pre-computed to lexicographically follow query in the collation order. For example, if query is "o hai" then <<query+1>> is "o haj".
推荐答案
令人惊讶的是，如果您要进行前缀搜索，LIKE查询将使用索引就很好.
Surprisingly, a LIKE query will use an index just fine if you're doing a prefix search.
SELECT * from TableName Where ColumnName LIKE 'o hai%'
确实会使用索引，因为它不是以通配符开头.
will indeed use an index since it does not begin with a wildcard character.
(如何使用MySQL)使用索引"文档中记录了此(以及其他行为): http://dev.mysql.com/doc/refman/5.0 /en/mysql-indexes.html
This (and other behavior) is documented in the "How MySQL uses Indexes" doc: http://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html
您将需要转义'％'字符并遵循正常的引用规则，但除此之外，任何utf-8输入前缀都应该起作用并完成工作.运行EXPLAIN查询以确保，有时其他原因可能导致索引无法正常工作，例如需要执行OPTIMIZE TABLE更新索引基数(尽管这可能会花费很多时间并锁定您的表)
You will need to escape the '%' character and follow normal quoting rules, but other than that any utf-8 input prefix ought to work and do the job. Run an EXPLAIN query to make sure, sometimes other reasons can preclude indexes from working such as needing to do an OPTIMIZE TABLE to update index cardinalities (though this can take ages and locks your table)
这篇关于在MySql中，找到具有给定前缀的字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
