MySQL全文索引之布尔全文索引、查询扩展全文索引

1、布尔全文索引
MySQL可以使用IN BOOLEAN MODE修饰符执行布尔全文搜索 。使用此修饰符，某些字符在搜索字符串中的单词的开头或结尾处具有特殊含义。在以下查询中， +和-运算符分别表示必须存在或不存在单词才能进行匹配。因此，下面这个例子表示，检索包含单词 ''MySQL"但不包含"database"的所有行

mysql> select * from articles;
+----+-----------------------+------------------------------------------+
| id | title                 | body                                     |
+----+-----------------------+------------------------------------------+
|  1 | MySQL 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 ...      |
+----+-----------------------+------------------------------------------+
6 rows in set (0.00 sec)

mysql> select * from articles where match(title,body)
    -> against ('+mysql -database' in boolean mode);		//包含mysql不含database
+----+-----------------------+-------------------------------------+
| id | title                 | body                                |
+----+-----------------------+-------------------------------------+
|  6 | MySQL Security        | When configured properly, MySQL ... |
|  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. ... |
+----+-----------------------+-------------------------------------+
4 rows in set (0.00 sec)

MySQL使用了有时被称为隐含布尔逻辑的内容："+" 代表 AND，"-" 代表 NOT，无操作员为OR

全文布尔索引对于InnoDB表和MyISAM表而言有一些差别：

	InnoDB	MyISAM
是否相关性降低的顺序自动对行进行排序	是	否
最小字长全文参数	innodb_ft_min_token_size	ft_min_word_len
最大字长全文参数	innodb_ft_max_token_size	ft_max_word_len
停止字适用	是	是
是否支持单个搜索词上使用多个运算符	否	是
是否支持后导加减号	否	是

(1)InnoDB示例

mysql> show create table articles\G;
*************************** 1. row ***************************
       Table: articles
Create Table: CREATE TABLE `articles` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(200) DEFAULT NULL,
  `body` text,
  PRIMARY KEY (`id`),
  FULLTEXT KEY `title` (`title`,`body`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)


//自动按照相关性降低的顺序排序了

mysql> select * from articles where match(title,body)
    -> against('+mysql -database' in boolean mode);		
+----+-----------------------+-------------------------------------+
| id | title                 | body                                |
+----+-----------------------+-------------------------------------+
|  6 | MySQL Security        | When configured properly, MySQL ... |
|  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. ... |
+----+-----------------------+-------------------------------------+
4 rows in set (0.00 sec)

mysql> select *,match(title,body) against('+mysql -database') as relevance
    -> from articles where match(title,body)
    -> against('+mysql -database' in boolean mode);
+----+-----------------------+-------------------------------------+----------------------------+
| id | title                 | body                                | relevance                  |
+----+-----------------------+-------------------------------------+----------------------------+
|  6 | MySQL Security        |