MySQL如何将Wordpress关联视图的动态meta_key转为列展示提交数据

问题背景 #

现有一个WordPress多表关联生成的MySQL视图lnd_wp_nps，用于存储用户课程评分提交记录，表结构如下：

+------------+---------------------+------+-----+---------+-------+
| Field      | Type                | Null | Key | Default | Extra |
+------------+---------------------+------+-----+---------+-------+
| post_id    | bigint(20) unsigned | YES  |     | 0       |       |
| field_id   | bigint(21)          | YES  |     | NULL    |       |
| meta_value | longtext            | YES  |     | NULL    |       |
| key        | longtext            | YES  |     | NULL    |       |
| label      | longtext            | YES  |     | NULL    |       |
| user_id    | varchar(255)        | YES  |     | NULL    |       |
| course_id  | varchar(255)        | YES  |     | NULL    |       |
| post_date  | date                | YES  |     | NULL    |       |
+------------+---------------------+------+-----+---------+-------+

数据为行存储模式，每个提交的post_id对应多行记录，分别存储用户信息、课程信息、多个问题的答案。

现有实现 #

已实现固定字段的行转列，SQL如下：

select post_id
, max(case when label = 'Course' then meta_value end) AS course
, max(case when label = 'User' then meta_value end) AS user
, post_date
from lnd_wp_nps
group by post_id
order by post_id

输出格式：

+---------+--------------+----------------------------+------------+
| post_id | course       | user                       | post_date  |
+---------+--------------+----------------------------+------------+
|    1250 | X22-XXX1-ENG | test_1@domain.org          | 2021-10-13 |
|    1251 | X22-XXX2-ENG | test_2@domain.org          | 2021-10-13 |
|    1252 | X22-XXX3-ENG | test_3@domain.org          | 2021-10-13 |
+---------+--------------+----------------------------+------------+

问题说明 #

需要将动态的问题字段也转为列，使用动态SQL拼接时出现两个错误：

1. 列别名长度超过MySQL限制导致语法报错
2. 拼接的SQL出现截断，执行PREPARE时报语法错误

解决方案 #

核心原因 #

MySQL默认group_concat_max_len参数值为1024字节，当动态问题较多时，GROUP_CONCAT拼接的内容会被截断，导致SQL语法错误。

解决步骤 #

1. 临时调高当前会话的GROUP_CONCAT长度限制
2. 优化动态SQL拼接逻辑，处理特殊字符转义和列别名合法性

最终可运行代码 #

-- 调整GROUP_CONCAT最大长度为100KB，可根据实际问题数量调整
SET SESSION group_concat_max_len = 102400;

-- 拼接动态查询SQL
SELECT CONCAT(
    "SELECT `post_id`,",
    "MAX(IF(`key` = 'course_shortcode' , `meta_value`,'')) AS `shortname`,",
    "MAX(IF(`key` = 'user_email' , `meta_value`,'')) AS `email`,",
    GROUP_CONCAT(
        CONCAT(
            "MAX(IF(`key` = '", REPLACE(q.`key`, "'", "\\'"), "' , `meta_value`, '')) AS `", 
            REPLACE(SUBSTR(q.`key`, 1, 64), '`', ''), "`"
        ) SEPARATOR ','
    ),
    ", `post_date` FROM `lnd_wp_nps` GROUP BY `post_id`, `post_date` ORDER BY `post_id`"
) INTO @query_wp_nps
FROM (
    SELECT DISTINCT `key`
    FROM `lnd_wp_nps`
    WHERE `key` NOT IN ('user_email', 'course_shortcode')
) q;

-- 执行生成的动态SQL
PREPARE stmt FROM @query_wp_nps;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

注意事项 #

• 若需永久生效，可在MySQL配置文件my.cnf中添加group_concat_max_len = 102400配置，重启服务后生效
• 拼接逻辑中已处理key字段中的单引号、反引号等特殊字符，避免SQL语法错误
• GROUP BY同时包含post_id和post_date，兼容MySQL高版本ONLY_FULL_GROUP_BY模式的校验要求

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