PHP中使用字符串包含关键字的MySql全文搜索 [英] MySql fulltext search in PHP using string contaning keywords
问题描述
我有一个字符串，其中包含一些像这样的关键字 $ string ='one，two，短语三个字'
我想要做一个使用以下全文搜索：
pre $ SELECT *，MATCH（column）AGAINST（'$ string'）AS评分，
FROM表WHERE MATCH（column）AGAINST（'$ string'in BOOLEAN MODE）ORDER BY score DESC
当MySql返回结果时，关键字短语三个单词不匹配为一个短语，但每个单词都匹配为一个单词。
如何修改字符串或查询以接收整个短语匹配的结果？我已经试过用stripslashes（）作为字符串，但结果是一样的。 ：//dev.mysql.com/doc/refman/5.5/en/fulltext-boolean.htmlrel =noreferrer> MySQL 手册上说：
< blockquote>
包含在双引号（）字符中的短语仅与包含该短语的行相匹配
，因为它是键入的。
让我们看一下示例表：
mysql> select * from文章;
+ ---- + ----------------------- + -------------- ---------------------------- +
| id | title | body |
+ ---- + ----------------------- + -------------------------- ---------------- +
| 1 | PostgreSQL教程| DBMS代表数据库... |
| 2 |如何使用MySQL |在您之后通过... |
| 3 |优化MySQL |在这个啧啧orial我们将显示... |
| 4 | 1001 MySQL技巧| 1.永远不要以root身份运行mysqld。 2. ... |
| 5 | MySQL与YourSQL |在以下数据库比较中... |
| 6 | MySQL安全|如果配置正确，MySQL ... |
+ ---- + ----------------------- + ---------------- -------------------------- +
mysql> SELECT * FROM articles WHERE MATCH（title，body）
AGAINST（''database comparison''IN BOOLEAN MODE）;
+ ---- + ------------------- + ---------------- -------------------------- +
| id |标题| body |
+ ---- + ------------------- + -------------------- ---------------------- +
| 5 | MySQL与YourSQL |在下面的数据库比较中... |
+ ---- + ------------------- + -------------------- ---------------------- +
<当单词被引用时，命令很重要：
mysql> SELECT * FROM articles WHERE MATCH（title，body）
AGAINST（''比较数据库''IN BOOLEAN MODE）;
空集（0.01秒）
当我们删除引号时，将搜索包含单词database或comparison的行：
mysql> SELECT * FROM articles WHERE MATCH（title，body）
AGAINST（'数据库比较'IN BOOLEAN MODE）;
+ ---- + --------------------- + -------------- ---------------------------- +
| id |标题| body |
+ ---- + --------------------- + ------------------ ------------------------ +
| 1 | PostgreSQL教程| DBMS代表DataBase ... |
| 5 | MySQL与YourSQL |在以下数据库比较中... |
+ ---- + --------------------- + ------------------ ------------------------ +
订单现在无关紧要：
mysql> SELECT * FROM articles WHERE MATCH（title，body）
AGAINST（'比较数据库'IN BOOLEAN MODE）;
+ ---- + --------------------- + -------------- ---------------------------- +
| id |标题| body |
+ ---- + --------------------- + ------------------ ------------------------ +
| 1 | PostgreSQL教程| DBMS代表DataBase ... |
| 5 | MySQL与YourSQL |在下面的数据库比较中... |
+ ---- + --------------------- + ------------------ ------------------------ +
如果我们想获得包含单词PostgreSQL或短语数据库比较的行，我们应该使用这个请求：
的MySQL> SELECT * FROM articles WHERE MATCH（title，body）
AGAINST（'PostgreSQL'数据库比较''布尔模式）;
+ ---- + --------------------- + -------------- ---------------------------- +
| id |标题| body |
+ ---- + --------------------- + ------------------ ------------------------ +
| 1 | PostgreSQL教程| DBMS代表DataBase ... |
| 5 | MySQL与YourSQL |在以下数据库比较中... |
+ ---- + --------------------- + ------------------ ------------------------ +
请确保您搜索的字词不在停用词表，被忽略。
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
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.
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.
As MySQL manual says:
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":
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:
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.
这篇关于PHP中使用字符串包含关键字的MySql全文搜索的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
