Running the loop stops after changing the Counter Variable

user_1625883 0
Viewed 56

When I run the following code, it always returns a NULL value for the two variables inside the loop.

SET @c = '';
SET @i = 4;
REPEAT
SET @c = CONCAT(
        ' -> ',
        (
            SELECT name
            FROM c
            WHERE id = @i
        ),
        @c
    );
SET @i = (
        SELECT pid
        FROM c
        WHERE id = @i
    );
UNTIL TRIM(COALESCE(@i, '')) = ''
END REPEAT;

The loop must be run at least 4 times; But in the second run, the run of the loop ends.
The value is set for variables in the first time that the loop is executed; But in the second run of the loop, the value of both changes to NULL and the loop stops. However, inside table c there are related values for the execution of the loop.

  • The problem occurs when the value of the variable @i in the loop changes.
    For example, the first time the loop is executed, the value of @i becomes 3, and there is an id with a value of 3 in table c in addition to the initial value of 4.

Structure of Table c

CREATE TABLE c (
    id int UNSIGNED NOT NULL AUTO_INCREMENT,
    name varchar(50) NOT NULL,
    pid int UNSIGNED DEFAULT NULL,
    PRIMARY KEY (id)
) ENGINE = INNODB,
AUTO_INCREMENT = 1,
CHARACTER SET utf8mb4,
COLLATE utf8mb4_unicode_ci;
ALTER TABLE c
ADD CONSTRAINT c_pid_foreign FOREIGN KEY (pid) REFERENCES c (id);

INSERT INTO c(id, name, pid)
VALUES (1, '1st', NULL),
    (2, '2nd', 1),
    (3, '3rd', 2),
    (4, '4th', 3);

Thank you in advance for the good cooperation of the people to solve this problem.

repeat mysql sql loops variables
1 Answers

I was finally able to solve my question after about more than a day; Of course, I did not understand the cause of this problem and I do not know why MySQL changes the return value of the result of the SELECT statement to NULL when using the @i variable after changing its value inside the loop?!!!
The solution is as follows:

SET @d = '';
SET @j = 4;
REPEAT
SELECT @c := name
FROM c
WHERE id = @j;
SET @d = CONCAT(' -> ', @c, @d);
SELECT @i := pid
FROM c
WHERE id = @j;
SET @j = @i;
UNTIL TRIM(COALESCE(@j, '')) = ''
END REPEAT;

Here are the best solution to this problem:

SET @d = '';
SET @j = 4;
REPEAT
SELECT name INTO @c
FROM c
WHERE id = @j;
SET @d = CONCAT(' -> ', @c, @d);
SELECT pid INTO @i
FROM c
WHERE id = @j;
SET @j = @i;
UNTIL TRIM(COALESCE(@j, '')) = ''
END REPEAT;

I hope it is useful for others as well.

user_16258830
Related