动态MySQL PIVOT透视表脚本无法在PHP中运行的解决方法

你这段动态PIVOT脚本在PHP中执行失败，核心是两个原因：

• PHP自带的MySQL操作扩展（MySQLi、PDO）默认单次查询调用仅支持执行1条独立SQL语句，你把5条独立SQL（生成动态列到变量、拼接完整查询、预处理、执行、释放资源）拼在一起直接传入查询方法，会被数据库接口直接判定语法错误。
• MySQL的用户变量（比如你用的@sql）是连接会话级别的，如果执行过程中出现连接断开、重连，变量会直接丢失，后续语句无法正常运行。

不要用多语句执行接口一次性跑完整段脚本，逐句在同一个数据库连接下执行即可，两种常用扩展的实现代码如下：

基于MySQLi扩展实现 #

// 初始化数据库连接
$conn = mysqli_connect('数据库主机地址', '数据库账号', '数据库密码', '目标库名');
mysqli_set_charset($conn, 'utf8mb4');
// 调大GROUP_CONCAT长度限制，避免动态列被截断
mysqli_query($conn, "SET SESSION group_concat_max_len = 102400;");

// 第一步：生成动态科目列，存入会话变量@sql
mysqli_query($conn, "SELECT
      GROUP_CONCAT(DISTINCT
        CONCAT(
          'MAX(IF(subjects.subject_name = ''',
      subject_name,
      ''', term_averages.student_average, NULL)) AS ',
      replace(subjects.subject_name, ' ', '')
        )
      ) INTO @sql
    from subjects;");

// 第二步：拼接完整的透视查询SQL
mysqli_query($conn, "SET @sql = CONCAT('SELECT 
 student_subject_averages.student_id,
        grades.grade_name,
        users.id as learner_id,
        users.name,
        users.middlename,
        users.lastname,
        term_averages.student_average,
        CASE WHEN term_averages.number_of_passed_subjects>=5  AND term_averages.student_average>=50 THEN \"Passed\" ELSE \"Failed\" END AS \"remark\",
', @sql,'
  FROM student_subject_averages INNER JOIN teaching_loads ON teaching_loads.id=student_subject_averages.teaching_load_id
        INNER JOIN subjects ON teaching_loads.subject_id=subjects.id
        INNER JOIN users ON users.id=student_subject_averages.student_id
        INNER JOIN grades_students ON grades_students.student_id=student_subject_averages.student_id
        INNER JOIN grades ON grades_students.grade_id=grades.id
        INNER JOIN term_averages ON term_averages.student_id=student_subject_averages.student_id
        WHERE grades.stream_id=1 AND student_subject_averages.term_id=4 AND term_averages.term_id=4 
        GROUP BY student_subject_averages.student_id  
        ORDER BY term_averages.student_average DESC');");

// 第三步：预处理动态SQL
mysqli_query($conn, "PREPARE stmt FROM @sql;");

// 第四步：执行查询，拿到结果集
$result = mysqli_query($conn, "EXECUTE stmt;");

// 第五步：释放预处理资源
mysqli_query($conn, "DEALLOCATE PREPARE stmt;");

// 读取结果数据
$list = [];
while ($row = mysqli_fetch_assoc($result)) {
    $list[] = $row;
}

// 释放结果集、关闭连接
mysqli_free_result($result);
mysqli_close($conn);

基于PDO扩展实现 #

// 初始化数据库连接
$pdo = new PDO('mysql:host=数据库主机地址;dbname=目标库名;charset=utf8mb4', '数据库账号', '数据库密码', [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
]);
// 调大GROUP_CONCAT长度限制
$pdo->exec("SET SESSION group_concat_max_len = 102400;");

// 逐句执行SQL
$pdo->exec("SELECT
      GROUP_CONCAT(DISTINCT
        CONCAT(
          'MAX(IF(subjects.subject_name = ''',
      subject_name,
      ''', term_averages.student_average, NULL)) AS ',
      replace(subjects.subject_name, ' ', '')
        )
      ) INTO @sql
    from subjects;");

$pdo->exec("SET @sql = CONCAT('SELECT 
 student_subject_averages.student_id,
        grades.grade_name,
        users.id as learner_id,
        users.name,
        users.middlename,
        users.lastname,
        term_averages.student_average,
        CASE WHEN term_averages.number_of_passed_subjects>=5  AND term_averages.student_average>=50 THEN \"Passed\" ELSE \"Failed\" END AS \"remark\",
', @sql,'
  FROM student_subject_averages INNER JOIN teaching_loads ON teaching_loads.id=student_subject_averages.teaching_load_id
        INNER JOIN subjects ON teaching_loads.subject_id=subjects.id
        INNER JOIN users ON users.id=student_subject_averages.student_id
        INNER JOIN grades_students ON grades_students.student_id=student_subject_averages.student_id
        INNER JOIN grades ON grades_students.grade_id=grades.id
        INNER JOIN term_averages ON term_averages.student_id=student_subject_averages.student_id
        WHERE grades.stream_id=1 AND student_subject_averages.term_id=4 AND term_averages.term_id=4 
        GROUP BY student_subject_averages.student_id  
        ORDER BY term_averages.student_average DESC');");

$pdo->exec("PREPARE stmt FROM @sql;");
$stmt = $pdo->query("EXECUTE stmt;");
$list = $stmt->fetchAll(PDO::FETCH_ASSOC);
$pdo->exec("DEALLOCATE PREPARE stmt;");

• 不要使用multi_query等多语句执行接口一次性跑完整段脚本，会大幅提升SQL注入风险，逐句执行更可控。
• 所有语句必须复用同一个连接实例，中途不要主动关闭连接、触发重连逻辑，否则会话变量@sql会被清空，执行报错。
• 如果科目数量很多，一定要先调大group_concat_max_len参数，否则动态列拼接会被截断，最终生成的SQL出现语法错误。

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