如何优化MySQL查询以高效匹配单列中的多组逗号分隔值

Great question—handling comma-separated values (CSVs) in SQL can feel clunky, but we can refine your queries to be both efficient and clear. First, let's lock in your hard requirement: we must always exclude rows where status_column is NULL or an empty string—we’ll include that filter in every query below.

Key Background: FIND_IN_SET vs REGEXP #

Before diving into each condition, a quick performance note: MySQL's FIND_IN_SET() is purpose-built for comma-separated lists, so it’s almost always faster than REGEXP for these exact use cases. REGEXP shines for complex pattern matching, but for simple "does this value exist in the list?" checks, FIND_IN_SET() avoids the overhead of regex pattern parsing and backtracking.

Condition 1: Rows with 'NA' but NOT 'NON_NA' #

Your initial query was missing the exclusion of 'NON_NA'—here's the corrected, optimized version:

SELECT * 
FROM my_table 
WHERE status_column IS NOT NULL 
  AND status_column != ''
  AND FIND_IN_SET('NA', status_column) > 0
  AND FIND_IN_SET('NON_NA', status_column) = 0;

This is far more efficient than a regex equivalent, since FIND_IN_SET() directly targets the comma-separated values without pattern matching.

Condition 2: Rows with 'NON_NA' (any occurrence) #

Your original FIND_IN_SET() approach is already optimal here—just add the null/empty filter:

SELECT * 
FROM my_table 
WHERE status_column IS NOT NULL 
  AND status_column != ''
  AND FIND_IN_SET('NON_NA', status_column) > 0;

No need for regex here; this is the fastest way to check for the presence of 'NON_NA' in the list.

Condition 3: Rows with BOTH 'NA' and 'NON_NA' #

Your current dual FIND_IN_SET() checks are actually the best approach for this scenario. Let's polish it with the null/empty filter:

SELECT * 
FROM my_table 
WHERE status_column IS NOT NULL 
  AND status_column != ''
  AND FIND_IN_SET('NA', status_column) > 0
  AND FIND_IN_SET('NON_NA', status_column) > 0;

While you could write a regex to handle this (like status_column REGEXP '(^|,)NA(,|$).*(^|,)NON_NA(,|$)'), this regex is more complex, slower to execute, and harder to read. The dual FIND_IN_SET() calls are straightforward and perform better, especially with large datasets.

Long-Term Optimization: Normalize Your Schema #

If you have the flexibility to adjust your database design, the best optimization is to move away from comma-separated values entirely. Create a separate junction table (e.g., my_table_statuses) that links each row in my_table to individual status values. This:

• Makes queries faster (you can index the status column in the junction table)
• Eliminates messy CSV handling
• Enforces data integrity (no typos or invalid status values)

For example:

• my_table keeps its primary key (e.g., id)
• my_table_statuses has columns my_table_id (foreign key to my_table.id) and status (varchar with your allowed values: 'NA', 'NON_NA', 'PSG_REQUIRED')

Then your Condition 3 query becomes:

SELECT t.*
FROM my_table t
JOIN my_table_statuses s1 ON t.id = s1.my_table_id AND s1.status = 'NA'
JOIN my_table_statuses s2 ON t.id = s2.my_table_id AND s2.status = 'NON_NA'
WHERE t.status_column IS NOT NULL -- If you keep the old column temporarily
  AND t.status_column != '';

This is infinitely more scalable and maintainable than CSV columns.

内容的提问来源于stack exchange，提问作者19_Manjunath HT