MySQL UDF/存储函数无法识别作为参数传递的表名

Question

以下是存储的函数:

DELIMITER //
CREATE FUNCTION `calcMedian`(
    `tbl` VARCHAR(64),
    `clm` VARCHAR(64)
) RETURNS decimal(14,4)
BEGIN

    SELECT AVG(middle_values) AS 'median'
    INTO medRslt
    FROM (
        SELECT t1.clm AS 'middle_values'
        FROM
        (
            SELECT @row:=@row+1 as `row`, table_column_name
            FROM tbl, (SELECT @row:=0) AS r
            ORDER BY clm
        ) AS t1,
        (
            SELECT COUNT(*) as 'count'
            FROM tbl        
        ) AS t2

        WHERE t1.row >= t2.count/2 and t1.row <= ((t2.count/2) +1)) AS t3;  
    RETURN medRslt; 
END//
DELIMITER ;

然后我继续执行以下查询:

USE ap2;

SELECT vendor_id, calcMedian('invoices', 'invoice_total')
FROM invoices i
WHERE vendor_id = 97
GROUP BY vendor_id;

我收到错误消息:

SQL Error (1146): Table 'ap2.tbl' doesn't exist *

我知道以下内容作为存储过程/准备好的语句可能比函数更好。我现在只想一步一个脚印。

我还制作了一个不同的函数来简单地输出存储在变量“tbl”中的值，并且它显示了正确的表名称(在本例中为发票)。

Answer 1

SQL 语句中的标识符不能作为值提供。标识符(表名、列名、函数名等)必须在 SQL 文本中指定。

要获取在过程的 SQL 语句中用作表名的 tbl 变量(过程参数)的值，您可以使用动态 SQL。

将变量设置为 SQL 文本，合并字符串值，然后将字符串作为 SQL 语句执行。举个例子:

  SET @sql = CONCAT( 'SELECT AVG(middle_values) AS `median`'
                   , ' INTO medRslt'
                   , ' ... '
                   , tbl
                   , ' ... '
             );
  PREPARE stmt FROM @sql;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;

<小时/>

请注意，将字符串值合并到 SQL 文本中会使过程受到 SQL 注入(inject)漏洞的影响。

如果我必须这样做，我会通过验证 tbl 不包含反引号字符来减少 SQL 注入(inject)的可能性，并将标识符括在反引号中/转义，例如

   CONCAT( ' ...' , '`' , tbl , '`' , ' ... ' );
                    ^^^         ^^^