求助：如何创建动态参数更新的存储过程？现有PREPARE语句报错

要实现仅根据传入的非空参数更新客户数据，核心是动态拼接SQL语句并处理边界情况，具体实现如下：

• 定义存储过程时，将所有更新字段参数设为允许NULL（代表不需要更新该字段）
• 动态拼接UPDATE语句，仅包含非空参数对应的字段
• 处理拼接后的语句末尾多余逗号，避免语法错误
• 使用参数占位符?绑定参数，防止SQL注入
• 添加无更新字段的判断，避免执行无效SQL

示例代码：

DELIMITER //
CREATE PROCEDURE updateCustomer(
    IN customer_id INT,
    IN new_name VARCHAR(100) NULL,
    IN new_email VARCHAR(100) NULL,
    IN new_phone VARCHAR(20) NULL
)
BEGIN
    DECLARE sql_stmt TEXT DEFAULT 'UPDATE customers SET ';
    DECLARE param_count INT DEFAULT 0;

    -- 拼接非空字段的更新语句
    IF new_name IS NOT NULL THEN
        SET sql_stmt = CONCAT(sql_stmt, 'name = ?, ');
        SET param_count = param_count + 1;
    END IF;
    IF new_email IS NOT NULL THEN
        SET sql_stmt = CONCAT(sql_stmt, 'email = ?, ');
        SET param_count = param_count + 1;
    END IF;
    IF new_phone IS NOT NULL THEN
        SET sql_stmt = CONCAT(sql_stmt, 'phone = ?, ');
        SET param_count = param_count + 1;
    END IF;

    -- 无字段更新时直接返回提示
    IF param_count = 0 THEN
        SELECT 'No fields to update' AS result;
        LEAVE;
    END IF;

    -- 移除末尾多余的逗号和空格
    SET sql_stmt = LEFT(sql_stmt, LENGTH(sql_stmt) - 2);
    -- 添加WHERE条件
    SET sql_stmt = CONCAT(sql_stmt, ' WHERE customer_id = ?');

    -- 根据参数数量绑定对应参数并执行
    PREPARE stmt FROM sql_stmt;
    CASE param_count
        WHEN 1 THEN
            IF new_name IS NOT NULL THEN
                EXECUTE stmt USING new_name, customer_id;
            ELSEIF new_email IS NOT NULL THEN
                EXECUTE stmt USING new_email, customer_id;
            ELSE
                EXECUTE stmt USING new_phone, customer_id;
            END IF;
        WHEN 2 THEN
            IF new_name IS NOT NULL AND new_email IS NOT NULL THEN
                EXECUTE stmt USING new_name, new_email, customer_id;
            ELSEIF new_name IS NOT NULL AND new_phone IS NOT NULL THEN
                EXECUTE stmt USING new_name, new_phone, customer_id;
            ELSE
                EXECUTE stmt USING new_email, new_phone, customer_id;
            END IF;
        WHEN 3 THEN
            EXECUTE stmt USING new_name, new_email, new_phone, customer_id;
    END CASE;
    DEALLOCATE PREPARE stmt;
END //
DELIMITER ;

你的存储过程报错主要由以下几个问题导致：

1. 变量长度不足：DECLARE mySql VARCHAR(100);定义的长度过短，拼接后的SQL会被截断，引发语法错误，需改为TEXT类型。
2. 直接拼接参数名：例如'first_name = f_name'会将f_name当作字符串字面量，而非存储过程参数，导致逻辑错误且存在SQL注入风险，需用占位符?绑定参数。
3. 未处理末尾多余逗号：非pass的参数非空时，拼接后的SQL会出现..., WHERE的语法错误。
4. 无更新字段的边界情况：所有参数为空时，会生成无效SQL语句。

修正后的代码：

DELIMITER //
CREATE PROCEDURE updateStaff 
(
    IN id tinyint, 
    IN f_name VARCHAR(45) NULL, 
    IN l_name VARCHAR(45) NULL, 
    IN address tinyint NULL, 
    IN pic blob NULL, 
    IN emai VARCHAR(50) NULL,
    IN store tinyint NULL, 
    IN isActive tinyint NULL, 
    IN userN VARCHAR(16) NULL, 
    IN pass VARCHAR(50) NULL
 )

BEGIN
    DECLARE sql_stmt TEXT DEFAULT 'UPDATE staff SET ';
    DECLARE param_list TEXT DEFAULT '';
    DECLARE param_count INT DEFAULT 0;

    -- 拼接字段和占位符，记录需要传递的参数
    IF f_name IS NOT NULL THEN
        SET sql_stmt = CONCAT(sql_stmt, 'first_name = ?, ');
        SET param_list = CONCAT(param_list, 'f_name, ');
        SET param_count = param_count + 1;
    END IF;

    IF l_name IS NOT NULL THEN
        SET sql_stmt = CONCAT(sql_stmt, 'last_name = ?, '); 
        SET param_list = CONCAT(param_list, 'l_name, ');
        SET param_count = param_count + 1;
    END IF;

    IF address IS NOT NULL THEN
        SET sql_stmt = CONCAT(sql_stmt, 'address_id = ?, ');
        SET param_list = CONCAT(param_list, 'address, ');
        SET param_count = param_count + 1;
    END IF;

    IF pic IS NOT NULL THEN
        SET sql_stmt = CONCAT(sql_stmt, 'picture = ?, ');
        SET param_list = CONCAT(param_list, 'pic, ');
        SET param_count = param_count + 1;
    END IF;

    IF emai IS NOT NULL THEN
        SET sql_stmt = CONCAT(sql_stmt, 'email = ?, ');
        SET param_list = CONCAT(param_list, 'emai, ');
        SET param_count = param_count + 1;
    END IF;

    IF store IS NOT NULL THEN
        SET sql_stmt = CONCAT(sql_stmt, 'store_id = ?, ');
        SET param_list = CONCAT(param_list, 'store, ');
        SET param_count = param_count + 1;
    END IF;

    IF isActive IS NOT NULL THEN
        SET sql_stmt = CONCAT(sql_stmt, 'active = ?, ');
        SET param_list = CONCAT(param_list, 'isActive, ');
        SET param_count = param_count + 1;
    END IF;

    IF userN IS NOT NULL THEN 
        SET sql_stmt = CONCAT(sql_stmt, 'username = ?, ');
        SET param_list = CONCAT(param_list, 'userN, ');
        SET param_count = param_count + 1;
    END IF;

    IF pass IS NOT NULL THEN
        SET sql_stmt = CONCAT(sql_stmt, 'password = ?, ');
        SET param_list = CONCAT(param_list, 'pass, ');
        SET param_count = param_count + 1;
    END IF;

    -- 无字段更新时返回提示
    IF param_count = 0 THEN
        SELECT 'No fields specified for update' AS error_msg;
        LEAVE;
    END IF;

    -- 移除SQL语句末尾的逗号和空格
    SET sql_stmt = LEFT(sql_stmt, LENGTH(sql_stmt) - 2);
    -- 添加WHERE条件，绑定id参数
    SET sql_stmt = CONCAT(sql_stmt, ' WHERE staff_id = ?');
    SET param_list = CONCAT(param_list, 'id');

    -- 预处理并执行动态SQL
    SET @sql = sql_stmt;
    SET @params = CONCAT('USING ', param_list);
    PREPARE stmt FROM @sql;
    -- 动态生成参数绑定语句并执行
    SET @exec_stmt = CONCAT('EXECUTE stmt ', @params);
    PREPARE exec_stmt FROM @exec_stmt;
    EXECUTE exec_stmt;
    DEALLOCATE PREPARE exec_stmt;

    DEALLOCATE PREPARE stmt;
END//
DELIMITER ;

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