php mysql 匹配 ~ 示例

mysql match against ~ example

提问by Harry

Below is an example sql I am stuck with, it will not return a hotel named "mill hotel" It returns 10 other hotels. Any help would be great thanks

下面是我坚持使用的一个 sql 示例，它不会返回名为“mill hotel”的酒店，而是返回 10 家其他酒店。任何帮助都会非常感谢

---

---

SELECT * FROM tbl WHERE match(hotel) against('the mill hotel' IN BOOLEAN MODE) LIMIT 10";

---

---

回答by Roberto

You need to specify the operators, because not specifying any results in an OR operation. Try this:

您需要指定运算符，因为在 OR 运算中未指定任何结果。尝试这个：

SELECT * FROM tbl WHERE match(hotel) against('+the mill hotel' IN BOOLEAN MODE) LIMIT 10";

Read more

阅读更多

回答by Richard Wiseman

You can always use quotes to match the phrase mill hotelrather than the words milland/or hotel:

您始终可以使用引号来匹配短语mill hotel而不是单词mill和/或hotel：

SELECT * FROM tbl WHERE match(hotel) against('"the mill hotel"' IN BOOLEAN MODE) LIMIT 10;

or, simply:

或者，简单地说：

SELECT * FROM tbl WHERE match(hotel) against('"mill hotel"' IN BOOLEAN MODE) LIMIT 10;

I believe that all

我相信所有

SELECT * FROM tbl WHERE match(hotel) against('+the mill hotel' IN BOOLEAN MODE) LIMIT 10;

does is require the word theto match, leaving milland hotelas optional: if you want to require the whole phrase, it needs to be in quotes:

do 是要求单词the匹配，离开mill和hotel可选：如果你想要求整个短语，它需要用引号引起来：

SELECT * FROM tbl WHERE match(hotel) against('+"the mill hotel"' IN BOOLEAN MODE) LIMIT 10;

although when you've only got one search phrase, as in this example, the plus sign is redundant.

尽管当您只有一个搜索词组时，如本例所示，加号是多余的。

回答by Ramiro Zendejas

12.9.2 Boolean Full-Text Searches

12.9.2 布尔全文搜索

MySQL can perform boolean full-text searches using the IN BOOLEAN MODE modifier.

MySQL 可以使用 IN BOOLEAN MODE 修饰符执行布尔全文搜索。

Boolean full-text searches have these characteristics:

布尔全文搜索具有以下特点：

• They do not automatically sort rows in order of decreasing relevance.

• 它们不会按照相关性递减的顺序自动对行进行排序。

12.9.1 Natural Language Full-Text Searches

12.9.1 自然语言全文搜索

By default or with the IN NATURAL LANGUAGE MODE modifier, the MATCH() function performs a natural language search for a string against a text collection. A collection is a set of one or more columns included in a FULLTEXT index. The search string is given as the argument to AGAINST(). For each row in the table, MATCH() returns a relevance value; that is, a similarity measure between the search string and the text in that row in the columns named in the MATCH() list.

默认情况下或使用 IN NATURAL LANGUAGE MODE 修饰符，MATCH() 函数针对文本集合对字符串执行自然语言搜索。集合是包含在 FULLTEXT 索引中的一组一个或多个列。搜索字符串作为 AGAINST() 的参数给出。对于表中的每一行，MATCH() 返回一个相关值；也就是说，搜索字符串与 MATCH() 列表中命名的列中该行中的文本之间的相似性度量。

So, you need to sort them or remove the limit on your results.

因此，您需要对它们进行排序或取消对结果的限制。

回答by Ludo

To execute the following mysql query based on a phrase in PHP

根据 PHP 中的短语执行以下 mysql 查询

SELECT * 
FROM tbl 
WHERE match(hotel) against('"the mill hotel"' IN BOOLEAN MODE)

You have to do like this:

你必须这样做：

$phrase = "the mill hotel";
$phrase= '"'.$phrase.'"'; // Adds quotes around string

$stmt = $db->prepare('SELECT * FROM tbl WHERE MATCH(hotel)    AGAINST(:phrase )');
$stmt->bindParam(':phrase ', $phrase , PDO::PARAM_STR);
$stmt->execute();

Thank you to @ub3rst4r for his answer: Force exact string MATCH for PDO prepared statements

感谢@ub3rst4r 的回答：为 PDO 准备好的语句强制精确字符串匹配

回答by Fahim Parkar

Use + (plus)in such case...

+ (plus)在这种情况下使用...

SELECT * FROM tbl WHERE match(hotel) against ('+the mill hotel' IN BOOLEAN MODE) LIMIT 10";

Read thisfor detailed information.

阅读本文了解详细信息。