如何用列名通配符实现表间数据插入以提升SQL可维护性？

好问题！这种每次新增列都要手动修改INSERT语句的场景，确实挺折腾人的。下面给你几个不用硬编码列名的可维护方案，适配大部分主流数据库：

方案1：动态生成INSERT语句（通用型） #

利用数据库的系统元数据视图（比如information_schema.columns）自动匹配符合命名规则的列，动态拼接出完整的INSERT语句。这样不管新增多少符合规则的列（比如wA/wB对应w2/w1），只要运行这段SQL就能生成最新的插入语句，完全不用手动修改。

以PostgreSQL/MySQL为例，代码如下：

-- 生成INSERT语句的SQL
SELECT 'INSERT INTO Table2 (' || string_agg(t2_col, ', ') || ') SELECT ' || string_agg(t1_col, ', ') || ' FROM Table1;'
FROM (
    SELECT 
        col1.column_name AS t1_col,
        col2.column_name AS t2_col
    FROM information_schema.columns col1
    JOIN information_schema.columns col2 
        -- 匹配列名的前缀（去掉最后一个字符）
        ON SUBSTRING(col1.column_name FROM 1 FOR LENGTH(col1.column_name)-1) = SUBSTRING(col2.column_name FROM 1 FOR LENGTH(col2.column_name)-1)
        -- 匹配后缀规则：B→1，A→2
        AND (
            (col1.column_name LIKE '%B' AND col2.column_name LIKE '%1')
            OR (col1.column_name LIKE '%A' AND col2.column_name LIKE '%2')
        )
    WHERE col1.table_name = 'Table1'
      AND col2.table_name = 'Table2'
      AND col1.table_schema = 'public' -- 替换成你的数据库schema
      AND col2.table_schema = 'public'
) AS column_matches;

运行这段SQL后，会直接输出可以执行的INSERT语句，复制执行即可完成数据插入。

方案2：封装成存储过程（适合定期执行场景） #

如果需要定期执行这个插入操作，可以把动态生成语句的逻辑封装成存储过程，每次调用存储过程就自动完成插入，连生成语句的步骤都省了。

以MySQL为例，存储过程代码如下：

DELIMITER //
CREATE PROCEDURE InsertFromTable1ToTable2()
BEGIN
    DECLARE insert_sql VARCHAR(4000);
    -- 动态拼接INSERT语句
    SELECT CONCAT(
        'INSERT INTO Table2 (', GROUP_CONCAT(t2_col SEPARATOR ', '), ') SELECT ', GROUP_CONCAT(t1_col SEPARATOR ', '), ' FROM Table1;'
    ) INTO insert_sql
    FROM (
        SELECT 
            col1.column_name AS t1_col,
            col2.column_name AS t2_col
        FROM information_schema.columns col1
        JOIN information_schema.columns col2 
            ON LEFT(col1.column_name, LENGTH(col1.column_name)-1) = LEFT(col2.column_name, LENGTH(col2.column_name)-1)
            AND (
                (col1.column_name LIKE '%B' AND col2.column_name LIKE '%1')
                OR (col1.column_name LIKE '%A' AND col2.column_name LIKE '%2')
            )
        WHERE col1.table_name = 'Table1'
          AND col2.table_name = 'Table2'
          AND col1.table_schema = DATABASE() -- 自动获取当前数据库
          AND col2.table_schema = DATABASE()
    ) AS matches;
    -- 执行动态SQL
    PREPARE stmt FROM insert_sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END //
DELIMITER ;

-- 调用存储过程完成插入
CALL InsertFromTable1ToTable2();

之后不管新增多少符合规则的列，直接调用CALL InsertFromTable1ToTable2();就能完成最新的插入操作。

关键注意事项 #

• 严格遵守命名规则：要保证所有*A后缀的列都有对应的*2后缀列，*B后缀对应*1后缀，否则会出现列匹配失败的情况。
• 数据类型一致性：同步的列必须保证数据类型一致，避免插入时出现类型转换错误。
• 数据库适配：如果是SQL Server，系统视图要换成sys.columns，字符串聚合函数用STRING_AGG（2017+版本）或者FOR XML PATH的方式，语法需要稍作调整。

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