FULLTEXT 索引上的 MATCH AGAINST 不返回任何行 [英] MATCH AGAINST on FULLTEXT index returning no rows

问题描述

我有一个在线日记，其中有两个主要的 MyISAM 表:

I have an online diary, with the two main MyISAM tables being these:

**tbl_log_days**

Name                Type
ID                  int(11)
post_date           date
post_content        longtext
post_entry_date     datetime
post_cat_id         int(11)
post_update_date    datetime

**tbl_log_cats**

Name                Type
fld_id              int(11)
fld_cat             varchar(255)

第一家店铺日详情，第二家店铺类别.

The first stores day details, the 2nd stores categories.

根据这篇文章的建议，我一直在尝试使用 MATCH AGAINST 全文搜索:

I have been experimenting with MATCH AGAINST full text searching following advice from this post:

使用全文索引的高级文本搜索http://www.hackingwithphp.com/9/3/18/advanced-text-searching-using-full-text-indexes

Advanced text searching using full-text indexes http://www.hackingwithphp.com/9/3/18/advanced-text-searching-using-full-text-indexes

在tbl_log_days"表上有一个索引:

There is an index on the "tbl_log_days" table:

Keyname: post_content
Type: FULLTEXT
Unique: No
Packed: No
Column: post_content
Cardinality: 2

当我运行此搜索时:

    SELECT d.ID
         , d.post_date 
         , d.post_content
         , d.post_cat_id
         , d.post_label
         , c.fld_cat
      FROM tbl_log_days d
         , tbl_log_cats c
     WHERE d.post_cat_id = c.fld_id
       AND post_content LIKE '%saying%'
       AND c.fld_id = 101
       AND YEAR(post_date) = 2003
  ORDER BY post_date;

它返回 5 行，post_content"值包含完整的单词saying".

It returns 5 rows, and the "post_content" value contains the full word "saying".

当我运行此 SQL 时:

When I run this SQL:

    SELECT d.ID
         , d.post_date 
         , d.post_content
         , d.post_cat_id
         , d.post_label
         , c.fld_cat
      FROM tbl_log_days d
         , tbl_log_cats c
     WHERE d.post_cat_id = c.fld_id
       AND (MATCH(post_content) AGAINST ('saying') AND c.fld_id = 101 AND YEAR(post_date) = 2003)
  ORDER BY post_date;

它不返回任何行.

这也不会返回任何内容:

This also returns nothing:

    SELECT d.ID
         , d.post_date 
         , d.post_content
         , d.post_cat_id
         , d.post_label
         , c.fld_cat
      FROM tbl_log_days d
         , tbl_log_cats c
     WHERE d.post_cat_id = c.fld_id
       AND MATCH(post_content) AGAINST ('saying')
       AND c.fld_id = 101
       AND YEAR(post_date) = 2003
  ORDER BY post_date;

这也是如此 - 例如删除连接到类别表的可能问题:

And so does this - e.g. removing possible issues with the join to the category table:

    SELECT d.ID
         , d.post_date 
         , d.post_content
         , d.post_cat_id
         , d.post_label
      FROM tbl_log_days d
     WHERE MATCH(post_content) AGAINST ('saying');

索引是否有问题 - 我会想如果 LIKE '%saying%' 返回 5 行，那么 MATCH AGAINST 也应该这样做?

Could there be something wrong with the index - I would have thought if a LIKE '%saying%' returns 5 rows, then a MATCH AGAINST should do as well?

推荐答案

让我们直说:

post_content LIKE '%saying%'

与

MATCH(post_content) AGAINST ('saying')

1. like 运算符执行简单的模式匹配.如果您在帖子内容中有文字谚语"，喜欢将返回它.默认自然语言模式下的全文搜索将不会返回此记录，因为says 与says 不同.

1. The like operator performs simple patter matching. If you have the text 'sayings' in the post content, like will return it. Fulltext search in its default natural language mode will not return this record, since saying is not the same word as sayings.

like 操作符将返回所有匹配该模式的记录，不管总数中有多少记录匹配人口.默认自然语言模式下的全文搜索会将超过 50% 的总记录中存在的任何值视为噪声，并且不会返回任何仅符合该条件的记录.

like operator will return all records matching the pattern, regardless how many records out of the total matches the population. Fulltext search in its default natural language mode will consider any values present in over 50% of the total records as noise and will not return any records matching that criterion only.

有一个适用于全文搜索的最小字长属性.如果您的最小单词长度比saying"的长度长，那么 mysql 将简单地忽略此标准.like 操作符不关心这些事情.

There is a minimum word length property applied to the fulltext searches. If your minimum word length is longer than the length of 'saying', then mysql will simply disregard this criterion. like operator does not care about such things.

Mysql 有关于全文搜索，其中描述了上述所有内容以及更多内容.

Mysql has a very detailed documentation on fulltext search, where all the above and a lot more are described.

这篇关于FULLTEXT 索引上的 MATCH AGAINST 不返回任何行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！