Mysql存储过程只在第一次返回null

Question

这是一个名为 code 的表。

p_code 列是 parent 的代码

存储过程应该像这样返回一组由p_code组合的记录；

程序除了第一次之外运行良好(重新连接 MYSQL 后，它返回 null!)。有什么问题吗？

这是存储过程。

 BEGIN
 DECLARE _CODE bigint(20);
 DECLARE _P_CODE bigint(20);
 DECLARE _SORT bigint(20);
 DECLARE _pre_P_CODE bigint(20);
 DECLARE CONTINUE HANDLER FOR NOT FOUND SET @CODE = NULL;

 SET _P_CODE = @CODE;
 SET _CODE = '';
 SET _SORT = 0;
 SET _pre_P_CODE = '';



 IF @CODE IS NULL THEN
  RETURN NULL;
 END IF;

 LOOP

  SELECT CODE, P_CODE, SORT, CODE_NAME, CODE_LEVEL
  INTO @CODE, @P_CODE, @SORT, @CODE_NAME, @CODE_LEVEL
  FROM CODE
  WHERE LANGUAGE = @LANGUAGE
  AND P_CODE = _P_CODE
  AND SORT > _SORT
  ORDER BY SORT limit 1;

  IF @CODE IS NOT NULL OR _P_CODE = @start_with THEN
   SET @level = @level + 1;
   RETURN @CODE;
  END IF;

  SET @level := @level - 1;

  SELECT CODE, P_CODE, SORT
  INTO _CODE, _P_CODE, _SORT
  FROM CODE
  WHERE CODE = _P_CODE;
 END LOOP;

END

过程是这样调用的。此 SQL 将返回上面的第二张图片。

SELECT
    menu_connect_by_p_code() AS CODE, 
    @level as level, 
    @P_CODE as p_code, 
    @SORT as sort, 
    @CODE_NAME as CODE_NAME, 
    @CODE_LEVEL as CODE_LEVEL
FROM (
    SELECT
        @start_with := 6001,@LANGUAGE := 'en' ,
        @CODE := @start_with,
        @level := 0
) vars, code
WHERE @CODE IS NOT NULL

我不明白为什么它第一次返回 null。 MYSQL的程序有问题或者是什么bug？

----------------------------编辑------------ ----------------------

RolandoMySQLDBA，rsanchez，我试过你的选择，但还是一样。它返回多行，有些列为 null。

-------------------------------- edit2 ---------------- ----------------------

你可以在这里看到:http://sqlfiddle.com/#!2/aa033/1

Answer 1

来自 MySQL Reference Manual :

As a general rule, you should never assign a value to a user variable and read the value within the same statement. You might get the results you expect, but this is not guaranteed. The order of evaluation for expressions involving user variables is undefined and may change based on the elements contained within a given statement; in addition, this order is not guaranteed to be the same between releases of the MySQL Server.

因此你可能想要改变:

SELECT
    @start_with := 6001,@LANGUAGE := 'en' ,
    @CODE := @start_with,
    @level := 0

通过:

SELECT
    @start_with := 6001,@LANGUAGE := 'en' ,
    @CODE := 6001,
    @level := 0