MySQL 通配符 Like 多词查询

Question

我有一个 mysql 查询如下。

$query="SELECT name,activity FROM appid 
where result > 5 AND name LIKE :term ORDER BY name ASC LIMIT 0,40";

$result = $pdo->prepare($query);

$result->bindvalue(':term','%'.$_GET["q"].'%',PDO::PARAM_STR);
$result->execute();

我想做的就是这个。

我有这样的条目，我想找到

“新闻与天气”

但是当我输入

'新闻天气'

它当然不会找到它。我怎样才能输入并检索该条目？

Answer 1

Regular expressions可以做到这一点:

select *
from appid
where name rlike 'news|weather' -- Matches 'news' or 'weather'

另一个例子:

select *
from appid
where name rlike 'news.*weather' -- Matches 'news' and 'wether' 
                                 -- with any characters in-between or none at all
                                 -- (ordered)

还有一个:

select *
from appid
where name rlike '^news.{1,}weather$' -- Matches any text that starts with 'news'
                                      -- and has at least one character before
                                      -- ending with 'weather'

常规的 espressions 可用于在文本字段上创建非常复杂的过滤器。阅读上面的链接以获取更多信息。

---

如果你能为你的表添加一个全文索引，Full-text search可能是更好的方法。具体来说，一个 boolean Full-Text search :

select *
from appid
where match(name) against (+news +weather)