mysql regexp 特殊字符,mysql中的regexp-查找包含所有关键字的字符串（AND运算符）...

I am stuck for 3 hours with i guess simple thing but been searching internet and didnt find the answer.

I have 1, 2, 3, 4 or more keywords and a string to search for keywords in it. I am looking for regexp expression that maches ALL the keywords. So for example:

string = "this car is red and has big wheels"

keywords: car wheels

return: true

keywords: wheels car

return: true

keywords: car is red

return: true

keywords: this is big

return: true

keywords: car is red small

return: false (there is no 'small' word in string)

I use such a mysql query:

SELECT name, desc FROM table WHERE CONCAT(name, desc) REGEXP ($keyword1)($keyword2)

But it returns empty string. What should be correct regexp syntax?

解决方案

The easiest solution is to move the AND out of regexp and into SQL:

SELECT name, desc

FROM table

WHERE CONCAT(name, desc) REGEXP ($keyword1)

AND CONCAT(name, desc) REGEXP ($keyword2)

Otherwise, your regexp would have to include all N! possible orders in a long chain of OR statements, and that length grows very, very fast (with 5 keywords, you'd need to construct 120 distinct orderings!)