mysql不完全匹配,MySQL匹配（）对（）不会返回任何东西

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.