Full text search - tag system problem

Question

I store tags in 255 varchar area, like this type;

",keyword1,keyword2,keyword3,key word 324,",keyword1234,

(keyword must start and end comma (commakeyword123comma))

-

I can find a keyword3 like this sql query;

select * from table where keyword like = '%,keyword3,%'

CREATE TABLE IF NOT EXISTS `table1` (
  `id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
  `tags` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  FULLTEXT KEY `tags` (`tags`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=2242 ;


INSERT INTO `table1` (`id`, `tags`) VALUES
(2222, ',keyword,'),
(2223, ',word is not big,'),
(2224, ',keyword3,'),
(2225, ',my keys,'),
(2226, ',hello,keyword3,thanks,'),
(2227, ',hello,thanks,keyword3,'),
(2228, ',keyword3,hello,thanks,'),
(2239, ',keyword3 but dont find,'),
(2240, ',dont find keyword3,'),
(2241, ',dont keyword3 find,');

(returns 2224,2226,2227,2228)

-

I must change this like command for FULL TEXT SEARCH.

select * from table1 where match (tags) against (",keyword3," in boolean mode)

sql command find 2239,2240,2241 but i dont want to find %keyword3% or keyword3

http://prntscr.com/137u9

ideas to find only ,keyword3, ?

,keyword3,

thank you

Answer 1

You can't use full text search alone for this - it searches only for words. Here are a few different alternatives you could use:

• You can use a full text search to quickly find candidate rows and then afterwords use a LIKE as you are already doing to filter out any false matches from the full text search.

• You can use FIND_IN_SET.

• You can normalize your database - store only one keyword per row.

  INSERT INTO `table1` (`id`, `tag`) VALUES
  (2222, 'keyword'),
  (2223, 'word is not big'),
  (2224, 'keyword3'),
  (2225, 'my keys'),
  (2226, 'hello'),   -- // 2226 has three rows with one keyword in each.
  (2226, 'keyword3'),
  (2226, 'thanks'),
  (2227, 'hello'),
  -- etc...
  

Of those I'd recommend normalizing your database if it is at all possible.

Answer 2

Is there a reason for storing all the tags in one row?

I would store each "tag" in a row then do as andreas suggests and do something like this:

SELECT * FROM table1 WHERE tag IN('keyword0', 'keyword1', 'etc.')

If you need, for some reason, to return all the tags in one row, you could store them individually and GROUP_CONCAT them together.

http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat

Answer 3

Hi,

First of all FULL TEXT is intended to be used for text searches. So there are limitations to what you can do with it. To do what you want you need to check the Boolean Mode specifications and see if the " operator can help you, but even with this your searches may not be 100% accurate. You would need to impose a word format for your keywords (preferably no word delimiters inside them like ).