mysql 存储过程 - 如何获取上次插入 id [英] mysql stored procedure - How to get last insert id

问题描述

我创建了一个存储过程，用于将记录插入表 &获取该记录的自动递增 ID...这里我在将 LAST_INSERT_ID() 设置为变量时遇到语法错误...

I have created one stored procedure which inserts a record into table & gets auto incremented ID of that record... Here I am getting an syntax error while setting LAST_INSERT_ID() into a variable...

ERROR 1064 (42000):您的 SQL 语法有错误；检查与您的 MySQL 服务器版本相对应的手册，以获取在 ') 附近使用的正确语法；SET _orderId = SELECT LAST_INSERT_ID();END' 在第 5 行

请帮我解决这个问题...提前致谢...

Please help me to solve this issue... Thanks in Advance...

我的代码如下，

delimiter //

    CREATE PROCEDURE placeOrder(IN _cartId INT,IN _createdBy INT)
    BEGIN

    DECLARE _orderId INT;

    -- insert into order
    INSERT INTO `TBL_ORDER`(`DealerId`, `OrderNo`, `CreatedBy`) VALUES ((SELECT DealerId FROM TBL_SHOPPING_CART WHERE Id =  _cartId),UNIX_TIMESTAMP(),_createdBy)); 
    SET _orderId = SELECT LAST_INSERT_ID();

    END//

    delimiter ;

推荐答案

试试这个.

delimiter //
CREATE PROCEDURE placeOrder(IN _cartId INT,IN _createdBy INT)
BEGIN
SET @orderId = '';
-- insert into order
INSERT INTO `TBL_ORDER`(`DealerId`, `OrderNo`, `CreatedBy`) VALUES ((SELECT DealerId FROM TBL_SHOPPING_CART WHERE Id =  _cartId),UNIX_TIMESTAMP(),_createdBy)); 

SELECT LAST_INSERT_ID() INTO @orderId;


END//

delimiter ;

或

delimiter //
    CREATE PROCEDURE placeOrder(IN _cartId INT,IN _createdBy INT)
    BEGIN

    -- insert into order
    INSERT INTO `TBL_ORDER`(`DealerId`, `OrderNo`, `CreatedBy`) VALUES ((SELECT DealerId FROM TBL_SHOPPING_CART WHERE Id =  _cartId),UNIX_TIMESTAMP(),_createdBy); 

SELECT LAST_INSERT_ID() AS '_orderId ';


END//

delimiter ;

这篇关于mysql 存储过程 - 如何获取上次插入 id的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！