mysql全文索引: why result of match against is not as good as result of like?

Question

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

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

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

4. >上面2个select的解释。
   
mysql> explain select name from play_movie where match(name) against ('约会规则');<br/>
+----+-------------+------------+----------+---------------+--------+---------+------+------+-------------+<br/>
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |<br/>
+----+-------------+------------+----------+---------------+--------+---------+------+------+-------------+<br/>
| 1 | SIMPLE | play_movie | fulltext | name_2 | name_2 | 0 | | 1 | Using where |<br/>
+----+-------------+------------+----------+---------------+--------+---------+------+------+-------------+<br/>
<br/>
mysql> explain select name from play_movie where name like '%约会规则%';<br/>
+----+-------------+------------+-------+---------------+------+---------+------+-------+--------------------------+<br/>
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |<br/>
+----+-------------+------------+-------+---------------+------+---------+------+-------+--------------------------+<br/>
| 1 | SIMPLE | play_movie | index | NULL | name | 767 | NULL | 42782 | Using where; Using index |<br/>
+----+-------------+------------+-------+---------------+------+---------+------+-------+--------------------------+<br/>

5. >喜欢使用 0.04 秒来查找结果，匹配使用 0.00 秒来查找结果，但喜欢找到比匹配更好的结果。
6. >对战结果，结果为约会规则第一季，落选结果为约会规则第六季，关键字为约会规则 。似乎全文索引没有将约会规则第六季拆分为约会规则和第六季。
   我如何配置 mysql 或全文索引来解决这个问题？以上文字为中文，默认字符集为utf8。

Answer 1

在您的匹配 查询中，您正在寻找包含确切 单词约会规则的行。但是，在 like 查询中，您要查找包含单词约会规则 anywhere 的行，包括其他单词。您可以在 boolean mode 中使用全文搜索，它允许您使用星号作为通配符:

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