mysql存储过程不一致地增加所有记录

I have created a stored procedure as I want to have a scenario where I want to get the next number from Database.

我创建了一个存储过程，因为我想要一个场景，我想从数据库中获取下一个数字。

DELIMITER $$
CREATE PROCEDURE `INCREMENT_IDENTIFIERS`(IN key_name varchar(30))
BEGIN
    SET @keyName := key_name;
    UPDATE APS_IDENTIFIERS SET COUNTER = COUNTER + 1 WHERE KEY_NAME = @keyName;
    SELECT * FROM APS_IDENTIFIERS WHERE KEY_NAME = @keyName;
END$$
DELIMITER ;

Initial Column Values

初始列值

Final Column Values after calling stored procedure

调用存储过程后的最终列值

call INCREMENT_IDENTIFIERS('urn');

Now my problem is that why this stored procedure increments both rows even if I am passing the exact match input condition, and it return both rows as output with incremented values.

现在我的问题是，即使我传递了完全匹配的输入条件，这个存储过程为什么会增加两行，并且它返回两行作为输出，增加值。

1 个解决方案

#1

It's not necessary to use a 9.4 User-Defined Variables, can use the input parameter or a 13.6.4.1 Local Variable DECLARE Syntax:

没有必要使用9.4用户定义的变量，可以使用输入参数或13.6.4.1本地变量DECLARE语法：

mysql> DELIMITER //

mysql> CREATE PROCEDURE `INCREMENT_IDENTIFIERS`(
    ->   `in_key_name` VARCHAR(30)
    -> )
    -> BEGIN
    ->   UPDATE `APS_IDENTIFIERS`
    ->   SET `COUNTER` = `COUNTER` + 1
    ->   WHERE `KEY_NAME` = `in_key_name`;
    ->   SELECT `KEY_NAME`, `COUNTER`
    ->   FROM `APS_IDENTIFIERS`
    ->   WHERE `KEY_NAME` = `in_key_name`;
    -> END//
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;

mysql> CALL `INCREMENT_IDENTIFIERS`('urn');
+----------+---------+
| KEY_NAME | COUNTER |
+----------+---------+
| urn      |       3 |
+----------+---------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

See db-fiddle.

请参阅db-fiddle。

UPDATE

UPDATE

Avoid using column names as parameter names:

避免使用列名作为参数名称：

mysql> DELIMITER //

mysql> CREATE PROCEDURE `INCREMENT_IDENTIFIERS`(
    ->   `key_name` VARCHAR(30) -- CHANGE NAME
    -> )
    -> BEGIN
    ->   SET @`keyName` := `key_name`;
    ->   UPDATE `APS_IDENTIFIERS`
    ->   SET `COUNTER` = `COUNTER` + 1
    ->   WHERE `KEY_NAME` = @`keyName`;
    ->   SELECT `KEY_NAME`, `COUNTER`
    ->   FROM `APS_IDENTIFIERS`
    ->   WHERE `KEY_NAME` = @`keyName`;
    -> END//
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;

mysql> CALL `INCREMENT_IDENTIFIERS`('urn');
+------------+---------+
| `KEY_NAME` | COUNTER |
+------------+---------+
| urn        |      69 |
| urn        |       3 |
+------------+---------+
2 rows in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

New parameter name:

新参数名称：

mysql> DELIMITER //

mysql> CREATE PROCEDURE `INCREMENT_IDENTIFIERS`(
    ->   `_key_name` VARCHAR(30) -- NEW NAME
    -> )
    -> BEGIN
    ->   SET @`keyName` := `_key_name`;
    ->   UPDATE `APS_IDENTIFIERS`
    ->   SET `COUNTER` = `COUNTER` + 1
    ->   WHERE `KEY_NAME` = @`keyName`;
    ->   SELECT `KEY_NAME`, `COUNTER`
    ->   FROM `APS_IDENTIFIERS`
    ->   WHERE `KEY_NAME` = @`keyName`;
    -> END//
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;

mysql> CALL `INCREMENT_IDENTIFIERS`('urn');
+----------+---------+
| KEY_NAME | COUNTER |
+----------+---------+
| urn      |       3 |
+----------+---------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

See db-fiddle.

请参阅db-fiddle。

Check documentation 13.6.4.2 Local Variable Scope and Resolution.

检查文档13.6.4.2局部变量范围和分辨率。

UPDATE 2

更新2

Another option (less recommended) is to use qualified column names (APS_IDENTIFIERS.KEY_NAME):

另一个选项（不太推荐）是使用限定列名（APS_IDENTIFIERS.KEY_NAME）：

mysql> DELIMITER //

mysql> CREATE PROCEDURE `INCREMENT_IDENTIFIERS`(
    ->   `key_name` VARCHAR(30)
    -> )
    -> BEGIN
    ->   SET @`keyName` := `key_name`;
    ->   UPDATE `APS_IDENTIFIERS`
    ->   SET `COUNTER` = `COUNTER` + 1
    ->   WHERE `APS_IDENTIFIERS`.`KEY_NAME` = @`keyName`;
    ->   SELECT `KEY_NAME`, `COUNTER`
    ->   FROM `APS_IDENTIFIERS`
    ->   WHERE `APS_IDENTIFIERS`.`KEY_NAME` = @`keyName`;
    -> END//
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;

mysql> CALL `INCREMENT_IDENTIFIERS`('urn');
+------------+---------+
| `KEY_NAME` | COUNTER |
+------------+---------+
| urn        |       3 |
+------------+---------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

See db-fiddle.

请参阅db-fiddle。

Check documentation 9.2.1 Identifier Qualifiers.

检查文档9.2.1标识符限定符。