MySQL UPDATE语句on子句报p.sales_mode未知列错误的优化咨询

背景说明 #

字段 sales_mode 的值映射规则为：A|B -> 1|2，即将分隔存储的编码值替换为对应的业务值。
之前直接写两层嵌套子查询的UPDATE语句报错[42S22][1054] Unknown column 'p.sales_mode' in 'on clause'，是因为MySQL的字段作用域限制：外层表的字段只能向下穿透一层子查询，超过两层嵌套就无法识别，你后续通过全表关联匹配ID的方式虽然解决了报错，但存在较大的性能优化空间。

优化方向 #

• 使用UPDATE JOIN替代多层嵌套关联子查询
  你当前的写法属于关联子查询，会对每一行待更新数据逐行执行嵌套查询，数据量大时性能极差。改为派生表+JOIN更新的方式，仅需一次计算所有待更新行的映射结果，再批量关联更新，性能提升明显。优化后的参考SQL如下：

  -- 提前调整GROUP_CONCAT最大长度，避免长值截断，可根据实际业务调整大小
  SET SESSION group_concat_max_len = 10240;
  
  UPDATE rv_p_ab_ass_equ p
  INNER JOIN (
      SELECT 
          a.id,
          GROUP_CONCAT(c.voc_code SEPARATOR '|') AS new_sales_mode
      FROM rv_p_ab_ass_equ a
      JOIN mysql.help_topic b 
          ON b.help_topic_id < LENGTH(a.sales_mode) - LENGTH(REPLACE(a.sales_mode, '|', '')) + 1
          -- 限制help_topic_id的最大范围，假设单个sales_mode最多不超过20个枚举值，可根据实际调整
          AND b.help_topic_id < 20
      JOIN rv_pub_voc_transform c 
          ON c.voc_code_e = SUBSTRING_INDEX(SUBSTRING_INDEX(a.sales_mode, '|', b.help_topic_id + 1), '|', -1)
      WHERE 
          a.sales_mode IS NOT NULL 
          AND a.sales_mode != ' '
          AND c.voc_type_e = 'T_S_DT_TD_MODE'
          AND c.system_name_e = '${system_name}'
          AND c.delete_flag = 0
      GROUP BY a.id
  ) t ON p.id = t.id
  SET p.sales_mode = t.new_sales_mode;
  

• 提前过滤无效数据
  不需要更新的空值、空格值直接在派生表的WHERE条件中过滤，不需要进入后续关联逻辑，也不需要在SET中用CASE判断，减少处理的数据量。
• 添加覆盖索引减少回表
  在rv_pub_voc_transform表上创建联合索引 idx_voc_mapping(voc_type_e, system_name_e, delete_flag, voc_code_e, voc_code)，所有查询用到的过滤字段、关联字段、返回字段都包含在索引中，查询时直接走索引不需要回表查询原数据，大幅提升关联速度。
• 限制help_topic关联范围
  由于sales_mode是业务枚举字段，单条值的分隔数量通常不会太大，可在关联mysql.help_topic时增加help_topic_id的上限过滤，避免不需要的ID参与关联，减少临时数据量。
• 替换非规范的字符串拆分方式
  如果使用MySQL 8.0及以上版本，建议用JSON_TABLE或者递归CTE替代mysql.help_topic做字符串拆分，避免依赖系统表的权限和存在性风险，逻辑也更规范。

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