I tried matching keywords with REGEXP in MySQL as following:
-- Match "fitt*", the asterisk "*" is expected to be matched as-is
> select 'aaaa fitt* bbb' regexp '[[:<:]]fitt\*[[:>:]]'; -- return 1, ok
> select 'aaaa fitttttt* bbb' regexp '[[:<:]]fitt\*[[:>:]]'; -- return 1 as well, but should return 0
> select 'aaaa fitt* bbb' regexp '[[:<:]]fitt\\*[[:>:]]'; -- return 0, failed
How to escape the asterisk (*) in order to exactly match the character *?
\\* is the correct way to match the asterisk. But [[:>:]] won't match after it, because that only matches between a word character and a non-word character, and * is not a word character. Instead, you need to match a non-word character there explicitly. You also need an alternative for the end of line, since that's the other type of word boundary.
> select 'aaaa fitt* bbb' regexp '[[:<:]]fitt\\*([^[:alnum:]]|$)'; -- returns 1
> select 'aaaa fitttttt* bbb' regexp '[[:<:]]fitt\\*([^[:alnum:]]|$)'; -- returns 0
Another way to match the asterisk explicitly is by putting it in a character class.
> select 'aaaa fitt* bbb' regexp '[[:<:]]fitt[*]([^[:alnum:]]|$)'; -- returns 1
> select 'aaaa fitttttt* bbb' regexp '[[:<:]]fitt[*]([^[:alnum:]]|$)'; -- returns 0
\\* means zero or more backslashes. Your test happens to work because it found zero backslashes, then a *, which is not an 'alnum'.
* in the regexp.SELECT 'a\b' REGEXP 'a.b', 'a\\b' REGEXP 'a.b'; --> 0, 1.select 'aaaa fitt\\* bbb' regexp '[[:<:]]fitt\\*([^[:alnum:]]|$)'; -> 0`There could be 3 problems:
Item 1: The answer to the title question is either of these:
\\* (in the regexp)
[*]
Item 2: \\\\* may be needed if you are coming from some client that first unescapes the backslash before feeding it to MySQL, which still needs a backslash. However, as written (without any client code), \\\\* is treated as zero or more backslashes.
Item 3: @Barmar's answer focused on why [[:>:]] is incorrect.
\\* is needed if you're writing the string literally in the query, since MySQL itself first unescapes the backslash.$sql = "SELECT 'aaaa fitt* bbb' regexp '[[:<:]]fitt\\\\*([^[:alnum:]]|$)'"; in PHP.
[[:>:]]. That only matches at a word boundary, but there's no word boundary between*and space because*isn't a word character.fitt*with REGEXP?