mysql喜欢在字符串上匹配完整的单词或单词的开头 [英] mysql like to match complete word or beginning of word on string

问题描述

给出搜索字符串，我需要选择每条记录(在执行搜索的字段中)至少有一个以给定文本开头的单词的记录.

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.

例如:

'John Doe'

必须通过以下搜索字符串进行选择:

Have to be be selected with search strings like:

'joh'
'do'
'JOHN doe'

必须不与

'ohn'
'oe'

(可能)我需要避免全文搜索.

I need (possibly) to avoid full text search.

我发现可以工作的是

$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 performance or same performance but more elegant)

此外，由于查询将使用准备好的语句构建，因此我应该如何在搜索字符串中取消转义 Like 元字符?

Also, as the query will be built up with a prepared statement, how should I unescape LIKE metacharacters in the search string?

推荐答案

问题已经说明了

$query = 'SELECT * FROM MYTABLE WHERE SEARCHFIELD LIKE "' . $searchText . '%"'
                                . 'OR SEARCHFIELD LIKE "% ' . $searchText . '%"'

用于匹配记录，其中SEARCHFIELD包含以(c1)开头(或等于)$searchText

works for matching records where the SEARCHFIELD contains a word that begins with (or is equal to) $searchText

关于性能，我已经在开发计算机MBP 2,2 GHz i7 quad core上进行了测试:

Regarding performance I've made a test on my development machine MBP 2,2 GHz i7 quad core:

在4.000条记录中搜索单词大约需要40毫秒.

Searching for a word on 4.000 records takes around 40 milliseconds.

记录通常被索引(没有全文).

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:

转义MySQL通配符

结果代码如下:

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 );

/* ... */

这篇关于mysql喜欢在字符串上匹配完整的单词或单词的开头的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！