Redshift：如何相对引用列，用staging表更新snapshot表？

嘿，这个问题问到点子上了！要实现这种不用手动指定列名，动态根据staging表的字段信息更新snapshot的需求，静态SQL肯定搞不定，得靠各个数据库支持的动态SQL功能来实现。下面我针对几种常用数据库给你具体的解决方案（假设两张表有共同的关联主键，比如id，用来匹配要更新的行——这是实现的前提哦）：

MySQL/MariaDB 实现方案 #

MySQL里可以用PREPARE+EXECUTE组合来执行动态生成的SQL：

-- 先动态拼接出所有需要执行的更新语句
SET @sql = (
  SELECT GROUP_CONCAT(
    DISTINCT CONCAT(
      'UPDATE snapshot s JOIN staging t ON s.id = t.id ',
      'SET s.', QUOTE(t.changed_column), ' = t.new_value ',
      'WHERE t.changed_column = ', QUOTE(t.changed_column)
    ) SEPARATOR ';'
  )
  FROM staging
);

-- 执行拼接好的动态SQL
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

这里用GROUP_CONCAT把每个不同的changed_column对应的更新语句拼在一起，QUOTE函数用来转义列名，避免SQL注入和特殊字符问题。如果你的数据量很大，可能需要先调整group_concat_max_len参数来支持更长的SQL字符串。

PostgreSQL 实现方案 #

PostgreSQL可以用PL/pgSQL的循环结合EXECUTE+format函数来处理：

DO $$
DECLARE
  rec record;
BEGIN
  -- 循环处理每个不同的待更新列
  FOR rec IN SELECT DISTINCT changed_column FROM staging LOOP
    EXECUTE format(
      'UPDATE snapshot s
       SET %I = t.new_value
       FROM staging t
       WHERE s.id = t.id AND t.changed_column = %L',
      rec.changed_column, rec.changed_column
    );
  END LOOP;
END $$;

%I会自动转义列名，%L转义字符串，完美规避注入风险。如果你的staging表每行对应一个单独的行+列更新，也可以改成逐行循环处理：

DO $$
DECLARE
  rec record;
BEGIN
  FOR rec IN SELECT id, changed_column, new_value FROM staging LOOP
    EXECUTE format(
      'UPDATE snapshot SET %I = $1 WHERE id = $2',
      rec.changed_column
    ) USING rec.new_value, rec.id;
  END LOOP;
END $$;

SQL Server 实现方案 #

SQL Server可以用sp_executesql存储过程来执行动态SQL：

DECLARE @sql NVARCHAR(MAX) = '';

-- 拼接所有更新语句
SELECT @sql += DISTINCT
  'UPDATE s
   SET s.' + QUOTENAME(t.changed_column) + ' = t.new_value
   FROM snapshot s
   JOIN staging t ON s.id = t.id
   WHERE t.changed_column = ''' + t.changed_column + ''';'
FROM staging;

-- 执行动态SQL
EXEC sp_executesql @sql;

QUOTENAME函数用来转义列名，防止注入和特殊字符导致的语法错误。

几个重要注意事项 #

• 关联键是必须的：一定要有像id这样的字段来匹配snapshot和staging的行，不然根本不知道要更新哪一行。
• 防范SQL注入：如果changed_column的值来自用户输入，一定要用上面提到的转义函数（QUOTE/%I/QUOTENAME），避免恶意注入。
• 数据类型要匹配：确保staging表的new_value类型和snapshot对应列的类型一致，不然会触发类型转换错误。
• 性能考量：如果staging数据量极大，批量生成更新语句的方式比逐行循环更高效，但也要根据实际场景测试调整。

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