使用包含关键字的字符串在PHP中进行MySQL全文搜索

本文介绍了使用包含关键字的字符串在PHP中进行MySQL全文搜索的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有一个包含一些像这样的关键字的字符串$string = 'one, two, "phrase three words"' 我正在尝试使用以下内容进行全文搜索:

I have a string that contains some keywords like this $string = 'one, two, "phrase three words"' I am trying to do a full-text search using:

SELECT *, MATCH (column) AGAINST ('$string') AS score, FROM table WHERE MATCH (column) AGAINST ('$string' IN BOOLEAN MODE) ORDER BY score DESC

当MySql返回结果时，关键字短语3个单词"不作为短语匹配，但其中的每个单词都作为一个单词匹配.

When MySql gives back the results, the keyword "phrase three words" is not matched as a phrase, but every word from it is matched as a single.

我应该如何修改字符串或查询以接收整个短语匹配的结果?我已经尝试过使用stripslashes()作为字符串，但是结果是相同的.

How should I modify the string or the query to receive the results where the whole phrase matches? I have tried with stripslashes() for the string, but the result is the same.

推荐答案

为 MySQL 手册说:

包含在双引号(")字符中的短语匹配 仅包含按字面意思输入的短语的行.

A phrase that is enclosed within double quote (""") characters matches only rows that contain the phrase literally, as it was typed.

让我们看一下示例表:

mysql> select * from articles; +----+-----------------------+------------------------------------------+ | id | title | body | +----+-----------------------+------------------------------------------+ | 1 | PostgreSQL Tutorial | DBMS stands for DataBase ... | | 2 | How To Use MySQL Well | After you went through a ... | | 3 | Optimizing MySQL | In this tutorial we will show ... | | 4 | 1001 MySQL Tricks | 1. Never run mysqld as root. 2. ... | | 5 | MySQL vs. YourSQL | In the following database comparison ... | | 6 | MySQL Security | When configured properly, MySQL ... | +----+-----------------------+------------------------------------------+ mysql> SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('"database comparison"' IN BOOLEAN MODE); +----+-------------------+------------------------------------------+ | id | title | body | +----+-------------------+------------------------------------------+ | 5 | MySQL vs. YourSQL | In the following database comparison ... | +----+-------------------+------------------------------------------+

引号中的顺序很重要:

mysql> SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('"comparison database"' IN BOOLEAN MODE); Empty set (0.01 sec)

当我们删除引号时，它将搜索包含单词"database"或"comparison"的行:

When we remove the quotes, it will search for rows, containing words "database" or "comparison":

mysql> SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('database comparison' IN BOOLEAN MODE); +----+---------------------+------------------------------------------+ | id | title | body | +----+---------------------+------------------------------------------+ | 1 | PostgreSQL Tutorial | DBMS stands for DataBase ... | | 5 | MySQL vs. YourSQL | In the following database comparison ... | +----+---------------------+------------------------------------------+

订购现在无关紧要:

mysql> SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('comparison database' IN BOOLEAN MODE); +----+---------------------+------------------------------------------+ | id | title | body | +----+---------------------+------------------------------------------+ | 1 | PostgreSQL Tutorial | DBMS stands for DataBase ... | | 5 | MySQL vs. YourSQL | In the following database comparison ... | +----+---------------------+------------------------------------------+

如果要获取包含单词"PostgreSQL"或短语数据库比较"的行，则应使用此请求:

If we want to get rows, containing either word "PostgreSQL" or phrase "database comparison", we should use this request:

mysql> SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('PostgreSQL "database comparison"' IN BOOLEAN MODE); +----+---------------------+------------------------------------------+ | id | title | body | +----+---------------------+------------------------------------------+ | 1 | PostgreSQL Tutorial | DBMS stands for DataBase ... | | 5 | MySQL vs. YourSQL | In the following database comparison ... | +----+---------------------+------------------------------------------+

小提琴

请确保您要搜索的单词不在停用词列表，将被忽略.

Make sure, that the words, you are searching for, are not in the list of stopwords, that are ignored.