MySQL存储过程能否支持多类型IN参数（INT/VARCHAR）？

我想编写一个存储过程，调用时能根据传入的信息更新表中指定列。这个存储过程需要接收待更新列名和对应值，但表中部分列是INT类型，部分是VARCHAR类型。请问能不能用一个存储过程实现，还是得分别给VARCHAR和INT列写两个存储过程？

我知道示例里的写法行不通，就是想看看有没有其他开发者遇到过类似问题，以及MySQL里的变通方案。

用户提供的示例代码：

CREATE PROCEDURE updateColumn(
    IN an_id,
    IN a_column_name VARCHAR,
    IN a_column_value VARCHAR || INT
)
BEGIN
    UPDATE foo
    SET a_column_name = a_column_value
    WHERE id = id
END $$

可以用单个存储过程实现，核心是借助动态SQL结合列类型自动转换来处理不同数据类型的字段。具体实现思路如下：

• 先查询目标列的数据类型，明确需要转换的方向
• 根据列类型拼接对应的SQL语句，对传入的值做适配处理
• 执行拼接好的动态SQL

以下是可直接使用的代码示例：

DELIMITER $$
CREATE PROCEDURE updateColumn(
    IN p_id INT,
    IN p_column_name VARCHAR(64),
    IN p_column_value VARCHAR(255) -- 统一用VARCHAR接收输入，后续按需转换
)
BEGIN
    DECLARE col_type VARCHAR(64);
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '更新失败，请检查参数或列名';
    END;

    -- 获取目标列的数据类型
    SELECT DATA_TYPE INTO col_type
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE() 
      AND TABLE_NAME = 'foo' 
      AND COLUMN_NAME = p_column_name;

    -- 校验列是否存在
    IF col_type IS NULL THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '指定的列不存在';
    END IF;

    -- 拼接动态UPDATE语句，根据列类型处理值
    SET @sql = CONCAT(
        'UPDATE foo SET ', 
        p_column_name, ' = ',
        CASE 
            WHEN col_type IN ('INT', 'TINYINT', 'SMALLINT', 'MEDIUMINT', 'BIGINT') 
                THEN CAST(p_column_value AS UNSIGNED) -- 转换为整数类型
            WHEN col_type IN ('VARCHAR', 'CHAR', 'TEXT', 'LONGTEXT') 
                THEN QUOTE(p_column_value) -- 字符串类型添加引号，防止SQL注入
            WHEN col_type IN ('DATE', 'DATETIME', 'TIMESTAMP') 
                THEN QUOTE(p_column_value) -- 日期类型同样用引号包裹
            WHEN col_type IN ('DECIMAL', 'FLOAT', 'DOUBLE') 
                THEN p_column_value -- 数值直接传入
            ELSE QUOTE(p_column_value) -- 其他类型默认按字符串处理
        END,
        ' WHERE id = ', p_id
    );

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

注意事项 #

• SQL注入防护：虽然用了QUOTE()函数处理字符串，但代码中已通过查询INFORMATION_SCHEMA校验列的合法性，进一步降低注入风险
• 类型扩展：如果表中有其他数据类型，可在CASE分支中添加对应的处理逻辑
• 异常处理：代码中添加了异常捕获，可根据实际需求调整错误提示内容

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