MySQL RegEx search each column

Question

I'm searching my MySQL Database with Regex like this:

$search looks like this WORD1|WORD2|WORD3

$regexp = "REGEXP '[[:<:]][[:alpha:]]*($search)[[:alpha:]]*[[:>:]]'";

SELECT * FROM
    zitate
WHERE
    UPPER(`field1`) $regexp OR UPPER(`field2`) $regexp OR UPPER(`field3`)

Now I'm getting, as expected, every entry where $search matches field1 or field2 or field3

But I only want to recieve the entries where WORD1, WORD2 and WORD3 match within each column, not within the whole table.

I know this is because of the OR in my query but is there something better than writing something like this:

WHERE
    UPPER(`field1`) $regexp AND UPPER(`field2`) $regexp
OR
    UPPER(`field2`) $regexp AND UPPER(`field3`) $regexp
OR
    UPPER(`field1`) $regexp AND UPPER(`field3`) $regexp
OR
    UPPER(`field1`) $regexp AND UPPER(`field2`) $regexp AND UPPER(`field3`) $regexp

Or should I use this AND, OR query?

Thanks for any tips and answers!

Answer 1

Idea: in MySQL logical values are actually maps to 0 and 1, so instead of ORing them, you could simply add them like:

WHERE 1 < UPPER(`field1`) $regexp
        + UPPER(`field2`) $regexp
        + UPPER(`field3`) $regexp

Alternatively, you should learn about MySQL's full text indexing/searching features, or find some other solution, like Fulltext Search with InnoDB .