如何用Python正则提取MySQL完整性错误的可变部分？

No worries, let's break this down step by step. MySQL integrity errors follow a consistent structure, so we can build regex patterns that target the dynamic bits without breaking a sweat. First, let's start with common examples of these errors, then craft regex for each scenario, and wrap it up with Python code to put it all together.

Common MySQL Integrity Error Examples #

Let's use these typical error messages as our test cases:

• Unique key conflict:

  1062 (23000): Duplicate entry 'john_doe' for key 'users.username'

• Foreign key constraint failure:

  1452 (23000): Cannot add or update a child row: a foreign key constraint fails (mydb.orders, CONSTRAINT orders_user_id_foreign FOREIGN KEY (user_id) REFERENCES users (id))

• Not-null constraint violation:

  1048 (23000): Column 'email' cannot be null

Regex Patterns for Each Scenario #

We'll use capturing groups to pull out the variable parts. Remember to escape special characters like (, ), ` since they're part of MySQL's error syntax (using Python raw strings r"" makes this easier).

1. Unique Key Conflict #

This pattern captures the duplicate value and the affected key:

^(\d+) \((\w+)\): Duplicate entry '([^']+)' for key '([^']+)'$

• (\d+): Captures the numeric error code (e.g., 1062)
• (\w+): Captures the SQLSTATE code (e.g., 23000)
• ([^']+): Captures the duplicate value (stops at the next single quote, avoiding over-matching)
• ([^']+): Captures the key name (e.g., users.username)

2. Foreign Key Constraint Failure #

This pattern extracts the database, table, constraint name, and related fields:

^(\d+) \((\w+)\): Cannot add or update a child row: a foreign key constraint fails \(`([^`]+)`\.`([^`]+)`, CONSTRAINT `([^`]+)` FOREIGN KEY \(`([^`]+)`\) REFERENCES `([^`]+)` \(`([^`]+)`\)$

• Captures in order: Error code, SQLSTATE, database name, child table name, constraint name, child column, parent table, parent column

3. Not-Null Constraint Violation #

Simple pattern to get the column name causing the issue:

^(\d+) \((\w+)\): Column '([^']+)' cannot be null$

• Captures: Error code, SQLSTATE, the non-null column name (e.g., email)

Universal Fallback Pattern #

If you want a single regex to handle multiple integrity errors and pull out the core message along with codes:

^(\d+) \((\w+)\): (.+)$

• (\d+): Error code
• (\w+): SQLSTATE
• (.+): The full error description (you can parse this further if needed for specific details)

Python Implementation Example #

Here's how to use these patterns in practice:

import re

# Test error messages
unique_error = "1062 (23000): Duplicate entry 'john_doe' for key 'users.username'"
fk_error = "1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`mydb`.`orders`, CONSTRAINT `orders_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`))"
not_null_error = "1048 (23000): Column 'email' cannot be null"

# Handle unique key conflict
unique_pattern = r"^(\d+) \((\w+)\): Duplicate entry '([^']+)' for key '([^']+)'$"
match = re.match(unique_pattern, unique_error)
if match:
    error_code, sqlstate, duplicate_val, key_name = match.groups()
    print(f"Unique key conflict: Duplicate value '{duplicate_val}' on key '{key_name}'")

# Handle foreign key failure
fk_pattern = r"^(\d+) \((\w+)\): Cannot add or update a child row: a foreign key constraint fails \(`([^`]+)`\.`([^`]+)`, CONSTRAINT `([^`]+)` FOREIGN KEY \(`([^`]+)`\) REFERENCES `([^`]+)` \(`([^`]+)`\)$"
match = re.match(fk_pattern, fk_error)
if match:
    error_code, sqlstate, db_name, child_table, constraint, child_col, parent_table, parent_col = match.groups()
    print(f"Foreign key failure: Constraint '{constraint}' on {db_name}.{child_table}.{child_col} references {parent_table}.{parent_col}")

# Handle not-null violation
not_null_pattern = r"^(\d+) \((\w+)\): Column '([^']+)' cannot be null$"
match = re.match(not_null_pattern, not_null_error)
if match:
    error_code, sqlstate, column = match.groups()
    print(f"Not-null violation: Column '{column}' cannot be empty")

Troubleshooting Tips #

• If your regex isn't matching, double-check that you've escaped special characters properly (using raw strings r"" avoids having to escape backslashes twice in Python).
• The [^']+ and [^]+patterns are safer than.*?` because they stop at the next quote/backtick, preventing accidental over-matching of unrelated content.
• If you encounter error messages with extra whitespace, add \s* where needed to account for optional spaces (e.g., \s*Cannot\s*add).

内容的提问来源于stack exchange，提问作者QLands - Carlos Quiros