MySQL匹配()对()不会返回任何东西(MySQL match() against() won't return anything)

177 IT屋

First query returns nothing, second returns 0 and third returns zero against all records. I removed IN NATURAL LANGUAGE MODE but no change. Any reason why?

I've checked many examples e.g.:

DB)

SELECT
*
FROM person
WHERE
MATCH(`name`, `middlename`, `surname`) AGAINST ('John' IN NATURAL LANGUAGE MODE);


SELECT
COUNT(*)
FROM person
WHERE
MATCH(`name`, `middlename`, `surname`) AGAINST ('John' IN NATURAL LANGUAGE MODE);


SELECT
id,
MATCH(`name`, `middlename`, `surname`) AGAINST ('John' IN NATURAL LANGUAGE MODE) AS score
FROM person
ORDER BY score DESC;


DROP TABLE IF EXISTS `person`;
CREATE TABLE `person` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
  `middlename` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
  `surname` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
  `code` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  FULLTEXT(`name`,`middlename`,`surname`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


INSERT INTO `person` (`name`, `middlename`, `surname`, `code`) VALUES
('John', 'Joseph', 'Travolta', 'JJT'),
('John', '', 'Lenon', 'JL'),
('John', '', 'Wayne', 'JW'),
('John', 'Paul', 'John', 'JPJ'),
('Robert', '', 'DeNiro', 'RD'),
('Elton', '', 'John', 'EJ'),
('Abi', 'John John', '', 'AJ'),
('Johny', '', '', 'J'),
('John', 'John', 'John', 'JJJ');
解决方案

This result is documented. You're using MyISAM table and your keyword "John" is present in at least 50% of the rows and so is a stopword, see manual, Natural Language Full-Text Searches

MyISAM Limitation For very small tables, word distribution does not adequately reflect their semantic value, and this model may sometimes produce bizarre results for search indexes on MyISAM tables. For example, although the word “MySQL” is present in every row of the articles table shown earlier, a search for the word in a MyISAM search index produces no results:

mysql> SELECT * FROM articles
WHERE MATCH (title,body)
AGAINST ('MySQL' IN NATURAL LANGUAGE MODE); Empty set (0.00 sec)

The search result is empty because the word “MySQL” is present in at
least 50% of the rows, and so is effectively treated as a stopword. This filtering technique is more suitable for large data sets, where you might not want the result set to return every second row from a 1GB table, than for small data sets where it might cause poor results for popular terms.

You could use the InnoDB Storage Engine to get around this feature:

The 50% threshold can surprise you when you first try full-text searching to see how it works, and makes InnoDB tables more suited to experimentation with full-text searches.

Another possibility is to use Boolean Full-Text Searches:

They do not use the 50% threshold that applies to MyISAM search indexes.

第一个查询不返回任何内容,第二个返回0,第三个返回所有记录的零。我删除了在自然语言模式但没有变化。任何理由为什么?



我已经检查了很多例子,例如:



< p> DB)

  SELECT 
*
FROM person
WHERE
MATCH (`name`,`middlename`,`surname`)AGAINST('John'in natural language version);


SELECT
COUNT(*)
FROM person
WHERE
MATCH(`name`,`middlename`,`surname`)反对('约翰'在自然语言模式下);



id,
MATCH(`name`,`middlename`,`surname`)AGAINST('John'IN NATURAL LANGUAGE MODE)AS分数
来自人
ORDER BY得分DESC;


删除表如果存在`person`;
CREATE TABLE`person`(
`id` int(11)NOT NULL AUTO_INCREMENT,
`name` varchar(45)COLLATE utf8_unicode_ci DEFAULT NULL,
`middlename` varchar 45)COLLATE utf8_unicode_ci DEFAULT NULL,
`surname` varchar(45)COLLATE utf8_unicode_ci DEFAULT NULL,
`code` varchar(45)COLLATE utf8_unicode_ci DEFAULT NULL,
PRIMARY KEY(`id`) ,
FULLTEXT(`name`,`middlename`,`surname`)
)ENGINE = MyISAM DEFAULT CHARSET = utf8 COLLATE = utf8_unicode_ci;


插入`person`(`name`,`middlename`,`surname`,`code`)VALUES
('John','Joseph','Travolta ','JJT'),
('John','','Lenon','JL'),
('John','','Wayne','JW'),
('John','Paul','John','JPJ'),
('Robert','','DeNiro','RD'),
('Elton' ,'','John','EJ'),
('Abi','John John','','AJ'),
('Johny','','',' 'J'),
('John','John','John','JJJ');

解决方案

记录此结果。您正在使用MyISAM表,并且您的关键字"John"在至少50%的行中存在,所以是停用词,请参阅 manual,Natural Language Full-Text Searches


MyISAM限制对于非常小的表格,单词分布不会
充分反映它们的语义值,并且此模型有时可能会在MyISAM表格上产生奇怪的搜索索引结果。对于
例子,尽管在前面显示的
articles表的每一行中都存在"MySQL"这个单词,但是在MyISAM搜索
索引中搜索该单词不会产生任何结果:



mysql> SELECT * FROM文章

匹配(标题,正文)

AGAINST('MySQL'IN NATURAL LANGUAGE MODE);空集(0.00秒)

搜索结果为空,因为单词"MySQL"存在于
至少50%的行中,因此被有效地视为一个停止词。
这种过滤技术更适用于大数据集,其中
可能不希望结果集从
1GB表中返回每隔一行,而不是小数据集,它可能会导致对于流行的术语,效果不佳



你可以使用InnoDB存储引擎来解决这个问题:




当您第一次尝试全文
搜索以查看它的工作方式时,50%的阈值可能会让您大吃一惊,并使InnoDB表更适合
使用全文搜索进行实验。



另一种可能是使用本文地址:IT屋 » MySQL匹配()对()不会返回任何东西