ZF2 - 用于全字搜索的 MySQL 正则表达式 [英] ZF2 - MySQL Regex for Whole word searches

问题描述

鉴于:您在 ZF2 模型中有一个功能正常的子字符串搜索.您如何将其转换为全字搜索?

Given: You have a functioning substring-search in a ZF2 model. How do you do turn that into a whole-word search?

推荐答案

一种方法是在谓词表达式中使用 MySQL REGEXP 调用.

One method would be to use a MySQL REGEXP call in a Predicate Expression.

其中 $select 是 Zend\Db\Sql\Select 的一个实例，$searchFor 是您的搜索词:

Where $select is an instance of Zend\Db\Sql\Select and $searchFor is your search term:

原始子字符串搜索可能会使用像这样的 where...

The original substring search might use a where like this...

$select->where(array(
    new Predicate\PredicateSet(
        array(
            new Zend\Db\Sql\Predicate\Like('tag', '%' . $searchFor . '%'),
            new Zend\Db\Sql\Predicate\Like('title', '%' . $searchFor . '%'),
        ),
        Zend\Db\Sql\Predicate\PredicateSet::COMBINED_BY_OR
    ),
));

...上面的全字版本是:

...and whole-word version of the above is:

$select->where(array(
    new Predicate\PredicateSet(
        array(
            new Zend\Db\Sql\Predicate\Expression("tag REGEXP '([[:<:]]" . $searchFor ."[[:>:]])'"),
            new Zend\Db\Sql\Predicate\Expression("title REGEXP '([[:<:]]" . $searchFor ."[[:>:]])'"),
        ),
        Zend\Db\Sql\Predicate\PredicateSet::COMBINED_BY_OR
    ),
));

请注意，这些是多字段搜索，所以我做了一个PredicateSet::COMBINED_BY_OR.我花了很长时间才停止在我的正则表达式中使用 \b .希望这可以节省一些人的时间.

Note these are multi-field searches, so I've done a PredicateSet::COMBINED_BY_OR. It took me far too long to stop fooling around with \b in my regex. Hope this saves someone out there some time.

这篇关于ZF2 - 用于全字搜索的 MySQL 正则表达式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！