我有一个MySQL查询,它是由FULLTEXT
-索引组成的,因为我需要相关性排序。缺点是我需要像一样的模式匹配
SELECT * FROM table WHERE colum LIKE %word%
有什么方法可以在FULLTEXT
-搜索中实现这一点吗?
以下是如何使用MySQL全文搜索:
在这里,您不需要将较大的输入字符串分解为一个数组来与每个单词进行比较。
请参考下面的例子来写你自己的:
包含在双引号(")字符内的短语与匹配
只有按字面意思包含短语的行,就像键入的一样。
我想向您解释布尔全文搜索;但我建议你也使用查询扩展进行全文搜索。
让我们看一下示例表:
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)
当我们删除引号时,它将搜索包含单词"的行;数据库";或";"比较":
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";或短语";数据库比较";,我们应该使用这个请求:
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 ... |
+----+---------------------+------------------------------------------+
小提琴
请确保您正在搜索的单词不在被忽略的停止词列表中。
是,使用MATCH反对全文搜索,如
SELECT*FROM表名WHERE MATCH(列名)反对('value');
如果要从多个表中搜索,请提及用逗号分隔的表名