mysql全文索引：为什么匹配结果不如结果那样好？

Question

1. 我已经创建了mysql的索引和全文索引，后端存储引擎是MyISAM。
   mysql> show index from play_movie;
+------------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+------------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| play_movie | 0 | PRIMARY | 1 | id | A | 42782 | NULL | NULL | | BTREE | |
| play_movie | 0 | name | 1 | name | A | 42782 | NULL | NULL | | BTREE | |
| play_movie | 1 | play_movie_ec9d726c | 1 | describe | A | 1944 | 333 | NULL | | BTREE | |
| play_movie | 1 | name_2 | 1 | name | NULL | 42782 | NULL | NULL | | FULLTEXT | |
+------------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

2. 匹配的结果。
   mysql> select name from play_movie where match(name) against ('约会规则'); +------------------------+
| name |
+------------------------+
| 约会规则 第二季 |
| 约会规则 第一季 |
+------------------------+
2 rows in set (0.00 sec)

3. 喜欢的结果。
   mysql> select name from play_movie where name like '%约会规则%';
+------------------------------------+
| name |
+------------------------------------+
| 恋爱法则/约会规则第四季 |
| 约会规则 第一季 |
| 约会规则 第二季 |
| 约会规则第三季 |
| 约会规则第六季 |
+------------------------------------+
5 rows in set (0.04 sec)

4. 解释了上面的2选择。
   mysql> explain select name from play_movie where match(name) against ('约会规则');
+----+-------------+------------+----------+---------------+--------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+----------+---------------+--------+---------+------+------+-------------+
| 1 | SIMPLE | play_movie | fulltext | name_2 | name_2 | 0 | | 1 | Using where |
+----+-------------+------------+----------+---------------+--------+---------+------+------+-------------+

mysql> explain select name from play_movie where name like '%约会规则%';
+----+-------------+------------+-------+---------------+------+---------+------+-------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+-------+---------------+------+---------+------+-------+--------------------------+
| 1 | SIMPLE | play_movie | index | NULL | name | 767 | NULL | 42782 | Using where; Using index |
+----+-------------+------------+-------+---------------+------+---------+------+-------+--------------------------+

5. 喜欢用0.04秒，找到的结果，针对使用0.00秒的比赛找到的结果，但像找到比对匹配较好的效果。
6. 对比赛的结果，结果看起来像约会规则第一季，错过的结果看起来像约会规则第六季，而关键字是约会规则。好像全文索引没有分割成约会规则第六季变成约会规则和第六季。
   如何配置mysql或全文索引来解决这个问题？上面的单词是中文，默认字符集是utf8。

Answer 1

在你match against查询您正在寻找包含确切字行约会规则。然而，在like查询中，您正在查找包含任何位置的约会规则的行，包括其他字。你可以使用boolean mode的全文搜索，它允许您使用星号作为通配符：

SELECT name FROM play_movie WHERE MATCH(name) 
AGAINST ('约会规则*' IN BOOLEAN MODE);