MySQL REGEXP matches a boundary word only but excl

问题:

Following this question and answer, I still have a bit trouble in the result I get,

For instance,

REGEXP '>[^<]*lau[[:>:]]' will match 'lau' only but not 'laurence' which is correct.

REGEXP '>[^<]*men[[:>:]]' will match 'empowerment' and 'women' which are not what I am after.

REGEXP '( |>|$)home( |<|$)' will match 'home' only but not 'home!' which it should.

How can match these correctly?

For instance,

Home - match
HOME - match
welcome home! - match
home is far! - match
hometown - not match
xxhomexx - not match
<a href="home.html">Hello</a> - not match
<p class="home">a box</p> - not match

It should not match any occurrence in html tags. home-town - not match

回答1:

how about REGEXP '>[^<]*[[:<:]]home[[:>:]]' ?

Besides, I am really interested in the performance of your solution... Why don't you have another column in your table, which contains the text having the html tags stripped. You are then able to make good use of the full text search, instead of having a complex regexp.