mysql - 如何将变量传递给 UPDATE mysql 查询

Question

我想用以下命令替换表中的值 (1 > -1)更新表名 SET column_name = replace(column_name, '1', '-1');

但是，我决定学习如何在 Mysql 中使用存储程序，因为列的数量很大，而且它们的名称格式很规则('i01'，'i02'，...)。以下是我的试用:

---

DELIMITER $$
DROP PROCEDURE IF EXISTS example$$
CREATE PROCEDURE example()
BEGIN
DECLARE p INT;
DECLARE str VARCHAR(20);
SET p = 1;

    WHILE p < 100 DO
     IF p <= 9 THEN SET str = CONCAT('i0', p);
     ELSE SET str = CONCAT('i', p);
     END IF;
    UPDATE target_table 
    SET `str` = replace(str, '1', '-1');
    SET p = p + 1; 
    END WHILE; 
END$$

---

当我获取脚本并调用该函数时，出现错误 1054 (42S22):“字段列表”中的未知列“str”。如何在 UPDATE 查询中传递变量(在本例中为 @str)？我在网上搜索并发现 PREPARE 可能是一个答案，但无法弄清楚如何在我的案例中使用它。

Answer 1

是的，您使用 PREPARE.. 走在了正确的轨道上。唯一的问题是您必须将查询字符串与字符串外部的变量名称连接起来才能访问其内容。

SET @A = (SELECT CONCAT("UPDATE target_table SET `", @str, "` = replace(str, '1', '-1');"));
PREPARE qry FROM @A;
EXECUTE qry;
DEALLOCATE PREPARE qry;

如果您拥有的 replace 与您的变量 @str 相关(因为您没有 @ 符号，我不确定它是实际的列还是变量)那么您需要更改@一点点

SET @A = (SELECT CONCAT("UPDATE target_table SET `", @str, "` = replace(", @str, ", '1', '-1');"));

DEMO

有了你的 DECLARE，这应该是你的最终查询

DELIMITER $$
DROP PROCEDURE IF EXISTS example$$
CREATE PROCEDURE example()
BEGIN
DECLARE p INT;
DECLARE str VARCHAR(20);
DECLARE update_qry VARCHAR(200);
SET p = 1;

    WHILE p < 100 DO
        -- set up column name
        IF p <= 9 
            THEN SET str = CONCAT('i0', p);
            ELSE SET str = CONCAT('i', p);
        END IF;
        -- set up query to execute
        SET update_qry = CONCAT("UPDATE target_table SET `", str, "` = replace(", str, ", '1', '-1');")
        -- prepare execute and deallocate query
        PREPARE qry FROM update_qry;
        EXECUTE qry;
        DEALLOCATE PREPARE qry;
        -- increment counter for next column name
        SET p = p + 1; 
    END WHILE; 
END$$