使用包含关键字的字符串在 PHP 中进行 MySql 全文搜索
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17009208/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySql fulltext search in PHP using string containing keywords
提问by Vasko
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:
我有一个字符串，其中包含一些这样的关键字，$string = 'one, two, "phrase three words"'我正在尝试使用以下方法进行全文搜索：
SELECT *, MATCH (column) AGAINST ('$string') AS score,
FROM table WHERE MATCH (column) AGAINST ('$string' IN BOOLEAN MODE) ORDER BY score DESC
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.
当MySql返回结果时，关键字“词组三个词”不是作为词组匹配的，而是其中的每个词都作为一个词匹配。
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.
我应该如何修改字符串或查询以接收整个短语匹配的结果？我已经尝试对字符串使用 stripslashes()，但结果是一样的。
回答by user4035
As MySQLmanual says:
正如MySQL手册所说：
A phrase that is enclosed within double quote (“"”) characters matches only rows that contain the phrase literally, as it was typed.
包含在双引号 (“"”) 字符中的短语仅匹配按字面意思包含该短语的行，因为它是键入的。
Let's look at the example table:
让我们看一下示例表：
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 ... |
+----+-------------------+------------------------------------------+
Order matters, when the words are quoted:
当引用这些词时，顺序很重要：
mysql> SELECT * FROM articles WHERE MATCH (title,body)
AGAINST ('"comparison database"' IN BOOLEAN MODE);
Empty set (0.01 sec)
When we remove the quotes, it will search for rows, containing words "database" or "comparison":
当我们删除引号时，它将搜索包含单词“database”或“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 ... |
+----+---------------------+------------------------------------------+
Order doesn't matter now:
现在顺序无关紧要：
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 ... |
+----+---------------------+------------------------------------------+
If we want to get rows, containing either word "PostgreSQL" or phrase "database comparison", we should use this request:
如果我们想要获取包含单词“PostgreSQL”或短语“数据库比较”的行，我们应该使用这个请求：
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.
确保您正在搜索的单词不在停用词列表中，因此会被忽略。
