mysql - 存储过程 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 ;

我真的不知道该怎么做。

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 ;