MySQL InnoDB搜索索引的Stopwords

InnoDB搜索索引的Stopwords
InnoDB的默认禁止词列表相对较短，因为来自技术、文学和其他来源的文档经常使用短词作为关键字或重要短语。例如，你可能搜索“是”或“不是”，并期望得到一个合理的结果，而不是让所有这些词都被忽略
InnoDB默认的stopword列表可以通过查询INFORMATION_SCHEMA查看。INNODB_FT_DEFAULT_STOPWORD表。

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_DEFAULT_STOPWORD;
±------+
| value |
±------+
| a |
| about |
| an |
| are |
| as |
| at |
| be |
| by |
| com |
| de |
| en |
| for |
| from |
| how |
| i |
| in |
| is |
| it |
| la |
| of |
| on |
| or |
| that |
| the |
| this |
| to |
| was |
| what |
| when |
| where |
| who |
| will |
| with |
| und |
| the |
| www |
±------+
36 rows in set (0.01 sec)
为了对所有InnoDB表定义了一个自定义的stopword列表，那么使用与innodb_ft_default_stopword表相同的结构来定义你自定义的stopword表，然后向表中插入stopwords，并且在创建全文索引之前以db_name/table_name的形式设置innodb_ft_server_stopword_table选项的值。自定义的stopword表必须有一个varchar类型的value列。下面的例子演示了如何为innodb创建一个新的全局stopword表。

mysql> CREATE TABLE my_stopwords(value VARCHAR(30)) ENGINE = INNODB;
Query OK, 0 rows affected (0.21 sec)

mysql> INSERT INTO my_stopwords(value) VALUES (‘Ishmael’);
Query OK, 1 row affected (0.12 sec)

mysql> CREATE TABLE opening_lines (
-> id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
-> opening_line TEXT(500),
-> author VARCHAR(200),
-> title VARCHAR(200)
-> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.14 sec)

mysql> INSERT INTO opening_lines(opening_line,author,title) VALUES
-> (‘Call me Ishmael.’,‘Herman Melville’,‘Moby-Dick’),
-> (‘A screaming comes across the sky.’,‘Thomas Pynchon’,‘Gravity’s Rainbow’),
-> (‘I am an invisible man.’,‘Ralph Ellison’,‘Invisible Man’),
-> (‘Where now? Who now? When now?’,‘Samuel Beckett’,‘The Unnamable’),
-> (‘It was love at first sight.’,‘Joseph Heller’,‘Catch-22’),
-> (‘All this happened, more or less.’,‘Kurt Vonnegut’,‘Slaughterhouse-Five’),
-> (‘Mrs. Dalloway said she would buy the flowers herself.’,‘Virginia Woolf’,‘Mrs. Dalloway’),
-> (‘It was a pleasure to burn.’,‘Ray Bradbury’,‘Fahrenheit 451’);
Query OK, 8 rows affected (0.04 sec)
Records: 8 Duplicates: 0 Warnings: 0

mysql> SET GLOBAL innodb_ft_server_stopword_table = ‘mysql/my_stopwords’;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE FULLTEXT INDEX idx ON opening_lines(opening_line);
Query OK, 0 rows affected, 1 warning (0.91 sec)
Records: 0 Duplicates: 0 Warnings: 1
默认情况下，长度小于3个字符或大于84个字符的单词不会出现在InnoDB全文搜索索引中。最大和最小字长值可以通过innodb_ft_max_token_size和innodb_ft_min_token_size变量进行配置。这种默认行为不适用于ngram解析器插件。ngram令牌大小由
ngram_token_size选项定义。

通过查询INFORMATION_SCHEMA.INNODB_FT_INDEX_TABLE中的单词，验证指定的stopword (‘Ishmael’)没有出现。

mysql> SET GLOBAL innodb_ft_aux_table=‘mysql/opening_lines’;
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT word FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_TABLE;
±----------+
| word |
±----------+
| across |
| all |
| burn |
| buy |
| call |
| comes |
| dalloway |
| first |
| flowers |
| happened |
| herself |
| invisible |
| less |
| love |
| man |
| more |
| mrs |
| now |
| now |
| now |
| pleasure |
| said |
| screaming |
| she |
| sight |
| sky |
| the |
| the |
| this |
| was |
| was |
| when |
| where |
| who |
| would |
±----------+
35 rows in set (0.00 sec)
要在一个表一个表的基础上创建stopword列表，需要创建其他stopword表，并在创建全文索引之前使用innodb_ft_user_stopword_table选项指定想要使用的stopword表。