MySQL正则实现标签位置无关的多标签匹配查询

Hey, I’ve dealt with this exact problem when working with comma-separated tag columns in MySQL. Your current regex 'b.*a' only handles the case where b comes before a, and it also risks matching partial tags (like ba,c being incorrectly flagged as containing b and a). Here are two reliable approaches that work no matter the order of a and b:

Approach 1: RegEx with Boundary Checks (Covers Both Orders) #

To ensure we match full tags only and account for either order, use regex patterns that check for the start/end of the string or comma boundaries. You can either use two separate regex checks (cleaner) or a single combined pattern:

Separate RegEx Checks (Recommended for Readability) #

SELECT * FROM sample_table
WHERE tag REGEXP '(^|,)a(,|$)'
  AND tag REGEXP '(^|,)b(,|$)';

Combined Single RegEx Pattern #

If you prefer a one-liner regex, this covers both a before b and b before a:

SELECT * FROM sample_table
WHERE tag REGEXP '((^|,)a(,|$).*(^|,)b(,|$))|((^|,)b(,|$).*(^|,)a(,|$))';

The (^|,) and (,|$) parts ensure we're matching the full tag a or b—not a substring inside another tag (like ab in a tag like ab,c).

Approach 2: Use MySQL's FIND_IN_SET (Simpler & More Reliable) #

MySQL has a built-in function specifically designed for comma-separated strings, which avoids regex complexity entirely:

SELECT * FROM sample_table
WHERE FIND_IN_SET('a', tag) > 0
  AND FIND_IN_SET('b', tag) > 0;

FIND_IN_SET returns the position of the tag in the comma-separated list (greater than 0 if found, 0 if not). This method is straightforward, handles any tag order, and eliminates the risk of partial tag matches automatically.

Why Your Original RegEx Failed #

Your initial REGEXP 'b.*a' only checks for b followed by a, ignoring the reverse scenario. It also doesn't include boundary checks, which could lead to false positives with tags that contain a or b as substrings.

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