在 MySQL 中获取最后插入的 ID 表单存储过程
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13151861/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Fetch last inserted ID form stored procedure in MySQL
提问by Ekky
How to fetch last inserted id?
如何获取最后插入的ID？
CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_insertzonemsg`
(IN User_Id INT(10),
IN zid INT(10),
IN update_titles VARCHAR(50),
IN textarea1s TEXT,
IN filupload1s TEXT,
IN audio1s VARCHAR(100),
OUT out_id INT(10))
BEGIN
INSERT INTO zone_message_master
**(user_id,ZoneID,update_title,textarea1,filupload1,
audio1,LastUpdate)**
VALUE
**(User_Id,zid,update_titles,textarea1s,
filupload1s,audio1s,NOW());**
SELECT **id** as **out_id**
FROM **zone_message_master** LAST_INSERT_ID();
END$$
I need to return last inserted ID as out_idform the Table zone_message_master?
我需要返回最后插入的 ID 作为out_id表格zone_message_master吗？
回答by Devart
You need to use SET statement. For example -
您需要使用 SET 语句。例如 -
Table:
桌子：
CREATE TABLE table1(
id INT(11) PRIMARY KEY AUTO_INCREMENT,
column1 VARCHAR(10),
column2 VARCHAR(10)
);
Procedure's body:
程序主体：
BEGIN
INSERT INTO table1(column1, column2) VALUES ('value1', 'value2');
SET out_param = LAST_INSERT_ID();
END
Note, that ID field is not specified in INSERT statement. This value will be inserted automatically; and of course, this ID field must have AUTO_INCREMENT option.
请注意，该 ID 字段未在 INSERT 语句中指定。这个值会自动插入；当然，这个 ID 字段必须有 AUTO_INCREMENT 选项。
回答by Adnan Khan
You can Get Last Insert ID with the help of following code:-
您可以借助以下代码获取上次插入 ID：-
Just create a stored Procedure:
只需创建一个存储过程：
CREATE DEFINER=`root`@`localhost` PROCEDURE `add_enquiry`(
IN EnquiryNo varchar(40),
IN OpeningDate date,
IN ClosingDate date,
IN CreatedOn date,
OUT LID int(11)
)
BEGIN
INSERT into `enquiries` SET `enquiry_no`=EnquiryNo ,`opening_date`=OpeningDate ,`closing_date`=ClosingDate,`created_on` =CreatedOn;
SET LID = LAST_INSERT_ID();
END
Call on PHP:
调用 PHP：
$rs = $dbh->query('CALL add_enquiry("ENQ" ,"2016-08-19","2016-08-18", "2016-11-23",@LID)');
$rs2 = $dbh->query("SELECT @LID as id");
$row = $rs2->fetchObject();
echo $row->id;
