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 ?
