MySQL 用 CASE WHEN 模拟 PIVOT 的多列动态写法

mysql原生不支持PIVOT语法，需用CASE WHEN+聚合函数实现静态透视，动态列则依赖PREPARE/EXECUTE拼接SQL，但存在长度限制、转义、性能及维护等多重风险。

为什么不能直接用 MySQL 的 PIVOT？

MySQL 原生不支持 PIVOT 语法，SQL Server 和 oracle 可以直接写 PIVOT，但 MySQL 得靠 CASE WHEN + 聚合函数“手搓”。这不是语法缺陷，而是设计取舍——MySQL 更倾向把行转列这类逻辑交给应用层或预处理。但真要 SQL 里做，就得自己拼出列名、对齐值、避免 NULL 干扰。

CASE WHEN 实现静态多列透视的关键写法

所谓“静态”，是指你知道所有要转成列的值（比如固定几个部门：'HR'、'IT'、'Finance'），这时可以直接写死 CASE WHEN 分支：

SELECT   dept,   SUM(CASE WHEN month = 'Jan' THEN sales END) AS `Jan`,   SUM(CASE WHEN month = 'Feb' THEN sales END) AS `Feb`,   SUM(CASE WHEN month = 'Mar' THEN sales END) AS `Mar` FROM sales_data GROUP BY dept;

注意三点：

• SUM() 或 MAX() 等聚合函数必不可少，否则 MySQL 会报错“Invalid use of group function”
• 每个 CASE WHEN 分支必须有 ELSE NULL（显式或隐式），否则漏匹配时结果不可控
• 列别名要用反引号包裹，尤其当列名含短横线、数字开头或关键字时（如 `2024-Q1`）

动态列怎么办？只能靠预生成 SQL

如果要转的列来自数据本身（比如所有出现过的 product_name），MySQL 没法在单条语句里自动展开——必须用 PREPARE + EXECUTE 拼接 SQL 字符串：

先查出所有唯一值生成列部分：

SELECT GROUP_CONCAT(   DISTINCT CONCAT(     'SUM(CASE WHEN product_name = ''', product_name, ''' THEN revenue END) AS `', product_name, '`')   ORDER BY product_name   SEPARATOR ', ' ) INTO @sql_cols FROM sales_summary;

再拼完整语句并执行：

SET @sql = CONCAT('SELECT region, ', @sql_cols, ' FROM sales_summary GROUP BY region'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

常见坑：

• GROUP_CONCAT 默认长度限制是 1024，长列名多时会截断，需提前设 SET session group_concat_max_len = 1000000;
• 单引号要双写（''）才能正确转义进字符串，漏了就会报 Unknown column 或语法错误
• 不能在存储过程外直接用变量拼接后执行；若需复用，必须封装进存储过程

性能和可维护性的真实代价

这种动态写法看着灵活，但实际线上慎用：

• 每次执行都触发 SQL 解析+编译，不如静态语句走 query cache（虽然 8.0 默认关了）
• EXPLaiN 看不到真实执行计划，调试困难；加索引也难针对性优化
• 一旦 product_name 有 500 个不同值，生成的 SQL 会超长，客户端可能报 Packets larger than max_allowed_packet
• 更现实的做法：用应用代码（python/java）查出维度值，生成 SQL 后执行；或改用物化视图思路，定期跑 etl 写入宽表

真正需要动态列的场景，往往说明模型该拆了——把“列”变回“行”，加个 Attribute 和 value 字段，反而更稳。