MySQL mysql喜欢在字符串上匹配完整的单词或单词的开头
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19717713/
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
mysql like to match complete word or beginning of word on string
提问by Paolo
Given a search string I need to select every record where (in the field the search is performed on) there is at least one word that begins with the given text.
给定一个搜索字符串，我需要选择每条记录，其中（在执行搜索的字段中）至少有一个以给定文本开头的单词。
For example:
例如：
'John Doe'
Have to be be selected with search strings like:
必须使用搜索字符串进行选择，例如：
'joh'
'do'
'JOHN doe'
Have notto be selected with
有没有被选中
'ohn'
'oe'
I need (possibly) to avoid full text search.
我需要（可能）避免全文搜索。
What I've found to work is
我发现有效的是
$query = 'SELECT * FROM MYTABLE WHERE SEARCHFIELD LIKE "' . $searchText . '%"'
. 'OR SEARCHFIELD LIKE "% ' . $searchText . '%"'
I'm asking if there is a better way to do that.
我在问是否有更好的方法来做到这一点。
(for "better way" I mean better performanceor same performance but more elegant)
（对于“更好的方式”我的意思是更好的性能或相同的性能但更优雅）
Also, as the query will be built up with a prepared statement, how should I unescape LIKEmetacharacters in the search string?
此外，由于查询将使用准备好的语句构建，我应该如何取消搜索字符串中的LIKE元字符？
采纳答案by Paolo
As already stated in the question the query
如问题中所述，查询
$query = 'SELECT * FROM MYTABLE WHERE SEARCHFIELD LIKE "' . $searchText . '%"'
. 'OR SEARCHFIELD LIKE "% ' . $searchText . '%"'
works for matching records where the SEARCHFIELDcontains a word that begins with (or is equal to) $searchText
适用于匹配SEARCHFIELD包含以（或等于）开头的单词的记录$searchText
Regarding performance I've made a test on my development machine MBP 2,2 GHz i7 quad core:
关于性能，我在我的开发机器上进行了测试MBP 2,2 GHz i7 quad core：
Searching for a word on 4.000 records takes around 40 milliseconds.
在 4.000 条记录上搜索一个词大约需要 40 毫秒。
Records are normally indexed (no fulltext).
记录通常被索引（没有全文）。
I have few thousands records and the query doesn't run very often so for me is good.
The solution may not be suitable for other contexts.
我有几千条记录，查询不经常运行，所以对我来说很好。
该解决方案可能不适用于其他上下文。
To build a prepared statement with the above query I used the technique described here:
为了使用上述查询构建准备好的语句，我使用了此处描述的技术：
The resulting code is as follows:
结果代码如下：
function like($s, $e)
{
return str_replace(array($e, '_', '%'), array($e . $e, $e . '_', $e . '%'), $s);
}
/* ... */
/* create a prepared statement */
$stmt = $mysqli->prepare(
'SELECT * FROM MYTABLE WHERE SEARCHFIELD LIKE ? ESCAPE "=" OR SEARCHFIELD LIKE ? ESCAPE "="'
);
if( $stmt )
{
/* escape the text */
$escSearchText = like( $searchText, "=" );
/* 'like' parameters */
$like1 = $escSearchText . "%";
$like2 = "%" . $escSearchText . "%";
/* bind parameters for markers */
$stmt->bind_param( "ss", $like1, $like2 );
/* ... */
回答by Barmar
Use this:
用这个：
$query = "SELECT * FROM MyTable WHERE searchfield LIKE CONCAT('%', ?, '%')";
You don't need the ORcondition -- if a field matches search%, it will also match %search%.
您不需要OR条件——如果一个字段匹配search%，它也将匹配%search%。
