MySQL动态行转列：预编译语句报错排查

问题背景 #

我有一个包含3列的实体属性值（EAV）表，列名分别为policyholder_locator、field_name、field_value，示例数据如下：

policyholder_locator,field_name,field_value
1,first_name,john
1,phone,8888888888
1,email,john@gmail.com
2,first_name,jane
2,phone,7777777777
2,email,jane@gmail.com
3,first_name,joe
3,phone,6666666666
3,email,joe@gmail.com

尝试用结合GROUP_CONCAT和预编译语句的动态SQL实现行转列时，抛出错误：An SQLException was provoked by the following failure: java.lang.ArrayIndexOutOfBoundsException。动态SQL代码如下：

SET @sql = NULL;
SELECT
    GROUP_CONCAT(DISTINCT
        CONCAT(
            'MAX(CASE WHEN field_name = ''',
            field_name,
            ''' THEN field_value END) ',
            field_name
        )
    ) INTO @sql
FROM policyholder_fields;
SET @sql = CONCAT('SELECT policyholder_locator, ',@sql, '
    FROM policyholder_fields
    GROUP BY policyholder_locator');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

已能用静态SQL实现需求，但更倾向于动态方案，询问这段预编译语句是否存在明显问题。静态SQL代码如下：

SELECT policyholder_locator, 
GROUP_CONCAT(IF(field_name = 'phone', field_value, NULL)) AS phone, 
GROUP_CONCAT(IF(field_name = 'email', field_value, NULL)) AS email, 
GROUP_CONCAT(IF(field_name = 'first_name', field_value, NULL)) AS first_name 
FROM policyholder_fields  
GROUP BY policyholder_locator

问题排查与修正 #

这段动态SQL主要有两个潜在问题，可能触发了报错：

1. GROUP_CONCAT长度限制
   MySQL默认group_concat_max_len仅为1024字节，若生成的SQL语句长度超出此限制，@sql会被截断，导致预编译时出现语法错误，进而引发数组越界异常。

解决办法：执行动态SQL前临时调大该参数：

SET SESSION group_concat_max_len = 1000000;

2. 字段名未做转义处理
   若field_name包含空格、MySQL关键字等特殊字符，直接拼接会导致SQL语法错误。即便当前示例数据无此情况，这也是动态SQL的常见隐患。

优化后的完整动态SQL：

SET @sql = NULL;
SET SESSION group_concat_max_len = 1000000;
SELECT
    GROUP_CONCAT(DISTINCT
        CONCAT(
            'MAX(CASE WHEN field_name = ''',
            field_name,
            ''' THEN field_value END) AS `',
            field_name,
            '`'
        )
    ) INTO @sql
FROM policyholder_fields;
SET @sql = CONCAT('SELECT policyholder_locator, ',@sql, '
    FROM policyholder_fields
    GROUP BY policyholder_locator');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

另外需要注意：静态SQL用GROUP_CONCAT拼接值，动态SQL用MAX取单个值。如果你的实际数据中每个policyholder_locator+field_name是唯一组合，动态SQL的MAX逻辑是合理的，和静态SQL结果一致；若存在多值场景，可将动态SQL中的MAX替换为GROUP_CONCAT，保持逻辑统一。

内容的提问来源于stack exchange，提问作者mike-annex