请求编写处理mysqldump生成的INSERT插入语句的Python正则表达式

Hey there! I’ve worked through parsing mysqldump-generated INSERT statements plenty of times for data migration tasks, so I can help you craft the right Python regex (and supporting code) for this.

First, let's break down the key quirks of these INSERT statements we need to handle:

• Bulk records wrapped in (...) blocks, separated by commas
• Fields can include strings with commas, escaped single quotes (written as '' in mysqldump), NULL values, numbers, and empty strings
• The entire statement might span multiple lines

The first challenge is isolating each individual (...) record without getting tripped up by commas inside string fields. A recursive regex works perfectly here (it handles nested brackets, though mysqldump won’t generate those, it adds robustness):

record_pattern = re.compile(r'\((?>[^()]+|(?R))*\)', re.DOTALL)

Regex Breakdown: #

• \(: Match the opening parenthesis
• (?>[^()]+|(?R))*: A non-capturing group that either matches any characters that aren’t parentheses, or recursively matches the entire pattern (for nested brackets)
• \): Match the closing parenthesis
• re.DOTALL: Ensures . matches newlines, critical if the INSERT spans multiple lines

If you want to go beyond just extracting records and actually parse each field into Python-native types (strings, integers, None for NULL), here’s a complete function:

import re

def parse_mysqldump_insert(insert_str):
    # Step 1: Extract the bulk values section from the INSERT statement
    insert_match = re.search(
        r'INSERT INTO \w+ VALUES\s*(.*);', 
        insert_str, 
        re.DOTALL | re.IGNORECASE
    )
    if not insert_match:
        return []
    
    values_content = insert_match.group(1)
    
    # Step 2: Extract all individual record blocks
    record_pattern = re.compile(r'\((?>[^()]+|(?R))*\)', re.DOTALL)
    raw_records = record_pattern.findall(values_content)
    
    # Step 3: Parse each field in the records
    # Regex to match individual fields (handles strings, NULL, numbers, empty strings)
    field_pattern = re.compile(r'''
        (?:
            '([^'\\]*(?:\\.[^'\\]*)*)'  # Match quoted strings (handles escaped chars)
            | NULL                     # Match NULL values
            | (\d+)                    # Match numeric values
            | ''                       # Match empty strings
        )
        (?:,|\s*)                     # Match field separator (comma or whitespace)
    ''', re.VERBOSE | re.DOTALL | re.IGNORECASE)
    
    parsed_records = []
    for raw_record in raw_records:
        # Strip the outer parentheses from the record
        cleaned_record = raw_record.strip('()')
        fields = []
        
        for match in field_pattern.finditer(cleaned_record):
            str_val = match.group(1)
            num_val = match.group(2)
            
            if str_val is not None:
                # Restore escaped single quotes (mysqldump uses '' instead of \')
                fields.append(str_val.replace("''", "'"))
            elif num_val is not None:
                fields.append(int(num_val))
            elif match.group().strip().upper() == 'NULL':
                fields.append(None)
            else:
                # Empty string case
                fields.append('')
        
        parsed_records.append(fields)
    
    return parsed_records

# Test with your sample data
sample_insert = """INSERT INTO users VALUES (1,'pb',NULL,'User Example','example@example','','da',1493878226,NULL,NULL,'das','unassigned','','','','','','','',1,'',NULL,'',NULL,'','','','',1,0,0,25495,NULL,'','',0,0,0,'',NULL,''),(2,'pb',NULL,'User Example','example@example','','a774f',1493878226,NULL,NULL,'device','unassigned','','','','','','','',1,'',NULL,'',NULL,'','','','',0,0,0,NULL,NULL,'','',0,0,0,'',NULL,''),(3,'p=',NULL,'User Example','example@example.com','','abc123',1493878226,NULL,NULL,'laptop','assigned','','','','','','','',0,'',NULL,'',NULL,'','','','',1,1,1,98765,NULL,'','',1,0,1,'',NULL,'')"""

parsed = parse_mysqldump_insert(sample_insert)
for i, rec in enumerate(parsed):
    print(f"Record {i+1}: {rec[:5]}...")  # Print first 5 fields for brevity

• Multiline Support: The re.DOTALL flag is essential if your INSERT statement spans multiple lines (common in mysqldump outputs)
• Escaped Characters: Mysqldump uses two single quotes ('') to escape single quotes in strings—this code handles that conversion back to a single quote
• Robustness: The recursive regex avoids false positives from commas inside string fields, which simple split operations would fail on
• Type Conversion: The function converts numeric fields to integers and NULL to Python’s None for easier downstream processing

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