MySQL存储过程动态WHERE子句带参执行异常排查

代码存在4处明确问题，直接导致执行报错或筛选逻辑失效：

• 硬语法错误：拼接最终查询语句的CONCAT函数未闭合右括号，执行到PREPARE阶段会直接抛出语法异常。
• 逻辑错误：拼接WHERE条件时直接将参数名写在SQL字符串内，例如"AND is_payment IN (isPayment)"，动态SQL执行时会将isPayment识别为表字段，不会替换为传入的参数值；字符串类型参数因缺少引号包裹，还会触发「未知列」报错。
• 逻辑错误：用于生成动态行转列的首个CTE直接查询全表数据统计附加项/问题序号，加筛选条件后会生成多余的空值列；同时最终查询语句缺失样例中返回的item_id、amount字段，和预期输出不匹配。
• 稳定性风险：GROUP_CONCAT默认长度限制为1024字节，附加项/问题数量较多时拼接内容会被截断，触发SQL语法错误。

无参调用预期返回样例 #

修正后完整存储过程代码 #

DELIMITER $$

CREATE PROCEDURE `ReportAdditionals`(
    IN `isPayment` TINYINT(1), 
    IN `postTitle` TEXT, 
    IN `optionName` TEXT, 
    IN `askUser` TEXT
)
BEGIN
    -- 调整GROUP_CONCAT长度限制，避免拼接内容截断
    SET SESSION group_concat_max_len = 10240;

    -- 拼接动态WHERE条件，用QUOTE函数自动处理参数转义、引号包裹，避免语法错误和SQL注入
    SET @where = ' WHERE 1=1 ';
    IF isPayment IS NOT NULL THEN
        SET @where = CONCAT(@where, ' AND is_payment = ', QUOTE(isPayment));
    END IF;
    IF postTitle IS NOT NULL THEN
        SET @where = CONCAT(@where, ' AND post_title = ', QUOTE(postTitle));
    END IF;
    IF optionName IS NOT NULL THEN
        SET @where = CONCAT(@where, ' AND additional_option_name = ', QUOTE(optionName));
    END IF;
    IF askUser IS NOT NULL THEN
        SET @where = CONCAT(@where, ' AND ask_user = ', QUOTE(askUser));
    END IF;

    -- 基于筛选条件生成基础CTE，后续动态列从筛选后的数据中提取序号，避免生成多余空列
    SET @cte_base = 'WITH cte_raw AS (
        SELECT 
            post_title, 
            event_items.id AS item_id,
            users.id AS user_id, 
            name, 
            surname, 
            additional_option_name, 
            ask_user, 
            additional_option_price AS amount,
            is_payment,
            ROW_NUMBER() OVER(
                PARTITION BY name, surname 
                ORDER BY IF(additional_option_name IS NULL, 1, 0), post_title
            ) AS rn_add, 
            ROW_NUMBER() OVER(
                PARTITION BY name, surname 
                ORDER BY IF(ask_user IS NULL, 1, 0), post_title
            ) AS rn_qst
        FROM users
        LEFT JOIN event_items 
            ON users.id = event_items.user_id
        LEFT JOIN event_items_additional
            ON event_items.id = event_items_additional.event_items_id
        LEFT JOIN event_items_ask_user 
            ON event_items.id = event_items_ask_user.event_items_id
        ', @where, '
    ), cte AS (
        SELECT DISTINCT rn_add AS idx FROM cte_raw WHERE rn_add IS NOT NULL
        UNION
        SELECT DISTINCT rn_qst AS idx FROM cte_raw WHERE rn_qst IS NOT NULL
    )';

    -- 生成动态行转列字段
    SELECT GROUP_CONCAT(
               CONCAT(
                   'MAX(IF(rn_add = ', cte.idx, ', additional_option_name, NULL)) AS addition', cte.idx, ',',
                   'MAX(IF(rn_qst = ', cte.idx, ', ask_user, NULL)) AS question', cte.idx
               ) ORDER BY cte.idx
           ) INTO @sql_col
    FROM cte;

    -- 拼接最终查询SQL，补全闭合括号、缺失的返回字段
    SET @sql = CONCAT(
        @cte_base,
        ' SELECT 
            user_id AS ID,
            item_id,
            name,
            surname,',
            @sql_col,
            ',SUM(amount) AS amount 
        FROM cte_raw
        GROUP BY user_id, item_id, name, surname'
    );

    -- 执行动态SQL
    PREPARE stmt1 FROM @sql;
    EXECUTE stmt1;
    DEALLOCATE PREPARE stmt1;
END$$
DELIMITER ;

注：原代码中users和event_items的关联条件写为users.id = event_items.id，不符合常规表结构设计逻辑，上述代码按通用关联逻辑改为users.id = event_items.user_id，如果你的业务场景确实是用id直接关联，修改对应JOIN条件即可。如果参数需要支持多值匹配（例如同时传入多个postTitle筛选），需要额外增加字符串拆分逻辑，当前代码按单值等值匹配实现。

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