php - php中mysqli字符串中的多词匹配

Question

我有一个类似于以下内容的数据库:

ID   |   TEXT
1    | I like to eat cheetos.
2    | There is a snake in my boots
3    | to eat is to like food
4    | The sun stopped setting, Rlyah Cthullhu!
5    | Beat the heat with icecram!
6    | Treat yourself!
7    | Eat at Joes!

我有一个类似于以下内容的 mysqli 查询...

SELECT `ID` FROM table WHERE `ID` > 0 AND (`TEXT` REGEXP '/(like)(eat)/');

我也试过如下:

SELECT `ID` FROM table WHERE `ID` > 0 AND (`TEXT` REGEXP '/^(^like|^eat)/');
SELECT `ID` FROM table WHERE `ID` > 0 AND (`TEXT` REGEXP '(like)(eat)');
SELECT `ID` FROM table WHERE `ID` > 0 AND (`TEXT` REGEXP '/(?=like)(?=eat)/');

这些都不匹配任何字段(而且我不知道为什么，据我所知，它们应该有效)。出于绝望，我尝试了......

SELECT `ID` FROM table WHERE `ID` > 0 AND (`TEXT` REGEXP 'like|eat');

这当然是“或”而不是“和”匹配，它匹配了 1、3、5、6 和 7。

但我希望它匹配的是 1 和 3。

我做错了什么？

Answer 1

“或”更简洁:

REGEXP '[[:<:]](like|eat)[[:>:]]'

对于“和”:

    REGEXP '[[:<:]]like[[:>:]]'
AND REGEXP '[[:<:]]eat[[:>:]]'

但是 FULLTEXT 索引(也是“AND”)会快得多

MATCH (col) AGAINST ('+like +eat' IN BOOLEAN MODE)