从动态TSQL语句中获取列名及原始值的技术求助

Got it, let's figure out how to extract both column names and their corresponding raw values from dynamic SQL statements—since you mentioned your current setup only pulls column names, here are practical, actionable solutions tailored to different scenarios:

1. Use a SQL Parser (Cross-Language, Universal Approach) #

The most reliable way is to parse the dynamic SQL into an Abstract Syntax Tree (AST), then traverse the tree to map columns to their raw values. This works for any dynamically generated SQL, whether it's hardcoded strings or user-generated queries.

Example with Java (JSqlParser) #

JSqlParser is a robust library for parsing SQL across dialects:

import net.sf.jsqlparser.JSQLParserException;
import net.sf.jsqlparser.parser.CCJSqlParserUtil;
import net.sf.jsqlparser.statement.insert.Insert;
import net.sf.jsqlparser.statement.update.Update;

public class SqlColumnValueExtractor {
    public static void parseInsertSql(String sql) throws JSQLParserException {
        Insert insertStmt = (Insert) CCJSqlParserUtil.parse(sql);
        // Get column names
        System.out.println("Columns: " + insertStmt.getColumns());
        // Get raw literal values (handles strings, numbers, NULL, etc.)
        System.out.println("Raw Values: " + insertStmt.getItemsList());
    }

    public static void parseUpdateSql(String sql) throws JSQLParserException {
        Update updateStmt = (Update) CCJSqlParserUtil.parse(sql);
        updateStmt.getSets().forEach(setItem -> {
            System.out.println("Column: " + setItem.getColumn());
            System.out.println("Raw Value: " + setItem.getValue());
        });
    }

    public static void main(String[] args) throws JSQLParserException {
        String dynamicInsert = "INSERT INTO users (id, name, email) VALUES (1, 'Alice', 'alice@example.com')";
        parseInsertSql(dynamicInsert);

        String dynamicUpdate = "UPDATE users SET name = 'Bob', email = 'bob@example.com' WHERE id = 1";
        parseUpdateSql(dynamicUpdate);
    }
}

Example with Python (sqlparse) #

sqlparse is a lightweight, easy-to-use parser for Python:

import sqlparse
from sqlparse.sql import IdentifierList

def extract_insert_details(sql):
    parsed = sqlparse.parse(sql)[0]
    # Extract columns
    columns = next(t for t in parsed.tokens if isinstance(t, IdentifierList))
    column_names = [col.value.strip() for col in columns.get_identifiers()]
    # Extract raw values
    values_clause = next(t for t in parsed.tokens if t.value.upper() == 'VALUES')
    values = next(t for t in values_clause.parent.tokens if isinstance(t, IdentifierList))
    raw_values = [val.value.strip() for val in values.get_identifiers()]
    print(f"Columns: {column_names}\nRaw Values: {raw_values}")

def extract_update_details(sql):
    parsed = sqlparse.parse(sql)[0]
    set_clause = next(t for t in parsed.tokens if t.value.upper() == 'SET')
    set_items = next(t for t in set_clause.parent.tokens if isinstance(t, IdentifierList))
    for item in set_items.get_identifiers():
        if '=' in item.value:
            col, val = item.value.split('=', 1)
            print(f"Column: {col.strip()}, Raw Value: {val.strip()}")

# Test with sample dynamic SQL
extract_insert_details("INSERT INTO users (id, name, email) VALUES (1, 'Alice', 'alice@example.com')")
extract_update_details("UPDATE users SET name = 'Bob', email = 'bob@example.com' WHERE id = 1")

2. Track Values During SQL Construction (Application-Level Win) #

If you're generating the dynamic SQL in your own code, you don't need to parse it at all—just track columns and values as you build the query. This is simpler and avoids parsing overhead.

Example with Java #

import java.util.HashMap;
import java.util.Map;

public class DynamicSqlBuilder {
    public static void main(String[] args) {
        // Track columns and values in a map while building the SQL
        Map<String, Object> columnValueMap = new HashMap<>();
        columnValueMap.put("id", 1);
        columnValueMap.put("name", "Alice");
        columnValueMap.put("email", "alice@example.com");

        // Build the SQL string from the map
        String columns = String.join(", ", columnValueMap.keySet());
        String placeholders = String.join(", ", java.util.Collections.nCopies(columnValueMap.size(), "?"));
        String sql = String.format("INSERT INTO users (%s) VALUES (%s)", columns, placeholders);

        // Now you have direct access to both columns and raw values
        System.out.println("Columns: " + columnValueMap.keySet());
        System.out.println("Raw Values: " + columnValueMap.values());
        System.out.println("Generated SQL: " + sql);
    }
}

3. Handle Prepared Statements with Parameter Binding #

If you're using prepared statements (e.g., Java's PreparedStatement, Python's psycopg2), the SQL only contains placeholders. In this case, track the parameter values separately as you bind them—they'll map directly to the columns you defined.

Example with Python (psycopg2) #

import psycopg2

# Connect to your database
conn = psycopg2.connect("dbname=my_db user=postgres")
cur = conn.cursor()

# Define columns and their raw values explicitly
columns = ["id", "name", "email"]
raw_values = (1, "Alice", "alice@example.com")

# Build prepared statement SQL
sql = f"INSERT INTO users ({', '.join(columns)}) VALUES ({', '.join(['%s']*len(columns))})"
cur.execute(sql, raw_values)

# You already have columns and values from your variables
print(f"Columns: {columns}\nRaw Values: {raw_values}")

conn.commit()
cur.close()
conn.close()

Key Notes to Keep in Mind #

• Expression Values: If your SQL uses expressions (e.g., NOW(), COUNT(*) instead of literal values), parsers will return the expression string as the "raw value"—adjust your logic if you need to distinguish literals from expressions.
• SQL Dialects: Most parsers support multiple dialects (MySQL, PostgreSQL, etc.)—make sure to configure the parser for your database type if needed.
• Security: If parsing user-generated SQL, avoid executing any parsed content directly to prevent SQL injection risks.

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