MySQL 全文检索

MySQL 全文检索支持

MyISAM

从MySQL 4.0以上 myisam引擎就支持了full text search 全文搜索。
那么怎么使用了，简单看看：
1.创建一个表，指定支持fulltext的列

  CREATE TABLE articles (    id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,    title VARCHAR(200),    body TEXT,    FULLTEXT (title,body)  )ENGINE=MyISAM DEFAULT CHARSET=utf8;

2.插入一些数据作为测试

  INSERT INTO articles (title,body) VALUES    ('MySQL Tutorial','DBMS stands for DataBase ...'),    ('How To Use MySQL Well','After you went through a ...'),    ('Optimizing MySQL','In this tutorial we will show ...'),    ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),    ('MySQL vs. YourSQL','In the following database comparison ...'),    ('MySQL Security','When configured properly, MySQL ...');

3.select查询fulltext的列

  SELECT * FROM articles    WHERE MATCH (title,body) AGAINST ('database');

查询结果：

  5        MySQL vs. YourSQL        In the following database comparison ...      1        MySQL Tutorial               DBMS stands for DataBase ...  

InnoDB

从MySQL 5.6.4以上， InnoDB引擎支持full text search 全文搜索。
使用方法同上，只是建表的时候，引擎设置为ENGINE=InnoDB即可：

CREATE TABLE t_fulltext (    id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,    title VARCHAR(200),    body TEXT,    FULLTEXT (title,body)) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

全文检索语法

  MATCH (col1,col2,...) AGAINST (expr [search_modifier])  search_modifier: { IN BOOLEAN MODE | WITH QUERY EXPANSION }  例如：SELECT * FROM tab_name WHERE MATCH ('列名1,列名2...列名n') AGAINST('词1 词2 词3 ... 词m');  即：MATCH 相当于要匹配的列，而 AGAINST 就是要找的内容。    这里的table需要是MyISAM类型的表，col1、col2 必须是char、varchar或text类型，在查询之前需要在 col1 和 col2 上分别建立全文索引(FULLTEXT索引)。

检索方式

 1、自然语言检索： IN NATURAL LANGUAGE MODE 2、布尔检索： IN BOOLEAN MODE      剔除一半匹配行以上都有的词，譬如说，每个行都有this这个字的话，那用this去查时，会找不到任何结果，这在记录条数特别多时很有用，      原因是数据库认为把所有行都找出来是没有意义的，这时，this几乎被当作是stopword(中断词)；但是若只有两行记录时，是啥鬼也查不出来的，      因为每个字都出现50%（或以上），要避免这种状况，请用IN BOOLEAN MODE。   ● IN BOOLEAN MODE的特色：       ·不剔除50%以上符合的row。       ·不自动以相关性反向排序。       ·可以对没有FULLTEXT index的字段进行搜寻，但会非常慢。       ·限制最长与最短的字符串。       ·套用Stopwords。   ● 搜索语法规则：     +   一定要有(不含有该关键词的数据条均被忽略)。      -   不可以有(排除指定关键词，含有该关键词的均被忽略)。      >   提高该条匹配数据的权重值。      <   降低该条匹配数据的权重值。     ~   将其相关性由正转负，表示拥有该字会降低相关性(但不像 - 将之排除)，只是排在较后面权重值降低。      *   万用字，不像其他语法放在前面，这个要接在字符串后面。      " " 用双引号将一段句子包起来表示要完全相符，不可拆字。     SELECT * FROM articles WHERE MATCH (title,content) AGAINST ('+apple -banana' IN BOOLEAN MODE);     + 表示AND，即必须包含。- 表示NOT，即必须不包含。即：返回记录必需包含 apple，且不能包含 banner。     SELECT * FROM articles WHERE MATCH (title,content) AGAINST ('apple banana' IN BOOLEAN MODE);     apple和banana之间是空格，空格表示OR。即：返回记录至少包含apple、banana中的一个。     SELECT * FROM articles WHERE MATCH (title,content) AGAINST ('+apple banana' IN BOOLEAN MODE);     返回记录必须包含apple，同时banana可包含也可不包含，若包含的话会获得更高的权重。     SELECT * FROM articles WHERE MATCH (title,content) AGAINST ('+apple ~banana' IN BOOLEAN MODE);     ~ 是我们熟悉的异或运算符。返回记录必须包含apple，若也包含了banana会降低权重。     但是它没有 +apple -banana 严格，因为后者如果包含banana压根就不返回。     SELECT * FROM articles WHERE MATCH (title,content) AGAINST ('+apple +(>banana <orange)' IN BOOLEAN MODE);     返回必须同时包含“apple banana”或者必须同时包含“apple orange”的记录。     若同时包含“apple banana”和“apple orange”的记录，则“apple banana”的权重高于“apple orange”的权重。3、查询扩展检索： WITH QUERY EXPANSION

简单示例

通过IN BOOLEAN MODE指定全文检索模式为布尔全文检索。MySQL还提供了一些类似我们平时使用搜索引擎时用到的的语法：逻辑与、逻辑或、逻辑非等。具体通过几个SQL语句例子来说明：

1、+ 表示AND，即必须包含。- 表示NOT，即不包含。

SELECT * FROM articles WHERE MATCH (title,body)     AGAINST ('+apple -banana' IN BOOLEAN MODE);

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

SELECT * FROM articles WHERE MATCH (title,body)     AGAINST ('apple banana' IN BOOLEAN MODE);

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

SELECT * FROM articles WHERE MATCH (title,body)     AGAINST ('+apple banana' IN BOOLEAN MODE);

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

SELECT * FROM articles WHERE MATCH (title,body)     AGAINST ('+apple ~banana' IN BOOLEAN MODE);

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

SELECT * FROM articles WHERE MATCH (title,body)     AGAINST ('+apple +(>banana <orange)' IN BOOLEAN MODE);

MySQL全文索引相关配置

MySQL全文检索时，默认检索长度为4，即关键词的长度大于等于4才能被捕获。

通过以下设置/etc/my.cnf 更改：

ft_min_word_len = 2

ft_min_word_len = 全文检索的最小许可字符(默认4，通过 SHOW VARIABLES LIKE ‘ft_min_word_len’ 可查看)，中文通常是两个字就是一个词，所以做中文的话需要修改这个值为2最好。

注意事项

 1、预设搜寻是不分大小写，若要分大小写，columne 的 character set要从utf8改成utf8_bin。 2、预设 MATCH...AGAINST 是以相关性排序，由高到低。 3、MATCH(title, content)里的字段必须和FULLTEXT(title, content)里的字段一模一样。      如果只要单查title或content一个字段，那得另外再建一个 FULLTEXT(title) 或 FULLTEXT(content)，也因为如此，MATCH()的字段一定不能跨table，但是另外两种搜寻方式好像可以。 4、MySQL不支持中文全文索引，原因很简单：与英文不同，中文的文字是连着一起写的，中间没有MySQL能找到分词的地方，截至目前MySQL5.6版本是如此，但是有变通的办法，就是将整句的中文分词，并按urlencode、区位码、base64、拼音等进行编码使之以“字母+数字”的方式存储于数据库中。