MySQL兼容查询：列存在时取值否则返回NULL的实现报错求助

错误原因 #

你的SQL报错是因为MySQL在查询解析阶段就会校验所有引用的列是否存在，不管CASE语句里的条件是否满足。哪怕你判断出position_id列不存在，只要SELECT子句里写了sg.position_id，MySQL就会直接抛出“未知列”的错误——它不会等到执行阶段再去判断CASE的条件。

解决方案 #

方法一：动态SQL（推荐）

通过预处理语句先判断列是否存在，再构造对应的查询逻辑，完美绕过解析阶段的列检查：

SET @sql = '';

SELECT 
  IF(
    EXISTS(SELECT 1 FROM information_schema.columns WHERE table_name = 'site_group' AND column_name = 'position_id' AND table_schema = DATABASE()),
    'SELECT DISTINCT sg.id as site_group_id, coalesce(sgd.name, sgdefault.name) as site_group_name, sg.site_group_type, sg.term_end, sg.position_id as position_id, sa.site_id FROM site_group sg',
    'SELECT DISTINCT sg.id as site_group_id, coalesce(sgd.name, sgdefault.name) as site_group_name, sg.site_group_type, sg.term_end, NULL as position_id, sa.site_id FROM site_group sg'
  ) INTO @sql;

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

注意：加上table_schema = DATABASE()是为了限定检查当前数据库的表，避免不同库中同名表的干扰。

方法二：静态SQL（UNION ALL 折中方案）

如果无法使用动态SQL，可以用两个分支的UNION，利用EXISTS条件控制哪个分支生效：

SELECT 
  DISTINCT sg.id as site_group_id,
  coalesce(sgd.name, sgdefault.name) as site_group_name,
  sg.site_group_type,
  sg.term_end,
  sg.position_id as position_id,
  sa.site_id
FROM site_group sg
WHERE EXISTS(SELECT 1 FROM information_schema.columns WHERE table_name = 'site_group' AND column_name = 'position_id' AND table_schema = DATABASE())

UNION ALL

SELECT 
  DISTINCT sg.id as site_group_id,
  coalesce(sgd.name, sgdefault.name) as site_group_name,
  sg.site_group_type,
  sg.term_end,
  NULL as position_id,
  sa.site_id
FROM site_group sg
WHERE NOT EXISTS(SELECT 1 FROM information_schema.columns WHERE table_name = 'site_group' AND column_name = 'position_id' AND table_schema = DATABASE())

原理是：只有当列存在时，第一个SELECT的WHERE条件成立才会执行；列不存在时，第二个SELECT生效。这样每个分支的SQL都不会引用不存在的列，避免了解析错误。

内容的提问来源于stack exchange，提问作者Muhammad Daniyal Danish