Home > Database > Mysql Tutorial > body text

mysql全文搜索中文应用

WBOY
Release: 2016-06-07 17:53:08
Original
1114 people have browsed it

今天要介绍的就是mysql全文搜索的应用,全文检索的表的存储引擎类型必须为MyISAM,否则不能做全文搜索哦。

首先用下面表举例:

 代码如下 复制代码


01.CREATE TABLE articles (02.id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,03.title VARCHAR(200),04.body TEXT,05.FULLTEXT (title,body)06.) ENGINE=MyISAM DEFAULT CHARSET=utf8;

MySQL使用Match()和Against()执行全文搜索,举例如下:

 代码如下 复制代码

SELECT body FROM articles WHERE Match(body) Against('www.phpddt.com');

如上Match是正对指定的列进行搜索,Against('www.phpddt.com')是正对指定词进行搜索

另外:

使用查询扩展WITH QUERY EXPANSION,将会搜索出更多结果(mysql会将匹配的行中有用的词再次进行搜索,这样搜索出来的结果更多,但可能不是你想要的);

使用布尔文本搜索IN BOOLEAN MODE,类似我们平时使用搜索引擎时用到的的语法:逻辑与、逻辑或、逻辑非等.支持的布尔操作符如下:

全文布尔操作符:
--------------------------------------------------------------------------------------------------------------
|操作符                        |      说明                                                                                  |
--------------------------------------------------------------------------------------------------------------
|    +                       |包含,词必须存在                                                                            
|     -                      |排除,词必须不出现                                                                        
|     >                      |包含,而且增加等级值                                                                     
|     |     ()                      |把词组成子表达式(允许这些子表达式作为一个组被包含,排除,排列等)
|      ~                     |取消一个词的排序值
|     *                       |词尾的通配符
|     " "                     |定义一个短语(与单个词的列表不一样,它匹配整个短语以便包含或排除这个短语) 
----------------------------------------------------------------------------------------------------------------

使用方法如下:

 代码如下 复制代码

SELECT * FROM articles WHERE MATCH (title,body)

     AGAINST ('+apple -banana' IN BOOLEAN MODE);

   + 表示AND,即必须包含。- 表示NOT,即不包含。

 

 代码如下 复制代码

SELECT * FROM articles WHERE MATCH (title,body)

     AGAINST ('apple banana' IN BOOLEAN MODE);

   apple和banana之间是空格,空格表示OR,即至少包含apple、banana中的一个。

 

 代码如下 复制代码

SELECT * FROM articles WHERE MATCH (title,body)

     AGAINST ('+apple banana' IN BOOLEAN MODE);

   必须包含apple,但是如果同时也包含banana则会获得更高的权重。

 

 代码如下 复制代码

SELECT * FROM articles WHERE MATCH (title,body)

     AGAINST ('+apple ~banana' IN BOOLEAN MODE);

   ~ 是我们熟悉的异或运算符。返回的记录必须包含apple,但是如果同时也包含banana会降低权重。但是它没有 +apple -banana 严格,因为后者如果包含banana压根就不返回。

 

 代码如下 复制代码

SELECT * FROM articles WHERE MATCH (title,body)

 AGAINST ('+apple +(>banana

返回同时包含apple和banana或者同时包含apple和orange的记录。但是同时包含apple和banana的记录的权重高于同时包含apple和orange的记录。

linux中mysql中文全文搜索 .

 代码如下 复制代码

centos6
xampp1.7.7
mysql5.5

# /opt/lampp/bin/mysql -uroot -ppassword

mysql> SHOW VARIABLES LIKE 'plugin_dir';
插件路径:/opt/lampp/lib/mysql/plugin

# wget http://mysqlcft.googlecode.com/files/mysqlcft-1.0.0-x86_64-bin.tar.gz
# tar zxvf mysqlcft-1.0.0-x86_64-bin.tar.gz
# cp mysqlcft.so /opt/lampp/lib/mysql/plugin

# /opt/lampp/bin/mysql -uroot -ppassword
mysql> INSTALL PLUGIN mysqlcft SONAME 'mysqlcft.so';
mysql> SELECT * FROM mysql.plugin;
mysql> SHOW PLUGINS;


ALTER IGNORE TABLE articles ADD FULLTEXT INDEX cnFullIndex (title,body) WITH PARSER mysqlcft;

SELECT * FROM articles WHERE MATCH(title,body) AGAINST ('北京' IN BOOLEAN MODE

Related labels:
source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template
About us Disclaimer Sitemap
php.cn:Public welfare online PHP training,Help PHP learners grow quickly!