求助：使用Awk每12行合并生成MySQL插入查询语句

Got it, let's tackle this problem step by step. Since you have script output where every 12 lines represent a single database record, here are a few practical approaches to generate the MySQL INSERT statements you need.

Option 1: Bash Command-Line Tool (Fast & Simple) #

If your script output is saved to a file (say script_output.txt), you can use paste and awk to quickly group lines and format inserts. This works great for straightforward, unquoted data (adjust if you need string quotes):

# Group 12 lines into comma-separated values, then wrap into INSERT statements
paste -d',' - - - - - - - - - - - - < script_output.txt | awk '{print "INSERT INTO your_table (col1, col2, col3, col4, col5, col6, col7, col8, col9, col10, col11, col12) VALUES (" $0 ");"}'

Notes for Bash: #

• Replace your_table with your actual table name, and col1 to col12 with your column names.
• If your values are strings that need quotes, modify the awk command to add them:

  paste -d',' - - - - - - - - - - - - < script_output.txt | awk '{gsub(/,/, "\",\""); print "INSERT INTO your_table (...) VALUES (\"" $0 "\");"}'
  

• Add > insert_queries.sql at the end to save the output to a file instead of printing it.

Option 2: Python Script (Flexible & Robust) #

For more control (like handling special characters, empty values, or data type formatting), a Python script is ideal. This example reads your output file, cleans up whitespace, groups lines, and generates properly escaped INSERT statements:

# Configure these variables to match your database setup
TABLE_NAME = "your_table"
COLUMNS = ["col1", "col2", "col3", "col4", "col5", "col6", "col7", "col8", "col9", "col10", "col11", "col12"]

# Read and clean the script output
with open("script_output.txt", "r") as f:
    # Remove empty lines and trim whitespace from each line
    cleaned_lines = [line.strip() for line in f if line.strip()]

# Split cleaned lines into chunks of 12 (one chunk = one database record)
records = [cleaned_lines[i:i+12] for i in range(0, len(cleaned_lines), 12)]

# Generate and print INSERT statements
for record in records:
    # Escape single quotes (critical for MySQL string values)
    escaped_values = []
    for val in record:
        # Handle empty values if needed (replace with NULL if that's appropriate)
        if not val:
            escaped_values.append("NULL")
        else:
            # Replace single quotes with double single quotes for MySQL escaping
            escaped_val = val.replace("'", "''")
            escaped_values.append(f"'{escaped_val}'")
    
    values_str = ", ".join(escaped_values)
    columns_str = ", ".join(COLUMNS)
    print(f"INSERT INTO {TABLE_NAME} ({columns_str}) VALUES ({values_str});")

Notes for Python: #

• Save this as generate_inserts.py, replace the TABLE_NAME and COLUMNS variables, then run it with python generate_inserts.py.
• Adjust the empty value handling (currently uses NULL) to match your table's requirements.
• Add > insert_queries.sql to the command to save the output to a file.

Key Tips Before Running Inserts: #

1. Test with a small subset: Run the tool on 24-36 lines first to verify the output is correct.
2. Check data types: Make sure numeric values don't have quotes, and string values do (the Python script handles this, but the Bash script may need adjustments).
3. Escape special characters: Always escape single quotes in string values—MySQL will throw errors otherwise.

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