About Mysql Fulltext Search

2018-06-03 22:41:44

I have a table and some rows, i want do fulltext search but fulltext col without space.

For Example:

My MySQL Table:

CREATE TABLE IF NOT EXISTS `members` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `username` varchar(255) NOT NULL DEFAULT '',
  `fulltextcol` varchar(10) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  FULLTEXT KEY `fulltextcol` (`fulltextcol`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=5 ;

Inserts:

INSERT INTO `members` (`id`, `username`, `fulltextcol`) VALUES
(1, 'username1', 'YYYYYNYNYN'),
(2, 'username2', 'NNNNYNYNYN'),
(3, 'username3', 'YYNNYNYYYY'),
(4, 'username4', 'YNYNYNYNYN');

And now my mysql command:

SELECT *,MATCH(fulltextcol) AGAINST('YNYNYNYNYN') as Relevance 
FROM members
WHERE MATCH(fulltextcol) AGAINST('YNYNYNYNYN' IN BOOLEAN MODE)
HAVING Relevance > 0.2
ORDER BY Relevance DESC

Yes everything is OK, bu this query is select only exact match (so select only 4th row) , but i want it must select all of them with scores/relevances.

For example first row have "YYYYYNYNYN" and it's not exact match but i want this query select it with low relevance, if 4th row's relevance is 1.18, 1st row's relevance can be 0.32.

I hope I could explain my problem.

How can i do it ?

链接地址: http://www.djcxy.com/p/13110.html

上一篇: 使用部分词汇匹配和相关度得分进行高效搜索（FULLTEXT）

下一篇: 关于Mysql全文搜索