MariaDB存储过程参数被误判为表字段的问题及解决

在Drupal使用的MariaDB 10.4.17环境中，需要实现以下逻辑：当目标表存在，且表中某字段值为html时，替换指定字段内的特定字符串。

最初编写的存储过程执行时抛出错误Unknown column 'my_table' in 'field list'，系统误将传入的表名参数当作字段查找，原代码如下：

USE myDatabase;

DELIMITER $$

CREATE PROCEDURE replaceLinks(
    tableName VARCHAR(255),
    fieldValue VARCHAR(255),
    fieldFormat VARCHAR(255)
)
BEGIN
    IF EXISTS (
        SELECT *
        FROM tableName
    ) THEN
        UPDATE tableName
        SET fieldValue = REPLACE(
                fieldValue,
                'TARGET_STR',
                'REPLACE_STR'
            )
        WHERE fieldFormat = 'html'
    END IF
END

DELIMITER ;

CALL replaceLinks(
    my_table field_value field_format
);

错误原因分析 #

• 直接将存储过程参数用作表名、字段名：静态SQL会把这些参数解析为字段名，而非动态的表/字段标识
• 调用存储过程时未给字符串参数添加引号：导致参数无法被正确识别为字符串类型

修正后的实现代码 #

通过**动态SQL（EXECUTE IMMEDIATE）**拼接SQL语句，并添加错误处理器处理表不存在（错误码1146）、字段不存在（错误码1054）的情况，最终可用代码如下：

USE myDatabase;

DELIMITER $$

CREATE OR REPLACE PROCEDURE replaceLinks(
        tableName VARCHAR(255),
        fieldValue VARCHAR(255),
        fieldFormat VARCHAR(255)
    ) 
    BEGIN
        DECLARE EXIT HANDLER FOR 1146 SELECT 1;
        DECLARE EXIT HANDLER FOR 1054 SELECT 1;
        EXECUTE IMMEDIATE CONCAT(
            'UPDATE ',
            tableName,
            ' SET ',
            fieldValue,
            ' = REPLACE(',
            fieldValue,
            ',\'TARGET_STR\', \'REPLACE_STR\') WHERE ',
            fieldFormat,
            ' = \'html\''
        );
    END
$$

DELIMITER ;

CALL replaceLinks(
        'my_table', 'field_value', 'field_format'
    );

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