Using OR in LIKE Query in MySQL to compare multiple fields

Question

I always thought that you could use OR in a LIKE statment to query things in MySQL. So, if I wanted to compare multiple fields in a row to 1 keyword or term:

SELECT * FROM MyTable WHERE Column1 OR Column2 LIKE '%keyword%'; 

and if I had an array of words to compare:

SELECT * FROM MyTable WHERE Column1 OR Column2 LIKE '%keyword1%' 
AND Column1 OR Column2 LIKE '%keyword2%';

I don't believe that syntax is correct, however. Is there an efficient method of writing this aside from something like:

SELECT * FROM MyTable WHERE Column1 LIKE '%keyword1%' OR Column2 LIKE 
'%keyword1%' AND Column1 LIKE '%keyword2%' OR Column2 LIKE '%keyword2%';

Am I going about this correctly?

Answer 1

Use this::

SELECT * FROM MyTable WHERE (Column1 LIKE '%keyword1%' OR Column2 LIKE 
'%keyword1%') AND (Column1 LIKE '%keyword2%' OR Column2 LIKE '%keyword2%');

Answer 2

The closest to the syntax you are desiring is:

SELECT * FROM MyTable
WHERE (CONCAT(Column1, Column2) LIKE '%keyword1%')
AND (CONCAT(Column1, Column2) LIKE '%keyword2%')

Note: that the "%" at the start of your search string precludes the use of indexes. If there are any large number of records to search, it would be best to rethink the implementation.

If you cannot guarantee that each column is not NULL, then use CONCAT_WS instead:

SELECT * FROM MyTable
WHERE (CONCAT_WS("-", Column1, Column2) LIKE '%keyword1%')
AND (CONCAT_WS("-", Column1, Column2) LIKE '%keyword2%')

This CONCAT_WS solution also has the possible benefit of assuring that matches of your "keyword" where in only in Column1 OR Column2, if you select a separator character that is never present in your keywords.