在 MySql 中，查找具有给定前缀的字符串
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2011304/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
In MySql, find strings with a given prefix
提问by Oren Trutner
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.
在 MySql 中，我想定位其中一列中的字符串值以查询字符串开头（或与查询字符串相同）的记录。该列使用适当的整理顺序进行索引。但是，该列上没有全文搜索索引。
A good solution will:
一个好的解决方案将：
Use the indexon the column. Solutions that need to iterate over all the records in the table aren't good enough (several million records in the table)
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.
使用列上的索引。需要遍历表中所有记录的解决方案还不够好（表中有几百万条记录）
处理具有任何字符值的字符串。某些列值包含标点符号。查询字符串也可能如此。如果您的解决方案包含正则表达式字符或类似字符，请记住这一点。字符串是 UTF-8 编码的，但如果您的解决方案仅适用于 ASCII，它仍然很有用。
The closest I have at this point is
我在这一点上最接近的是
SELECT * FROM TableName WHERE ColumnName BETWEEN query AND <<query+1>>
Where <<query+1>>is pre-computed to lexicographically follow queryin the collation order. For example, if queryis "o hai" then <<query+1>>is "o haj".
哪里<<query+1>>是预先计算的，以按字典query顺序排列在排序规则中。例如，如果query是“o hai”，则<<query+1>>是“o haj”。
回答by Crast
Surprisingly, a LIKEquery will use an index just fine if you're doing a prefix search.
令人惊讶的是，LIKE如果您进行前缀搜索，查询将使用索引就好了。
SELECT * from TableName Where ColumnName LIKE 'o hai%'
will indeed use an index since it does not begin with a wildcard character.
确实会使用索引，因为它不以通配符开头。
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
此（和其他行为）记录在“MySQL 如何使用索引”文档中：http: //dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html
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 EXPLAINquery to make sure, sometimes other reasons can preclude indexes from working such as needing to do an OPTIMIZE TABLEto update index cardinalities (though this can take ages and locks your table)
您将需要转义 '%' 字符并遵循正常的引用规则，但除此之外，任何 utf-8 输入前缀都应该起作用并完成这项工作。运行EXPLAIN查询以确保，有时其他原因可能会阻止索引工作，例如需要执行OPTIMIZE TABLE更新索引基数（尽管这可能需要很长时间并锁定您的表）
回答by Matthew Wood
Try this:
尝试这个：
SELECT * FROM tablename WHERE columname LIKE CONCAT(query, '%');
