MySQL全文搜索

 全文索引（fulltext），适用于innodb引擎，字段类型为char，varchar，text。

列：

CREATE TABLE articles (
id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
title VARCHAR(200),
body TEXT,
FULLTEXT (title,body) );

 

三种类型全文搜索：

1.自然语言（natural language）（默认）（默认不区分大小写，要区分大小写，对索引列使用二进制排序规则）

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

 

2.布尔（Boolean）

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

+ 代表 AND  - 代表 NOT   [ 无操作员 ]暗示 OR

InnoDB全文搜索不支持使用带有通配符（'+*'），加号和减号组合（'+-'）或带正号和减号组合（'+-apple'）的前导加号。这些无效查询返回语法错误。

InnoDB全文搜索不支持在单个搜索词上使用多个运算符，如下例所示：'++apple'。

InnoDB全文搜索不支持@在布尔全文搜索中使用该符号。该@符号保留供@distance 邻近搜索运算符使用。

 

 

 

3.查询扩展（query expansion）

搜索“ database ”的用户 可能实际上意味着 “ MySQL ”，“ Oracle ”，“ DB2 ”和“ RDBMS ”都是应该匹配“ 数据库 ”的短语 也应该返回。

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

 

 

全文停用词（如果一个词被认为是太普通或者太没价值，那么它将会被搜索索引和搜索查询忽略）

SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_DEFAULT_STOPWORD;

要为所有InnoDB表定义自己的停用词列表 ，请定义一个与表结构相同的 INNODB_FT_DEFAULT_STOPWORD表，使用停用词填充它，并 在创建全文索引之前将 innodb_ft_server_stopword_table 选项的值设置为表单中的值 。

CREATE TABLE my_stopwords(value VARCHAR(30));
INSERT INTO my_stopwords(value) VALUES ('Ishmael');
SET GLOBAL innodb_ft_server_stopword_table = 'test/my_stopwords';

默认情况下，长度小于3个字符或长度大于84个字符的单词不会出现在 InnoDB全文搜索索引中。

 

全文限制

1.分区表不支持全文搜索。

2.虽然支持在单个表中使用多个字符集，但FULLTEXT索引中的所有列都 必须使用相同的字符集和排序规则。

3.'％'字符不是全文搜索支持的通配符

4.对于InnoDB所有DML操作（INSERT， UPDATE， DELETE）与全文索引以事务处理涉及列提交时间。

5.全文搜索可以与大多数多字节字符集一起使用。Unicode， utf8可以使用字符集，但不能使用ucs2字符集

 

全文优化

OPTIMIZE TABLE在具有全文索引的表上 运行会重建全文索引，删除已删除的文档ID并在可能的情况下合并同一个单词的多个条目。
要优化全文索引，请启用 innodb_optimize_fulltext_only 并运行OPTIMIZE TABLE。