如何在SQL中搜索多个关键字 [英] How to search for multiple keywords in sql

问题描述

我有一个当前的SQL搜索查询，该查询允许用户输入关键字来搜索我的SQL数据库.此刻，搜索将使用多个单词，但只能使用相同的顺序，即"Ford Mustang"将以该精确顺序显示包含"Ford Mustang"的结果.如果用户输入"Mustang"或"Mustang Ford"，则不会显示任何结果.如何更改搜索查询以显示具有两个关键字但不一定以相同顺序显示的结果?我已经搜索了该站点和其他站点，发现了类似的问题，但是找不到我能够解决的任何答案.

public function getProductByName($name){
    $stmt = $this->pdo->prepare('SELECT * FROM tbl_products WHERE name LIKE :name');
    $name = "%".$name."%";
    $stmt->execute(array('name' => $name));
    return $stmt;
}

有人建议使用以下方法，该方法可以搜索两个关键字，但我需要搜索两个关键字，只是顺序不一.

public function getProductByName($name){
    $stmt = $this->pdo->prepare('SELECT * FROM tbl_products WHERE name REGEXP :names');
    $names = "[[:<:]](" . str_replace(" ", "|", $name) . ")[[:>:]]";
    $stmt->execute(array('names' => $names));
    return $stmt;
}

解决方案

如果可以更改表以将FULLTEXT添加到名称列，则可以使用 在此处查看演示

I have a current SQL search query that lets users enter keywords to search for my SQL database. At the moment, the search will work with multiple words, but only the same order, ie "Ford Mustang" will show results that include "Ford Mustang" in that exact order. If a user types in "Mustang" or "Mustang Ford" then no results show. How do I change the search query to show results that have both keywords, but not necessarily in the same order? I have search through this site and others and have found similar questions, but have not been able to find any answers that I have been able to work out.

public function getProductByName($name){
    $stmt = $this->pdo->prepare('SELECT * FROM tbl_products WHERE name LIKE :name');
    $name = "%".$name."%";
    $stmt->execute(array('name' => $name));
    return $stmt;
}

Someone suggested the following, which works to search for either keyword, but I need to search for both keywords, just not in order.

public function getProductByName($name){
    $stmt = $this->pdo->prepare('SELECT * FROM tbl_products WHERE name REGEXP :names');
    $names = "[[:<:]](" . str_replace(" ", "|", $name) . ")[[:>:]]";
    $stmt->execute(array('names' => $names));
    return $stmt;
}

解决方案

If you can alter your table to add FULLTEXT to your name column then you can use the Full text Search option.

select *
from tbl_products
where match(name) against ('+Mustang +Ford' in boolean mode)

Check demo here

这篇关于如何在SQL中搜索多个关键字的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！