问题

我有一个Drupal数据库（MariaDB 10.4.17），我试图只在以下情况下替换字段中的字符串：

1. 目标表存在；以及
2. 字段中的一个值包含特定值

然而，当我运行这段代码时，它会抛出错误“未知列'my_table'在'字段列表'中”，从我所读到的内容来看，这似乎是在表内查找一个具有该名称的*字段*，但找不到它。

这是为什么，以及我如何使代码在`THEN`块中仅在表存在时才执行？

---

**更新**

我尝试了@danblack的答案，无论我传递的表名是否存在，都会得到相同的错误：
“未知列'my_table'在'字段列表'中”。

看起来有两个问题：

1. 我试图使用过程参数作为字段名的变量，正如Dan所指出的；
2. 我还传递了没有引号的参数，尽管它们是字符串，所以它们没有被解释为字符串。

现在它完全按预期工作：
```sql
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'
    );

<details>
<summary>英文:</summary>

I&#39;ve got a Drupal database (MariaDB 10.4.17), and I&#39;m trying to replace a string in a field only if:

 1. The target table exists; and
 2. One of the field values within contains a specific value

However when I run this, it throws the error `Unknown column &#39;my_table&#39; in &#39;field list&#39;`, which from what I&#39;ve read suggests it is looking within a table for a *field* with that name and can&#39;t find it.

Why is this happening, and how can I make the code in the `THEN` block execute on a table only if it exists?

```sql
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,
                &#39;TARGET_STR&#39;,
                &#39;REPLACE_STR&#39;
            )
        WHERE fieldFormat = &#39;html&#39;
    END IF
END

DELIMITER ;

CALL replaceLinks(
    my_table field_value field_format
);

UPDATE

I tried with @danblack's answer, and still get the same error whether the table name I pass existed or not:
Unknown column 'my_table' in 'field list'.

It looks like there was 2 issues at work:

1. I was trying to use procedure parameters as variables for field names, as Dan noted; and
2. I was also passing the parameters without quotes despite them being strings, so they were not interpreted as such.

This now works exactly as intended:

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'
    );

答案1

得分: 3

存储过程参数不能用作字段名的变量 SQL 语法。

你可以使用 EXECUTE IMMEDIATE 的魔法来构建一个查询：

CREATE OR REPLACE PROCEDURE replaceLinks(tableName VARCHAR(255),
  fieldValue VARCHAR(255),
  fieldFormat VARCHAR(255) )
EXECUTE IMMEDIATE CONCAT('UPDATE ',
  tableName,
  ' SET ',
  fieldValue,
  ' = REPLACE(',
  fieldValue,
  ',\'TARGET_STR\', \'REPLACE_STR\') WHERE ',
  fieldFormat,
  ' = \'html\'');

处理未找到的情况可以使用 DECLARE HANDLER。

BEGIN
DECLARE EXIT HANDLER FOR 1146 SELECT 1;
EXECUTE IMMEDIATE ....;
END$$

CREATE OR REPLACE PROCEDURE replaceLinks(tableName VARCHAR(255),
  fieldValue VARCHAR(255),
  fieldFormat VARCHAR(255) )
EXECUTE IMMEDIATE CONCAT('UPDATE ',
  tableName,
  ' SET ',
  fieldValue,
  ' = REPLACE(',
  fieldValue,
  ',\'TARGET_STR\', \'REPLACE_STR\') WHERE ',
  fieldFormat,
  ' = \'html\'');

BEGIN
DECLARE EXIT HANDLER FOR 1146 SELECT 1;
EXECUTE IMMEDIATE ....;
END$$