求验证MySQL查询中CASE WHEN END语句格式的正则表达式

Got it, let's tackle this—you need a regex to validate those runtime-generated MySQL CASE WHEN statements, right? First, let's recap the structure you're dealing with: these are the searched CASE expressions (each WHEN checks a custom user-defined condition, not matching a value against a single field).

Below are regex patterns tailored to this, along with breakdowns and notes for edge cases:

Basic Regex (No Nested CASE Support) #

This works for simple, non-nested CASE statements like your examples, handling whitespace, mixed keyword casing, and multiple comma-separated CASE blocks:

^\s*CASE\s+(WHEN\s+.+?\s+THEN\s+.+?\s*)+\s*(ELSE\s+.+?\s*)?END\s*(?:,\s*CASE\s+(WHEN\s+.+?\s+THEN\s+.+?\s*)+\s*(ELSE\s+.+?\s*)?END\s*)*$

Use with the i (case-insensitive) and s (dot matches newlines) flags where your regex engine supports them—this lets it handle multi-line statements and keywords like case or When.

Breakdown of the Basic Pattern #

Let's walk through what each part does:

• ^\s*CASE\s*: Matches the start of the string, any leading whitespace, and the CASE keyword (case-insensitive thanks to the i flag)
• (WHEN\s+.+?\s+THEN\s+.+?\s*)+: Catches one or more WHEN ... THEN ... clauses. The .+? is a non-greedy match, so it stops at the next keyword instead of gobbling up everything. \s* handles any spaces/tabs/newlines between tokens.
• \s*(ELSE\s+.+?\s*)?: Makes the ELSE clause optional (the ? means it can be present or not), matching any value after ELSE
• END\s*: Matches the closing END keyword and any trailing whitespace
• (?:,\s*CASE\s+...END\s*)*: A non-capturing group that lets you match zero or more additional CASE ... END blocks separated by commas—perfect for your example where multiple CASE statements are chained.

Advanced Regex (Supports Nested CASE Statements) #

If your generated queries might have nested CASE expressions (like a CASE inside a THEN or ELSE value), you'll need a recursive regex. This works in engines that support recursion (like PCRE, Python's regex module, etc.):

^\s*CASE\s+(WHEN\s+(?:(?!WHEN|THEN|ELSE|END).|(?R))+\s+THEN\s+(?:(?!WHEN|THEN|ELSE|END).|(?R))+\s*)+\s*(ELSE\s+(?:(?!WHEN|THEN|ELSE|END).|(?R))+\s*)?END\s*(?:,\s*CASE\s+(WHEN\s+(?:(?!WHEN|THEN|ELSE|END).|(?R))+\s+THEN\s+(?:(?!WHEN|THEN|ELSE|END).|(?R))+\s*)+\s*(ELSE\s+(?:(?!WHEN|THEN|ELSE|END).|(?R))+\s*)?END\s*)*$

The (?R) here refers to the entire regex pattern, allowing it to match nested CASE structures correctly.

Key Notes #

• Whitespace Flexibility: Both patterns handle arbitrary whitespace (spaces, tabs, newlines) so your generated queries don't have to be perfectly formatted to pass validation.
• Quoted Values: If your user-defined values include string literals with quotes, the regex will work as long as the quotes are properly closed. That said, regex isn't perfect for parsing nested quotes—for that, you'd need a full SQL parser, but this is great for structure checks.
• Limitations: Regex can't validate that the actual conditions/values are valid MySQL syntax (like checking if a column exists or an expression is valid). For full correctness, consider using a dedicated SQL parsing library, but for ensuring your CASE WHEN structure is right, this regex is solid.

Quick Example Usage #

Here's how you might use the advanced pattern in Python with the regex module:

import regex

# Define the recursive regex pattern
case_pattern = r'^\s*CASE\s+(WHEN\s+(?:(?!WHEN|THEN|ELSE|END).|(?R))+\s+THEN\s+(?:(?!WHEN|THEN|ELSE|END).|(?R))+\s*)+\s*(ELSE\s+(?:(?!WHEN|THEN|ELSE|END).|(?R))+\s*)?END\s*(?:,\s*CASE\s+(WHEN\s+(?:(?!WHEN|THEN|ELSE|END).|(?R))+\s+THEN\s+(?:(?!WHEN|THEN|ELSE|END).|(?R))+\s*)+\s*(ELSE\s+(?:(?!WHEN|THEN|ELSE|END).|(?R))+\s*)?END\s*)*$'

# Test a valid query with nested CASE
test_query = "CASE WHEN age > 18 THEN CASE WHEN income > 50000 THEN 'high_earner' ELSE 'average' END ELSE 'minor' END, CASE WHEN score >= 90 THEN 'A' WHEN score >= 80 THEN 'B' ELSE 'C' END"

# Check if it matches
match = regex.match(case_pattern, test_query, flags=regex.IGNORECASE | regex.DOTALL)
if match:
    print("Valid CASE WHEN structure!")
else:
    print("Invalid structure—check your CASE statements.")

内容的提问来源于stack exchange，提问作者d.coder