如何在where子句MySQL中使用变量列名?

Question

在此查询中:

SELECT *
FROM general_settings AS general_settings
JOIN settings_attribute AS settings_attribute ON settings_attribute.id = general_settings.settings_attribute_id 
JOIN user_settings AS user_settings ON general_settings.user_settings_id = user_settings.id 
WHERE 
(settings_attribute.name = 'AAA' AND brand_settings.AAA <> general_settings.value)
OR
(settings_attribute.name = 'BBB' AND brand_settings.BBB <> general_settings.value)
OR
(settings_attribute.name = 'CCC' AND brand_settings.CCC <> general_settings.value)
;

我想要一种方法使用MySQL或Redshift使用settings_attribute.name作为列名，以避免在查询中写入所有的属性名作为静态的，例如:

SELECT *
FROM general_settings AS general_settings
JOIN settings_attribute AS settings_attribute ON settings_attribute.id = general_settings.settings_attribute_id 
JOIN user_settings AS user_settings ON general_settings.user_settings_id = user_settings.id 
WHERE 
brand_settings.@settings_attribute.name <> general_settings.value
;

Answer 1

不，这不可能。在SQL中，所有标识符(例如列名)在解析查询时必须是显式的和固定的，以便SQL引擎可以在开始执行之前验证列是否确实存在。查询不可能根据它在执行期间读取的字符串值命名不同的列。

如果您的settings_attribute.name在某些行中包含'XYZ'，但没有该名称的列，会发生什么?如果您命名的列不存在，但在SQL中，在解析查询时检查该列，则会出现错误。