MySQL 存储过程变量格式在 SELECT 语句中不起作用

Question

我无法让变量在 MySQL 存储过程中正常工作。这是存储过程的示例:

BEGIN

DECLARE x_parent varchar(100);
DECLARE term_ids_current varchar(100);
DECLARE term_ids_list INT(100);
DECLARE x_counter INT(11);
DECLARE y_counter INT(11);
DECLARE z_counter INT(11);

SET @term_ids_current = 189;
SET @term_ids_list = @term_ids_current;
SET @y_counter = 0;
SET @z_counter = 0;

parent_child: LOOP
    # used for debugging #
    SET @z_counter = @z_counter + 1;

    # first i check to see if my criteria is met to continue the loop. #
    IF EXISTS (SELECT tt.term_id FROM `bitnami_wordpress`.`wp_2_term_taxonomy` tt WHERE tt.parent IN (@term_ids_current)) THEN

        # used for debugging #
        SET @y_counter = @y_counter + 1;

        BEGIN

            DECLARE x_finished INT(11) DEFAULT 0;

            DECLARE parent_child_cursor CURSOR FOR
            SELECT tt.term_id FROM `bitnami_wordpress`.`wp_2_term_taxonomy` tt WHERE tt.parent IN (@term_ids_current);

            # declare NOT FOUND handler
            DECLARE CONTINUE HANDLER 
            FOR NOT FOUND SET x_finished = 1;

            OPEN parent_child_cursor;

                SET @x_counter = 0;

                single_parent: LOOP

                    FETCH parent_child_cursor INTO x_parent;

                    IF x_finished = 1 THEN
                        LEAVE single_parent;
                    ELSEIF @x_counter = 0 THEN
                        SET @term_ids_current = x_parent;
                    ELSE
                        SET @term_ids_current = CONCAT (x_parent,",",@term_ids_current);
                    END IF;

                    SET @x_counter = @x_counter + 1;

                END LOOP single_parent;

                SET @term_ids_list = CONCAT (@term_ids_current,",",@term_ids_list);

            CLOSE parent_child_cursor;

        END;
    ELSE
        LEAVE parent_child;
    END IF;

END LOOP parent_child;

# used for debugging #
SELECT @z_counter, @y_counter, @term_ids_current, @term_ids_list;

END;

在 single_parent 循环中，我用查询结果填充 @term_ids_current 变量。然后在主循环的查询中使用该变量来确定是否仍然满足条件。如果是，那么我的循环再次发生。我已经将程序的每个步骤作为一个单独的查询进行了测试，结果是准确的。问题在于让 parent_child 循环中的主查询第二次正常运行。

当我针对现有数据集运行上面的 SP 时，@term_ids_current 变量包含此数据集:

218,200,199,198,197,196,195,194,193,192,191,190

我期望发生的是这个数据集现在被传递到 parent_child 循环的查询中，这样查询现在是:

(SELECT tt.term_id FROM `bitnami_wordpress`.`wp_2_term_taxonomy` tt WHERE tt.parent IN (218,200,199,198,197,196,195,194,193,192,191,190))

当我针对我的数据库手动运行该查询时，“IF EXISTS”语句为真，这意味着我的 parent_child 循环应该再次运行。但是循环在 IF EXISTS 语句之后停止并且不再运行。我已经用 z_counter 和 y_counter 变量验证了这一点。每次我运行 SP @z_counter = 2 和 @y_counter = 1。所以我知道 IF 语句正在移动到 ELSE 子句而不是运行。

为了进一步测试这一点，我更新了 SP 并在 parent_child 循环结束时手动设置我的 @term_ids_current 变量以查看会发生什么。因此，在我更改的 SP 结束时，我以这种方式设置了@term_ids_current:

SET @term_ids_current = (190,191,192,193,194,195,196,197,198,199,200,218);

    IF @z_counter = 2 THEN
        LEAVE parent_child;
    END IF;

我添加了第二个 IF 语句以防止 SP 运行无限循环。

现在，当我执行 SP 时，我收到此错误消息:“MySQL said: #1241 - Operand should contain 1 column(s).”所以我再次更新 SP 以设置 @term_ids_current 变量，但这次我使用双引号而不是括号。

SET @term_ids_current = "190,191,192,193,194,195,196,197,198,199,200,218";

通过手动设置变量，SP 返回预期结果。我试图在我的 SELECT 语句中同时使用 CAST 和 CONVERT 将变量视为字符串。这是一个例子:

SELECT tt.term_id FROM `bitnami_wordpress`.`wp_2_term_taxonomy` tt WHERE tt.parent IN (CONVERT(@term_ids_current,CHAR));

这是行不通的。如果我在 Python 中执行此操作，我将转换 SELECT 语句的变量:

str(@term_ids_current)

但是我找不到在 MySQL 中做同样事情的方法。

所以我的问题是，我怎样才能让我的变量@term_ids_current 被视为一个字符串，以便在我的 SELECT 语句中正常工作？

Answer 1

查询

SELECT tt.term_id 
FROM `bitnami_wordpress`.`wp_2_term_taxonomy` tt 
WHERE tt.parent IN (@term_ids_current)

如果 @term_ids_current 是逗号分隔的字符串，则将不起作用。这样做的原因是 @term_ids_current 被视为字符串而不是集合。

一些建议:

1. 不使用用户定义的变量 (@var)，而是使用当前 ID 的临时表
2. 最好在过程中使用局部变量(声明 v_var int)而不是用户定义的变量，因为用户定义变量的范围是连接，而局部变量的范围是过程。如果您的过程调用使用相同变量的其他过程，这很重要。