CODEWITHSUNDEEP
mysqlsqldatabasefull-text-search
Richard
Richard

Reputation: 462

MySQL full text search with delimiters

I have a MySQL table with a 'tags' field, which is using a FULLTEXT index. This field contains a list of comma separated values i.e 'books, toys, chocolate coloured sofa'.

SELECT * FROM Store WHERE MATCH(Store.tags) AGAINST ('"chocolate"' IN BOOLEAN MODE);

The (above) example query is matching records containing 'chocolate coloured sofa'. I would only like to retrieve records which contain 'chocolate' within the delimiters (in this case commas). In other words I would expect that the example record would fail the match.

I have looked at the MySQL manual including MySQL Fine-Tuning MySQL Full-Text Search but I cannot see any way this could be achieved.

Any pointers would be appreciated.

Upvotes: 0

Views: 3054

Answers (2)

corretge
corretge

Reputation: 1759

If the last tag has comma, you can search adding the comma:

SELECT * FROM Store WHERE MATCH(Store.tags) AGAINST ('"chocolate,"' IN BOOLEAN MODE);

But be careful with tags less than 4 characters, its aren't matched... well, you can configure it in my.ini

[mysqld]
ft_min_word_len=3

Upvotes: 1

sanmai
sanmai

Reputation: 30901

You have to normalize your table, so each tag will be saved in its own row.

Else you'll have to patch and rebuild MySQL to add the space character to the set of word characters. That link you provided has all instructions.

Upvotes: 0

Related Questions