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 |

+----+-------------+------------+-------+---------------+------+---------+------+-------+--------------------------+

1. 比如用0.04秒查找结果，用0.00秒匹配查找结果，但喜欢查找比匹配更好的结果。
2. 匹配的结果，结果看起来像约会规则第一季，遗漏的结果看起来像约会规则第六季，关键字是约会规则。看起来，全文索引并没有将划分为约会规则和第六季。 如何配置mysql或全文索引来解决这个问题？以上单词为中文，默认字符集为utf8。

Answer 1

在match against查询中，您正在查找包含确切单词约会规则的行。但是，在like查询中，您要查找包含单词约会规则的任何地方的行，包括在其他单词中。您可以使用布尔模式中的全文搜索，它允许您使用星号作为通配符：

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