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

You can add several conditions with AND between them:

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

Maybe REGEXP is not necessary here and you may use INSTR instead (regular are usually slower):

SELECT * FROM myTable
WHERE INSTR(title, "dog") AND INSTR(title, "cat") AND INSTR(title, "mouse");

Answer 2

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)"

Answer 3

AND operation may be only accessible by the mysql-AND:

WHERE title REGEXP 'dog' AND title REGEXP 'cat' AND title REGEXP 'mouse'

this will only show those entries where all the keywords are in the title field. like

title = "this is about mouse, cat and dog"

Answer 4

You can use full text search in boolean mode:

SELECT * FROM table WHERE MATCH(colmun_name) AGAINST('+dogs +cat' IN BOOLEAN MODE);

You must index your table first:

ALTER TABLE table ADD FULLTEXT(column_name);

Answer 5

If your title contains all the search terms, for example:

Anything mouse anything cat anything dog anything

Then you will have all the search terms in a three times repeated title in any order (for example dog, cat and mouse).

Anything mouse anything cat anything DOG anything

Anything mouse anything CAT anything dog anything

Anything MOUSE anything cat anything dog anything

So you can do this without concatenating ANDs with:

SELECT * FROM myTable WHERE REPEAT(title,3) RLIKE '.*dog.*cat.*mouse.*';