MySQL存储过程循环结束后变量值返回null

Question

这是我的简化，循环后rent_ids字符串的值返回为null。我已经知道循环工作正常，并且rent_ids的值随着每次迭代而变化。

BEGIN 

DECLARE rent_ids VARCHAR(265);
DECLARE tmp_rent_id int;
create temporary table due_rent_ids (rent_id int);
SET rent_ids = "";

set @test = "Insert into due_rent_ids (rent_id) select unit_id from tbl_rent";

PREPARE stmt1 FROM @test; 

EXECUTE stmt1; 

BEGIN   

        DECLARE cur1 CURSOR for select rent_id from due_rent_ids;
        OPEN cur1;

        read_loop: LOOP
            FETCH cur1 INTO tmp_rent_id;

            IF rent_ids = "" THEN
              SET rent_ids = tmp_rent_id;
            ELSE
              SET rent_ids = concat(rent_ids, ", ", tmp_rent_id);
            END IF;

        END LOOP;

        CLOSE cur1;

 END;

 select * from tbl_unit where unit_id in (rent_ids);

 DEALLOCATE PREPARE stmt1; 
END

Answer 1

你做的这一切都是错误的。您不能将逗号分隔的字符串放入 IN (...) 中，逗号必须位于实际的 SQL 代码中。

正确的做法是:

SELECT *
FROM tbl_unit
WHERE unit_id IN (SELECT rent_id FROM due_rent_ids)

或者:

SELECT t1.*
FROM tbl_unit AS t1
JOIN due_rent_ids AS t2 ON t1.unit_id = t2.rent_id

第二种形式在 MySQL 中往往表现更好。