MySQL存储过程:OUT参数未设置 [英] MySQL stored procedure: OUT parameter not being set

问题描述

我在MySQL中有一个存储过程，该存储过程从表中获取下一个唯一ID，用作其他2个表的ID(我确定这不是最佳方法，但是我正在修改别人的代码在这里).步骤如下:

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:

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$$

当我从MySQL Workbench调用该过程时:

When I call the procedure from MySQL Workbench:

CALL GetNextID( @id );
SELECT @id;

@id是NULL.我无法解决出了什么问题?即使我在调用该过程之前运行SET @id = 0;，此后它也会以NULL结尾.如果我从MySQL Workbench手动调用过程中的函数，则@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.:

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;

这会将@id输出为有效数字.

This outputs @id as being a valid number.

任何关于为什么id设置不正确的想法吗?

Any ideas why id isn't being set properly?

推荐答案

通常，在此上花费3个小时，然后在发布问题后才发现问题.因此，以供将来参考:在涉及变量的地方，MySQL似乎不区分大小写. ID列名和id变量显然完全混淆了它.

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.

我将过程的输入参数名称更改为retId，然后运行良好.

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

这篇关于MySQL存储过程:OUT参数未设置的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！