正则表达式捕获SQL CREATE TABLE语句最外层括号内内容的问题

Got it, I’ve run into this exact frustration before—trying to yank the column definitions from a CREATE TABLE statement, only to have my regex choke on the first nested parenthesis (like VARCHAR(50) or INT(11)). The problem with basic patterns like \(.*\) is they stop at the first closing bracket, leaving you with half the content. Here’s how to fix it:

Approach 1: Use a Regex with Balanced Groups (For Engines That Support It) #

If you’re working with JavaScript (ES2018+), Java, C#, or other regex engines that support recursive/balanced groups, this pattern will properly match the entire outer set of parentheses:

CREATE TABLE\s+\w+\s*\(([^()]|(\((?:[^()]|(?2))*\)))*\)

Breakdown of the Pattern: #

• CREATE TABLE\s+\w+\s*\(: Matches the start of the CREATE TABLE statement up to the opening parenthesis of column definitions.
• ([^()]|(\((?:[^()]|(?2))*\)))*: This recursive part matches either non-bracket characters, or a nested pair of parentheses (by referencing the second group recursively, ensuring we count opening/closing brackets correctly).
• \): Matches the final closing parenthesis of the outer column definition block.

Example in JavaScript: #

const createTableSql = `CREATE TABLE customers (
    id INT(11) AUTO_INCREMENT PRIMARY KEY,
    full_name VARCHAR(100) NOT NULL,
    signup_date DATE(6) DEFAULT CURRENT_TIMESTAMP(6)
)`;

// Use the 's' flag to treat newlines as part of the string
const regex = /CREATE TABLE\s+\w+\s*\(([^()]|(\((?:[^()]|(?2))*\)))*\)/s;
const match = createTableSql.match(regex);

if (match) {
  // Strip off the outer CREATE TABLE wrapper to get just column definitions
  const columnDefinitions = match[0]
    .replace(/^CREATE TABLE\s+\w+\s*\(/, '')
    .replace(/\)$/, '')
    .trim();
  
  console.log(columnDefinitions);
}

Approach 2: Manual Bracket Counting (For Tools Without Balanced Regex Support) #

If you’re using Python’s standard re module (which doesn’t support balanced groups) or prefer a more reliable cross-environment solution, manually counting brackets is foolproof:

Example in Python: #

create_table_sql = """CREATE TABLE orders (
    order_id INT(11) PRIMARY KEY,
    customer_id INT(11) NOT NULL,
    order_total DECIMAL(10,2) DEFAULT 0.00,
    status ENUM('pending','shipped','delivered')
)"""

# Find the first opening parenthesis of column definitions
start_idx = create_table_sql.find('(')
if start_idx == -1:
    print("No valid CREATE TABLE statement found")
else:
    bracket_count = 1
    end_idx = start_idx + 1
    
    # Iterate through the string to count brackets until we hit the outer closing one
    while end_idx < len(create_table_sql) and bracket_count > 0:
        if create_table_sql[end_idx] == '(':
            bracket_count += 1
        elif create_table_sql[end_idx] == ')':
            bracket_count -= 1
        end_idx += 1
    
    # Extract and clean up the column definitions
    column_definitions = create_table_sql[start_idx+1 : end_idx-1].strip()
    print(column_definitions)

Why This Works: #

We track the number of opening/closing brackets—every time we hit an (, we increment the count, and every ) decrements it. When the count hits 0, we’ve found the outer closing parenthesis.

Key Notes: #

• Both methods handle any depth of nested parentheses (even if you have weird edge cases like JSON(MAX) or custom type definitions with brackets).
• Make sure to account for whitespace/newlines—most SQL allows line breaks in CREATE TABLE statements, so adjust your regex or string handling accordingly.

内容的提问来源于stack exchange，提问作者as.beaulieu