UPDATE `xxx_detail` SET `id`=1955445664111890432, `product_name`='xxx', `update_time`='2025-08-23 13:37:44' WHERE `id`=1955445664111890432;
UPDATE `contracxxxt` SET `order_sn`='xxxxx', `total_amount`=1816485 WHERE `id`=1955445671208652800;

INSERT INTO `xxx_detail` (`id`, `product_name`, `update_time`) VALUES (1955445664111890432, 'xxx', '2025-08-23 13:37:44');
INSERT INTO `contracxxxt` (`order_sn`, `total_amount`) VALUES ('xxxxx', 1816485);

import re
import os

def update_to_insert(sql_content):
    """將UPDATE語句轉(zhuǎn)換為INSERT語句"""
    
    # 正則表達式匹配UPDATE語句
    update_pattern = r'UPDATE `(\w+)` SET (.+?) WHERE `id`=(\d+);'
    matches = re.findall(update_pattern, sql_content, re.DOTALL)
    
    insert_statements = []
    
    for table_name, set_clause, id_value in matches:
        # 解析SET子句
        set_items = re.findall(r'`(\w+)`=([^,]+)(?:,|$)', set_clause)
        
        # 構(gòu)建列名和值
        columns = []
        values = []
        
        for column, value in set_items:
            columns.append(f"`{column}`")
            
            # 處理NULL值
            value = value.strip()
            if value.upper() == 'NULL':
                values.append('NULL')
            # 處理字符串值（用單引號括起來的）
            elif re.match(r"^'.*'$", value):
                # 去除外層單引號，然后重新添加正確的單引號
                inner_value = value[1:-1]  # 去掉外層單引號
                # 轉(zhuǎn)義內(nèi)部單引號
                escaped_value = inner_value.replace("'", "''")
                values.append(f"'{escaped_value}'")
            # 處理數(shù)字值
            else:
                values.append(value)
        
        # 構(gòu)建INSERT語句
        insert_sql = f"INSERT INTO `{table_name}` ({', '.join(columns)}) VALUES ({', '.join(values)});"
        insert_statements.append(insert_sql)
    
    return insert_statements

def process_sql_file(input_file, output_file):
    """處理SQL文件，將UPDATE轉(zhuǎn)換為INSERT"""
    
    # 檢查輸入文件是否存在
    if not os.path.exists(input_file):
        print(f"錯誤：輸入文件 '{input_file}' 不存在")
        return
    
    try:
        # 讀取輸入文件
        with open(input_file, 'r', encoding='utf-8') as f:
            sql_content = f.read()
        
        # 轉(zhuǎn)換UPDATE語句
        insert_statements = update_to_insert(sql_content)
        
        # 寫入輸出文件
        with open(output_file, 'w', encoding='utf-8') as f:
            f.write("-- 由UPDATE語句生成的INSERT語句\n")
            f.write("-- 生成時間: 2025-09-27\n")
            f.write("-- 源文件: " + input_file + "\n")
            f.write("=" * 80 + "\n\n")
            
            for i, insert_stmt in enumerate(insert_statements, 1):
                f.write(f"-- INSERT語句 {i}\n")
                f.write(insert_stmt + "\n")
                f.write("\n")
        
        print(f"成功生成 {len(insert_statements)} 條INSERT語句")
        print(f"輸出文件: {output_file}")
        
    except Exception as e:
        print(f"處理文件時出錯: {e}")

def main():
    """主函數(shù)"""
    print("UPDATE語句轉(zhuǎn)INSERT語句工具")
    print("=" * 40)
    
    # 輸入文件路徑
    input_file = input("請輸入包含UPDATE語句的文件路徑: ").strip()
    
    # 輸出文件路徑（默認在輸入文件同目錄下）
    if input_file:
        base_name = os.path.splitext(input_file)[0]
        output_file = f"{base_name}_insert.sql"
    else:
        output_file = "output_insert.sql"
    
    # 確認輸出文件路徑
    custom_output = input(f"請輸入輸出文件路徑 (默認: {output_file}): ").strip()
    if custom_output:
        output_file = custom_output
    
    # 處理文件
    process_sql_file(input_file, output_file)

# 示例使用（直接指定文件路徑）
if __name__ == "__main__":
    # 方式1：交互式輸入
    # main()
    
    # 方式2：直接指定文件路徑
    input_file = "./rollback_12681.sql"  # 替換為你的文件路徑
    output_file = "output_insert.sql"
    process_sql_file(input_file, output_file)

update_pattern = r'UPDATE `(\w+)` SET (.+?) WHERE `id`=(\d+);'

set_items = re.findall(r'`(\w+)`=([^,]+)(?:,|$)', set_clause)

$ python update_to_insert.py
UPDATE語句轉(zhuǎn)INSERT語句工具
========================================
請輸入包含UPDATE語句的文件路徑: ./rollback.sql
請輸入輸出文件路徑 (默認: ./rollback_insert.sql): 
成功生成 25 條INSERT語句
輸出文件: ./rollback_insert.sql

if __name__ == "__main__":
    input_file = "./your_update_file.sql"
    output_file = "./output_insert.sql"
    process_sql_file(input_file, output_file)

UPDATE `resource_detail` SET `id`=1955445664111890432, `product_name`='熱軋卷', `update_time`='2025-08-23 13:37:44' WHERE `id`=1955445664111890432;

INSERT INTO `resource_detail` (`id`, `product_name`, `update_time`) VALUES (1955445664111890432, '熱軋卷', '2025-08-23 13:37:44');