mysql全文检索,mysql使用全文索引,mysql5.7全文索引

1.查看歌曲表结构（主要是给name字段添加全文索引）(mysql5.7才支持全中文索引)

desc   music;
+---------+-------------+------+-----+---------+----------------+
| Field   | Type        | Null | Key | Default | Extra          |
+---------+-------------+------+-----+---------+----------------+
| id      | int(11)     | NO   | PRI | NULL    | auto_increment |
| name    | varchar(50) | NO   |     | NULL    |                |
| author  | varchar(30) | NO   |     | NULL    |                |
| addtime | int(10)     | NO   |     | NULL    |                |
+---------+-------------+------+-----+---------+----------------+

2.查看mysql的ft_min_word_len配置值是否正确

show variables like 'ft_min_word_len';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| ft_min_word_len | 4     |
+-----------------+-------+

中文索引应该设置为2,改变量无法通过set设置,只能通过my.ini和my.cnf设置,奇葩的是我的ubuntu设置无效,无解  

3.查看分词大小是否合适

show variables like 'ngram_token_size';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| ngram_token_size | 2     |
+------------------+-------+

我需要测试是2,如果需要调整,修改配置文件

4.为music的name字段创建全文索引,定义索引的名称为idxname

alter table music add fulltext index idxname(name) with parser ngram;

5.插入数据,并让大家看看数据

select *  from   music;
+----+-----------------+-----------+---------+
| id | name            | author    | addtime |
+----+-----------------+-----------+---------+
|  1 | 我是神仙        | 马蓉      |    1122 |
|  2 | 123我爱你       | 王五      |    2233 |
|  3 | 全部都是你      | 张三      |    4455 |
|  4 | 说散就散        | 麦小兜    |    6677 |
|  5 | 佛系少女        | 张帅中    |    8899 |
|  6 | 我是apple       | 马三      |    9900 |
+----+-----------------+-----------+---------+

6.看看生成的索引信息

SET GLOBAL innodb_ft_aux_table='test/music';(test是我的库名称,musci是我的表名)
 SELECT * FROM information_schema.INNODB_FT_INDEX_CACHE;
+--------+--------------+-------------+-----------+--------+----------+
| WORD   | FIRST_DOC_ID | LAST_DOC_ID | DOC_COUNT | DOC_ID | POSITION |
+--------+--------------+-------------+-----------+--------+----------+
| 12     |            3 |           3 |         1 |      3 |        0 |
| 23     |            3 |           3 |         1 |      3 |        1 |
| 3我    |            3 |           3 |         1 |      3 |        2 |
| le     |            7 |           7 |         1 |      7 |        9 |
| pl     |            7 |           7 |         1 |      7 |        8 |
| pp     |            7 |           7 |         1 |      7 |        7 |
| 佛系   |            6 |           6 |         1 |      6 |        0 |
| 全部   |            4 |           4 |         1 |      4 |        0 |
| 少女   |            6 |           6 |         1 |      6 |        6 |
| 就散   |            5 |           5 |         1 |      5 |        6 |
| 我是   |            2 |           8 |         3 |      2 |        0 |
| 我是   |            2 |           8 |         3 |      7 |        0 |
| 我是   |            2 |           8 |         3 |      8 |        0 |
| 我爱   |            3 |           3 |         1 |      3 |        3 |
| 散就   |            5 |           5 |         1 |      5 |        3 |
| 是你   |            4 |           4 |         1 |      4 |        9 |
| 是真   |            8 |           8 |         1 |      8 |        3 |
| 是神   |            2 |           2 |         1 |      2 |        3 |
| 爱你   |            3 |           8 |         2 |      3 |        6 |
| 爱你   |            3 |           8 |         2 |      8 |       12 |
| 的爱   |            8 |           8 |         1 |      8 |        9 |
| 真的   |            8 |           8 |         1 |      8 |        6 |
| 神仙   |            2 |           2 |         1 |      2 |        6 |
| 系少   |            6 |           6 |         1 |      6 |        3 |
| 说散   |            5 |           5 |         1 |      5 |        0 |
| 部都   |            4 |           4 |         1 |      4 |        3 |
| 都是   |            4 |           4 |         1 |      4 |        6 |
+--------+--------------+-------------+-----------+--------+----------+

分词长度是2，所以数据两个一组。包含ID等等信息

7.查询测试索引

//全部布尔模式查询,仅仅根据分词来查询(支持高级查询语法,可查询手册)
7.1.查询"我是神仙",歌曲;
(1).select name from music where match(name) against('我是神' in boolean mode);  ok 
我是神仙
(2).select name from music where match(name) against('我是' in boolean mode);   ok
我是神仙
我是apple
我是真的爱你   
(3).select name from music where match(name) against('我神' in boolean mode);   falsed //我神不是词
//全部自然语言查询(会将你查询的字符串分词在索引中全部匹配的值)
(1).select name from music where match(name) against ('我是' IN NATURAL LANGUAGE MODE);

删除索引

ALTER TABLE music  DROP INDEX idx_music

8.备注:

(1).创建完成索引,只有插入数据和更新数据才会触发创建全文索引,并不是设置了就会将以前未设置的数据生成全文索引,有点不喜欢...

(2).暂时没有想到同时生成多种分词的方法...