如何在MySQL中创建支持动态选择列更新的存储过程？

针对你提出的「创建可灵活选择更新列的存储过程」需求，以下是几种成熟的解决方案，既支持设置列值为NULL，又能避免SQL注入风险：

方案1：参数+标志位模式 #

为每个可选更新的列额外添加布尔类型的标志参数，明确标记是否要更新该列。这种方式完全避免动态SQL，逻辑清晰且无注入风险。

示例代码（MySQL）：

CREATE PROCEDURE flexible_update_table (
    IN p_id INT, -- 用于WHERE条件的主键
    IN p_column1 VARCHAR(50),
    IN p_update_column1 BOOLEAN,
    IN p_column2 INT,
    IN p_update_column2 BOOLEAN,
    IN p_column3 DATE,
    IN p_update_column3 BOOLEAN
)
BEGIN
    UPDATE table_name
    SET
        column1 = CASE WHEN p_update_column1 THEN p_column1 ELSE column1 END,
        column2 = CASE WHEN p_update_column2 THEN p_column2 ELSE column2 END,
        column3 = CASE WHEN p_update_column3 THEN p_column3 ELSE column3 END
    WHERE id = p_id;
END;

调用示例：

-- 仅更新column1（设为NULL）和column3
CALL flexible_update_table(1, NULL, TRUE, 0, FALSE, '2024-05-20', TRUE);

优缺点：无注入风险，逻辑直观；但列较多时参数数量翻倍，调用时需要传递更多参数。

方案2：安全的动态SQL实现 #

如果觉得标志位参数过多，可以使用动态SQL，但必须通过参数绑定避免注入，绝对不能直接拼接参数值到SQL语句中。结合标志位参数，还能支持将列设置为NULL的场景。

示例代码（MySQL）：

CREATE PROCEDURE dynamic_safe_update_table (
    IN p_id INT,
    IN p_column1 VARCHAR(50),
    IN p_update_column1 BOOLEAN,
    IN p_column2 INT,
    IN p_update_column2 BOOLEAN,
    IN p_column3 DATE,
    IN p_update_column3 BOOLEAN
)
BEGIN
    DECLARE sql_stmt VARCHAR(1000);
    DECLARE set_clause VARCHAR(800) DEFAULT '';
    DECLARE param_list JSON DEFAULT '[]';

    -- 构建SET子句和参数列表
    IF p_update_column1 THEN
        SET set_clause = CONCAT(set_clause, 'column1 = ?, ');
        SET param_list = JSON_ARRAY_APPEND(param_list, '$', p_column1);
    END IF;
    IF p_update_column2 THEN
        SET set_clause = CONCAT(set_clause, 'column2 = ?, ');
        SET param_list = JSON_ARRAY_APPEND(param_list, '$', p_column2);
    END IF;
    IF p_update_column3 THEN
        SET set_clause = CONCAT(set_clause, 'column3 = ?, ');
        SET param_list = JSON_ARRAY_APPEND(param_list, '$', p_column3);
    END IF;

    -- 无更新列时直接返回
    IF set_clause = '' THEN
        LEAVE;
    END IF;

    -- 移除SET子句末尾的逗号
    SET set_clause = LEFT(set_clause, LENGTH(set_clause) - 2);
    SET sql_stmt = CONCAT('UPDATE table_name SET ', set_clause, ' WHERE id = ?');
    -- 添加主键参数
    SET param_list = JSON_ARRAY_APPEND(param_list, '$', p_id);

    -- 预处理并执行语句
    PREPARE stmt FROM sql_stmt;
    CASE JSON_LENGTH(param_list)
        WHEN 2 THEN EXECUTE stmt USING JSON_UNQUOTE(JSON_EXTRACT(param_list, '$[0]')), JSON_UNQUOTE(JSON_EXTRACT(param_list, '$[1]'));
        WHEN 3 THEN EXECUTE stmt USING JSON_UNQUOTE(JSON_EXTRACT(param_list, '$[0]')), JSON_UNQUOTE(JSON_EXTRACT(param_list, '$[1]')), JSON_UNQUOTE(JSON_EXTRACT(param_list, '$[2]'));
        WHEN 4 THEN EXECUTE stmt USING JSON_UNQUOTE(JSON_EXTRACT(param_list, '$[0]')), JSON_UNQUOTE(JSON_EXTRACT(param_list, '$[1]')), JSON_UNQUOTE(JSON_EXTRACT(param_list, '$[2]')), JSON_UNQUOTE(JSON_EXTRACT(param_list, '$[3]'));
    END CASE;
    DEALLOCATE PREPARE stmt;
END;

优缺点：参数数量比纯标志位模式精简，无注入风险；但动态SQL的编写和维护复杂度较高，不同数据库的预处理语法存在差异。

方案3：JSON参数传递更新字段 #

将需要更新的列和值打包成JSON参数传入，解析JSON后构建安全的动态SQL，同时验证列名的合法性进一步规避注入风险。

示例代码（MySQL）：

CREATE PROCEDURE json_based_update_table (
    IN p_id INT,
    IN p_updates JSON
)
BEGIN
    DECLARE sql_stmt VARCHAR(1000);
    DECLARE set_clause VARCHAR(800) DEFAULT '';
    DECLARE param_list JSON DEFAULT '[]';
    DECLARE column_name VARCHAR(50);
    DECLARE column_value JSON;
    DECLARE done INT DEFAULT FALSE;

    -- 游标遍历JSON中的键值对
    DECLARE cur CURSOR FOR 
        SELECT `key`, value 
        FROM JSON_TABLE(p_updates, '$.*' COLUMNS(`key` VARCHAR(50) PATH '$[0]', value JSON PATH '$[1]')) AS jt;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN cur;
    read_loop: LOOP
        FETCH cur INTO column_name, column_value;
        IF done THEN
            LEAVE read_loop;
        END IF;
        -- 验证列名是否属于目标表，防止恶意列名注入
        IF EXISTS (
            SELECT 1 
            FROM INFORMATION_SCHEMA.COLUMNS 
            WHERE TABLE_NAME = 'table_name' AND COLUMN_NAME = column_name
        ) THEN
            SET set_clause = CONCAT(set_clause, column_name, ' = ?, ');
            SET param_list = JSON_ARRAY_APPEND(param_list, '$', JSON_UNQUOTE(column_value));
        END IF;
    END LOOP;
    CLOSE cur;

    IF set_clause = '' THEN
        LEAVE;
    END IF;

    SET set_clause = LEFT(set_clause, LENGTH(set_clause) - 2);
    SET sql_stmt = CONCAT('UPDATE table_name SET ', set_clause, ' WHERE id = ?');
    SET param_list = JSON_ARRAY_APPEND(param_list, '$', p_id);

    -- 执行预处理语句
    PREPARE stmt FROM sql_stmt;
    CASE JSON_LENGTH(param_list)
        WHEN 2 THEN EXECUTE stmt USING JSON_UNQUOTE(JSON_EXTRACT(param_list, '$[0]')), JSON_UNQUOTE(JSON_EXTRACT(param_list, '$[1]'));
        WHEN 3 THEN EXECUTE stmt USING JSON_UNQUOTE(JSON_EXTRACT(param_list, '$[0]')), JSON_UNQUOTE(JSON_EXTRACT(param_list, '$[1]')), JSON_UNQUOTE(JSON_EXTRACT(param_list, '$[2]'));
    END CASE;
    DEALLOCATE PREPARE stmt;
END;

调用示例：

-- 更新column1为NULL，column2为100
CALL json_based_update_table(1, '{"column1": null, "column2": 100}');

优缺点：调用时参数简洁，支持任意列组合更新（包括设置NULL）；但需要数据库支持JSON函数（如MySQL 5.7+、PostgreSQL 9.4+），游标和JSON解析逻辑相对复杂。

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