如何在MySQL/Redshift的WHERE子句中使用变量作为列名？

MySQL 实现方式 #

静态SQL简化方案 #

如果不想编写动态SQL，可通过CASE表达式将settings_attribute.name映射到brand_settings的对应列，比原有的多OR结构更简洁：

SELECT *
FROM general_settings AS gs
JOIN settings_attribute AS sa ON sa.id = gs.settings_attribute_id 
JOIN user_settings AS us ON gs.user_settings_id = us.id 
WHERE 
  CASE sa.name
    WHEN 'AAA' THEN brand_settings.AAA
    WHEN 'BBB' THEN brand_settings.BBB
    WHEN 'CCC' THEN brand_settings.CCC
    -- 新增属性仅需添加WHEN分支
  END <> gs.value;

完全动态SQL方案 #

若要彻底避免手动枚举属性名，可利用MySQL的预编译语句动态生成查询逻辑，自动读取settings_attribute中的所有属性名：

SET @sql = CONCAT(
  'SELECT *
   FROM general_settings AS gs
   JOIN settings_attribute AS sa ON sa.id = gs.settings_attribute_id 
   JOIN user_settings AS us ON gs.user_settings_id = us.id 
   WHERE brand_settings.', 
  (SELECT GROUP_CONCAT(DISTINCT name SEPARATOR ' <> gs.value OR brand_settings.') FROM settings_attribute),
  ' <> gs.value'
);

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Redshift 实现方式 #

静态SQL简化方案 #

与MySQL逻辑一致，通过CASE表达式实现列映射：

SELECT *
FROM general_settings AS gs
JOIN settings_attribute AS sa ON sa.id = gs.settings_attribute_id 
JOIN user_settings AS us ON gs.user_settings_id = us.id 
WHERE 
  CASE sa.name
    WHEN 'AAA' THEN brand_settings.AAA
    WHEN 'BBB' THEN brand_settings.BBB
    WHEN 'CCC' THEN brand_settings.CCC
  END <> gs.value;

完全动态SQL方案 #

Redshift支持EXECUTE执行动态SQL，结合LISTAGG函数拼接属性列表：

DECLARE
  attr_list VARCHAR(MAX);
BEGIN
  SELECT LISTAGG(DISTINCT name, ' <> gs.value OR brand_settings.') INTO attr_list
  FROM settings_attribute;
  
  EXECUTE '
    SELECT *
    FROM general_settings AS gs
    JOIN settings_attribute AS sa ON sa.id = gs.settings_attribute_id 
    JOIN user_settings AS us ON gs.user_settings_id = us.id 
    WHERE brand_settings.' || attr_list || ' <> gs.value
  ';
END;

若直接在查询编辑器中执行，也可通过临时结果集生成动态语句：

WITH attr_names AS (
  SELECT LISTAGG(DISTINCT name, ' <> gs.value OR brand_settings.') AS attr_str
  FROM settings_attribute
)
SELECT EXECUTE(
  'SELECT *
   FROM general_settings AS gs
   JOIN settings_attribute AS sa ON sa.id = gs.settings_attribute_id 
   JOIN user_settings AS us ON gs.user_settings_id = us.id 
   WHERE brand_settings.' || attr_str || ' <> gs.value'
)
FROM attr_names;

注意事项 #

• 动态SQL存在SQL注入风险，若settings_attribute.name为用户可控内容，必须先做转义处理。
• 属性列频繁新增时，动态SQL方案无需修改查询语句，维护成本更低；属性列变动较少时，静态CASE方案更高效简洁。

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