MySQL local variable value changes unexpectedly

Question

I have a table with following prototype:

CREATE TABLE `T2` (
`ID` int(11) DEFAULT NULL,
`NAME` varchar(100) DEFAULT NULL
)

I want to create procedure to insert random strings with a specific length and I wrote the following:

DELIMITER ;;
CREATE PROCEDURE STRGEN ( IN ITER INT) BEGIN
    DECLARE COUNTER INT DEFAULT 0;
    DECLARE _STR VARCHAR(1000) DEFAULT NULL;

    WHILE COUNTER < @ITER DO
        SET _STR = CONCAT(_STR , CHAR(RAND()*100+65));
        SET COUNTER = COUNTER + 1;
    END WHILE;

    INSERT INTO T2 VALUES(RAND()*100 , _STR);
END;;

And called it this way:

CALL STRGEN(100);

The problem is that it inserts NULL as the NAME field.
My suspicion is that changes made to _STR goes out of scope when the while loop terminates.

I've also tried using a global variable like the following:

CREATE PROCEDURE STRGEN ( IN ITER INT) BEGIN
DECLARE COUNTER INT DEFAULT 0;
DECLARE _STR VARCHAR(1000) DEFAULT NULL;

SET @S = NULL;

WHILE (COUNTER < @ITER) DO
    SET _STR = CONCAT(_STR , CHAR(RAND()*100+65));
    SET COUNTER = COUNTER + 1;
    SET @S = _STR;
END WHILE;
INSERT INTO T2 VALUES(RAND()*100 , @S);
END;;

And also declaring @S outside of the procedure , but nothing special happened.

Any help is appreciated.
Best regards.

See [MySQL CONCAT returns NULL if any field contain NULL](https://stackoverflow.com/q/15741314): since your `_STR` is `NULL` to begin with, it stays `NULL` after `concat`. Set it e.g. to `''`. — Solarflare, Jan 05 '20 at 06:32

Hooman Bahreini · Accepted Answer · 2020-01-05T10:15:22.800

When I tried running your procedure, I got the following error:

Error Code: 1366. Incorrect string value: '\x87' for column '_STR' at row 1 0.000 sec

It seems that you are not generating valid chars... I tried a different char generator logic and it works as expected.

DELIMITER ;;
CREATE PROCEDURE STRGEN ( IN ITER INT) BEGIN
    DECLARE COUNTER INT DEFAULT 0;
    DECLARE _STR VARCHAR(1000) DEFAULT ''; -- change the initial value to ''

    WHILE COUNTER < @ITER DO
        -- SET _STR = CONCAT(_STR , CHAR(RAND()*100+65));

        -- if I change the logic for generating random char, then I get expected result
        SET _STR = CONCAT(_STR , LEFT(MD5(RAND()), 1));
        SET COUNTER = COUNTER + 1;
    END WHILE;

    INSERT INTO T2 VALUES(RAND()*100 , _STR);
END;;

That's not what the problem is. This question is a simple typo and should be closed as such. — Nick, Jan 05 '20 at 06:29

MySQL local variable value changes unexpectedly

1 Answers1