MySQL存储过程:OUT参数未设置。

I've got a stored procedure in MySQL that gets the next unique ID from a table, to use as an ID for 2 other tables (not the best way to do it, I'm sure, but I'm modifying someone else's code here). The procedure is as follows:

我在MySQL中有一个存储过程，它从一个表中获取下一个唯一的ID，作为另外两个表的ID(我确定这不是最好的方法，但我正在修改其他人的代码)。程序如下:

DELIMITER $$
CREATE DEFINER=`root`@`%` PROCEDURE `GetNextID`( OUT id bigint )
BEGIN
  DECLARE uid VARCHAR(255);
  SET uid = uuid();
  INSERT INTO `ident_column_generator` (u) VALUES (uid);
  SELECT ID INTO id FROM `ident_column_generator` WHERE u = uid;
  DELETE FROM `ident_column_generator` WHERE u = uid;
END$$

When I call the procedure from MySQL Workbench:

当我从MySQL Workbench调用过程时:

CALL GetNextID( @id );
SELECT @id;

@id is NULL. I can't work out what's going wrong? Even if I run SET @id = 0; before calling the procedure, it ends up as NULL afterwards. If I call the functions within the procedure manually from MySQL Workbench, @id outputs fine, e.g.:

@ id是NULL。我搞不清楚到底出了什么问题?即使我运行SET @id = 0;在调用该过程之前，它最终为NULL。如果我从MySQL工作台手动调用过程中的函数，@id输出很好，例如:

SET @uid = uuid();
INSERT INTO `ident_column_generator` (u) VALUES (@uid);
SELECT ID INTO @id FROM `ident_column_generator` WHERE u = @uid;
DELETE FROM `ident_column_generator` WHERE u = @uid;
SELECT @id;

This outputs @id as being a valid number.

这输出@id为一个有效数字。

Any ideas why id isn't being set properly?

有什么想法吗?

2 个解决方案

#1

Typically, spent 3 hours on this, then JUST after I posted the question I find the problem. So, for future reference: It appears MySQL is case insensitive where variables are concerned. The ID column name and id variable apparently completely confused it.

通常情况下，我花了3个小时在这上面，然后在我发布问题之后我发现了问题。因此，作为将来的参考:在涉及变量时，MySQL似乎不区分大小写。ID列名和ID变量显然完全混淆了它。

I changed the procedure's input parameter name to retId and then it worked perfectly.

我将程序的输入参数名称改为retId，然后效果非常好。

#2

Thanks Nick, I also had same issue. The column name and variable name were same due to which we were getting the issue.

谢谢尼克，我也有同样的问题。列名和变量名是相同的，因为我们得到了这个问题。

#1