Mysql find keywords in text

3
votes

I have a Mysql InnoDB table with 10k keywords and I want to match them against several texts.

Some keywords have several words and I only want exact matchs.

Example: Keywords - brown fox, lazy cat, dog, fox, rabbit

Text - The quick brown fox jumps over the lazy dog

I want the query to return - brown fox, dog, fox

3
mysqlstringmatchkeyword
I have 10k keywords. I cannot go one by one. – Hugo Gameiro
The example you give is the oposite of what I need. The idea was to go something like full text SELECT * FROM TABLE WHERE MATCH(keywords) AGAINST('The quick brown fox jumps over the lazy dog'); the problem is that the table is InnoDB and doesn't work with full text and full text doesn't return only full match. It would also return partial matchs – Hugo Gameiro

3 Answers

4
votes
SELECT * FROM tenKTable 
WHERE 'The quick brown fox jumps over the lazy dog' LIKE CONCAT('%',keyword,'%')

Source: MySQL SELECT query string matching

1
votes

Here's one idea:

SELECT keyword 
FROM Keywords
 JOIN (SELECT 'The quick brown fox jumps over the lazy dog' as col) k
   on k.col like Concat('%',keywords.keyword,'%')

And the SQL Fiddle.

Good luck.

0
votes

The accepted answer is very good. An improvement would be

SELECT kw FROM keyword
WHERE ' text ' LIKE CONCAT('% ',kw,' %')

Remember to prepend and append space to the text. This way, you avoid partial matches altogether.