regex - Logical AND operator in mySql REGEXP?

Question

I use MySql REGEXP:

SELECT * FROM myTable
WHERE title REGEXP "dog|cat|mouse";

The dataset is small, so I am not concerned about performance. And I prefer this over LIKE notation, because I do not have to concatenate a bunch of "LIKE" statements.

However, the above notation uses a logical OR operator. Is there a logical AND operator, so that only rows containing all of the keywords are matched?

(I am using InnoDB so fulltext search not an option)

Answer 1

There's really no nice solution except concatenating ANDs:

SELECT * FROM myTable
WHERE title REGEXP "dog"
AND title REGEXP "cat"
AND title REGEXP "mouse"

The regular expression would otherwise look like this:

SELECT * FROM myTable
WHERE title REGEXP "(dog.*cat.*mouse)|(dog.*mouse.*cat)|(mouse.*dog.*cat)|(mouse.*cat.*dog)|(cat.*dog.*mouse)|(cat.*mouse.*dog)"