存储过程中的变量 MySQL

Question

我遇到了一个与这个简单存储过程有关的问题。我想把 LAST_INSERT_ID 保存在一个变量中，然后将其分配给下一个插入操作。

    DELIMITER //
    CREATE PROCEDURE Adda(
       Name varCHAR(45),Surrname varCHAR(45),City varCHAR(45),PhoneNumber varCHAR(45),photo varCHAR(45))
    BEGIN
    START TRANSACTION;
       INSERT INTO Personal(Name,Surrname)
       VALUES(Name,Surrname);
       INSERT INTO Addres(Id_Personal_Address,Country, City) 
       VALUES(LAST_INSERT_ID(),Country,City);
--i think here i should assign LAST_INSER_ID to variable
        INSERT INTO Images(Id_Personal_Address,photo)
        VALUES(Photo); -- and here use it
    COMMIT;
    END//
    DELIMITER ;

我真的不知道该怎么做。

- HowIsIt

Answer 1

您需要声明一个变量并使用set来赋值，例如：

DELIMITER //
    CREATE PROCEDURE Adda(
       Name varCHAR(45),Surrname varCHAR(45),City varCHAR(45),PhoneNumber varCHAR(45),photo varCHAR(45))
    BEGIN
    DECLARE last_insert_id INT;
    START TRANSACTION;
       INSERT INTO Personal(Name,Surrname)
       VALUES(Name,Surrname);
       SET last_insert_id = LAST_INSERT_ID(); --assignment
       INSERT INTO Addres(Id_Personal_Address,Country, City) 
       VALUES(last_insert_id,Country,City);
        INSERT INTO Images(Id_Personal_Address,photo)
        VALUES(last_insert_id, Photo); -- use
    COMMIT;
    END//
    DELIMITER ;

- Darshan Mehta

---

如果你没有使用@，那么这是正确的。请参考我的回答中的解释。 - Bill Karwin

更新了答案，感谢您的输入 @BillKarwin - Darshan Mehta

Answer 2

你知道可以在任何表达式中调用LAST_INSERT_ID()，包括下一个INSERT语句:

   INSERT INTO Addres(Id_Personal_Address,Country, City) 
   VALUES(LAST_INSERT_ID(),Country,City);

   INSERT INTO Images(Id_Personal_Address,photo)
   VALUES(LAST_INSERT_ID(), Photo); -- and here use it

但是LAST_INSERT_ID()总是返回最后一个INSERT生成的id值。如果您插入到Address具有自动增量键，则此值将成为生成的新“最后”id。

因此，如果您需要多次使用该值，则应声明一个本地变量，因为插入到Images会更改最后插入的id。

您可以在过程中声明本地变量。这必须紧跟在BEGIN之后。

BEGIN
    DECLARE personalId INT DEFAULT NULL;

然后稍后使用它：

    SET personalId = LAST_INSERT_ID();

    INSERT INTO Addres(Id_Personal_Address, Country, City) 
    VALUES(personalId, Country, City);

    INSERT INTO Images(Id_Personal_Address, photo)
    VALUES(personalId, Photo);

提示：选择一个与您的列名不同的变量名，以使您的代码更清晰易懂。

---

那么在变量前使用@有什么作用呢？

你可以像使用@addressId这样的变量，而不需要先进行DECLARE。但是该变量将成为用户定义会话变量，并且在过程退出后保留其值。除非您意外覆盖会话范围内的变量，否则不会有任何损害。或者如果您编写一个调用另一个存储过程的存储过程，并且两个存储过程都尝试使用相同的变量名。

使用DECLARE创建一个真正局部于过程范围的变量。

更令人困惑的是，您可以声明一个给定名称的变量，然后使用带有@的相同名称，但那是一个不同的变量。

例如，在MySQL 8.0.0上测试的演示如下：

mysql> delimiter ;;
mysql> create procedure foo()
    -> begin
    ->  declare last_insert_id int default 0;
    ->  set @last_insert_id = 1234;
    ->  select last_insert_id;
    -> end;;
mysql> delimiter ;

mysql> call foo();
+----------------+
| last_insert_id |
+----------------+
|              0 |
+----------------+

mysql> select @last_insert_id;
+-----------------+
| @last_insert_id |
+-----------------+
|            1234 |
+-----------------+

- Bill Karwin