如何在MySQL存储过程中传入UPPER(c.Name)作为参数？

现有如下MySQL存储过程及调用语句：

CREATE DEFINER=`newuser`@`localhost` PROCEDURE `spSelectAllCustomers`(
IN customRole VARCHAR(30))
BEGIN
IF customRole IS NULL THEN
SELECT * FROM  db.Customers AS c;
ELSE
SELECT customRole FROM db.Customers AS c; 
END IF;
END

call db.spSelectAllCustomers('UPPER(c.CustomerName)');

期望通过参数传递UPPER(c.CustomerName)，执行SELECT UPPER(c.CustomerName) FROM Customers AS c;这类查询，但当前调用后，返回的是字符串'UPPER(c.CustomerName)'的字面量，而非函数执行后的大写用户名。

要实现动态传入字段或函数表达式作为查询项，必须使用动态SQL拼接语句并执行——静态SQL会将参数直接当作字符串值处理，无法解析执行表达式。修改后的存储过程如下：

CREATE DEFINER=`newuser`@`localhost` PROCEDURE `spSelectAllCustomers`(
IN customRole VARCHAR(30))
BEGIN
    DECLARE sql_stmt VARCHAR(255);
    IF customRole IS NULL THEN
        SET sql_stmt = 'SELECT * FROM db.Customers AS c';
    ELSE
        SET sql_stmt = CONCAT('SELECT ', customRole, ' FROM db.Customers AS c');
    END IF;
    PREPARE stmt FROM sql_stmt;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END

调用方式保持不变：

call db.spSelectAllCustomers('UPPER(c.CustomerName)');

注意事项 #

• 动态SQL存在SQL注入风险，若参数来自不可信来源，需做严格校验或转义处理。
• 确保传入的customRole是合法的SQL表达式，避免出现语法错误。

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