mysql - 在mysql游标中似乎被忽略的地方

我正在尝试使用游标来处理包含字符串的行：

CREATE PROCEDURE REVERT_ALL(IN TABLE_NAME VARCHAR(255))
  BEGIN
    DECLARE bDone INT;
    DECLARE CH_ID INT;
    DECLARE CH_CHANGE CHAR;

    DECLARE curs cursor for
      SELECT `table_id`, `change_type` FROM mysql_snapshot.db_changes where `table_name` = "rooms";
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET bDone = 1;

    OPEN curs;

    insert into splog set text = concat('SELECT id, `table_name`, table_id, `change_type` FROM mysql_snapshot.db_changes where table_name = ',TABLE_NAME, ';');

    SET bDone = 0;
    REPEAT
      FETCH curs INTO CH_ID, CH_CHANGE;

      insert into splog set text = concat_ws( ' -- ', 'CH_ID ', CH_ID,' TABLE ',TABLE_NAME, ' CH_CHANGE ', CH_CHANGE);

      UNTIL bDone END REPEAT;

      CLOSE curs;
    END;

由于我一天中大部分时间都在搜索，

where `table_name` = "rooms"

似乎被忽略了。计划是将其更改为

where `table_name` = TABLE_NAME

使用过程参数。我只是得到所有行。插件在那里用于日志记录和调试。

最佳答案

过程参数名称TABLE_NAME和表列名称table_name之间有歧义。应该避免使用它，因为它会像这种问题一样引起晦涩的问题。

在这种情况下，在过程内部，反引号中的TABLE_NAME，table_name和table_name均为interpreted as the local variable name（参数名称）。因此，您的条件'where table_name = ',TABLE_NAME始终为真；当用TABLE_NAME文字替换"rooms"时，如果以"rooms"作为参数调用该过程，则条件始终为true，否则为false。考虑以下简化示例：

DROP PROCEDURE IF EXISTS pr;
DROP TABLE IF EXISTS t;
CREATE PROCEDURE pr (IN TABLE_NAME VARCHAR(255))
  SELECT `id`, `table_name`, TABLE_NAME FROM t
;

CREATE TABLE t (id INT, `table_name` VARCHAR(255));
INSERT INTO t VALUES (1,'hotels'),(2,'rooms');

CALL pr("rooms");
CALL pr("foo");

你会得到

MariaDB [test]> CALL pr("rooms");
+------+--------------+------------+
| id   | `table_name` | TABLE_NAME |
+------+--------------+------------+
|    1 | rooms        | rooms      |
|    2 | rooms        | rooms      |
+------+--------------+------------+
2 rows in set (0.00 sec)

MariaDB [test]> CALL pr("foo");
+------+--------------+------------+
| id   | `table_name` | TABLE_NAME |
+------+--------------+------------+
|    1 | foo          | foo        |
|    2 | foo          | foo        |
+------+--------------+------------+
2 rows in set (0.00 sec)

如您所见，无论参数如何，所有行的table_name始终等于TABLE_NAME。

现在，如果将TABLE_NAME替换为显式的"rooms"：

DROP PROCEDURE IF EXISTS pr;
DROP TABLE IF EXISTS t;
CREATE PROCEDURE pr (IN TABLE_NAME VARCHAR(255))
  SELECT `id`, `table_name`, "rooms" FROM t
;

CREATE TABLE t (id INT, `table_name` VARCHAR(255));
INSERT INTO t VALUES (1,'hotels'),(2,'rooms');

CALL pr("rooms");
CALL pr("foo");

现在，条件table_name = "rooms"对于第一个调用（对于所有行）为true，对于第二个调用（对于所有行）为false：

MariaDB [test]> CALL pr("rooms");
+------+--------------+-------+
| id   | `table_name` | rooms |
+------+--------------+-------+
|    1 | rooms        | rooms |
|    2 | rooms        | rooms |
+------+--------------+-------+
2 rows in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> CALL pr("foo");
+------+--------------+-------+
| id   | `table_name` | rooms |
+------+--------------+-------+
|    1 | foo          | rooms |
|    2 | foo          | rooms |
+------+--------------+-------+
2 rows in set (0.00 sec)

您需要的是该参数的其他名称：

DROP PROCEDURE IF EXISTS pr;
DROP TABLE IF EXISTS t;
CREATE PROCEDURE pr (IN T_NAME VARCHAR(255))
  SELECT `id`, `table_name`, T_NAME FROM t
;

CREATE TABLE t (id INT, `table_name` VARCHAR(255));
INSERT INTO t VALUES (1,'hotels'),(2,'rooms');

CALL pr("rooms");
CALL pr("foo");

现在，比较table_name = T_NAME实际上有意义。

MariaDB [test]> CALL pr("rooms");
+------+------------+--------+
| id   | table_name | T_NAME |
+------+------------+--------+
|    1 | hotels     | rooms  |
|    2 | rooms      | rooms  |
+------+------------+--------+
2 rows in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> CALL pr("foo");
+------+------------+--------+
| id   | table_name | T_NAME |
+------+------------+--------+
|    1 | hotels     | foo    |
|    2 | rooms      | foo    |
+------+------------+--------+
2 rows in set (0.00 sec)

关于mysql - 在mysql游标中似乎被忽略的地方，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/48890076/