正则表达式首尾撇号未被视为单词部分，MySQL下如何解决？

Got it, let's work through this problem. The root issue here is that MySQL's default word boundaries ([[:<:]] and [[:>:]]) only treat letters, numbers, and underscores as "word characters." Apostrophes get excluded, which breaks matches when they sit at the start or end of your target string—even though they're valid parts of words in your language.

Breaking Down Your Test Cases #

Looking at your examples:

• cat''s works because its first and last characters are letters, so the default boundaries recognize valid word starts/ends.
• 'cat''s, cat'', and -cat''s fail because their first/last characters are apostrophes or hyphens (non-word characters to MySQL's default rules), so the boundaries don't trigger correctly.

The Solution: Custom Word Boundaries #

Since MySQL uses POSIX regex (which doesn't support Perl-style zero-width assertions), we'll build custom boundary checks that include apostrophes as part of our word set. Here's how:

1. Define our valid word characters: Include letters, numbers, and apostrophes (expand this if your language uses accented letters like ò from your air a' bhòrd example).
2. Replace default boundaries:
   • Start boundary: (^|[^a-zA-Z0-9']) — matches either the start of the string, or a character that's NOT in our valid word set.
   • End boundary: ([^a-zA-Z0-9']|$) — matches either the end of the string, or a character that's NOT in our valid word set.

Updated Test Queries #

Let's rewrite your failing cases with this approach (note: using escaped apostrophes \' for clarity; you can also use double apostrophes as in your original examples):

-- Test 1: Leading apostrophe (previously returned 0)
SELECT ('\'cat\'s' REGEXP CONCAT('(^|[^a-zA-Z0-9\'])', '\'cat\'s', '([^a-zA-Z0-9\']|$)')); -- Returns 1

-- Test 2: Trailing apostrophe (previously returned 0)
SELECT ('cat\'' REGEXP CONCAT('(^|[^a-zA-Z0-9\'])', 'cat\'', '([^a-zA-Z0-9\']|$)')); -- Returns 1

-- Test 3: Original working case still functions correctly
SELECT ('cat\'s' REGEXP CONCAT('(^|[^a-zA-Z0-9\'])', 'cat\'s', '([^a-zA-Z0-9\']|$)')); -- Returns 1

Handling Accented Characters #

If your target language uses accented letters (like ò in bhòrd), expand the character set to include them. For example:

-- Expanded character set for accented Latin letters
CONCAT(
  '(^|[^a-zA-Z0-9\'àáâãäåæçèéêëìíîïñòóôõöøùúûüýÿ])',
  search_string,
  '([^a-zA-Z0-9\'àáâãäåæçèéêëìíîïñòóôõöøùúûüýÿ]|$)'
)

Case Sensitivity Note #

MySQL's REGEXP is case-insensitive by default (based on your database collation). Use REGEXP BINARY if you need case-sensitive matching.

内容的提问来源于stack exchange，提问作者akerbeltz