如何通过php中的存储过程获取LAST_INSERT_ID [英] how to get LAST_INSERT_ID via stored procedure in php
本文介绍了如何通过php中的存储过程获取LAST_INSERT_ID的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
说我在如下所示的mysql中有一个存储过程
say i have a stored procedure in mysql like below
-- ----------------------------
-- Procedure structure for usp_insert_user_basic_info
-- ----------------------------
DROP PROCEDURE IF EXISTS `usp_insert_user_basic_info`;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `usp_insert_user_basic_info`(IN `user_first_name` varchar(200),IN `user_last_name` varchar(200),IN `user_password` text,IN `user_dob` date,IN `user_email` varchar(250))
BEGIN
#Routine body goes here...
INSERT INTO `nuclear`.`user_basic_info` (
`user_email` ,
`user_password` ,
`user_first_name` ,
`user_last_name` ,
`user_dob`,
`user_creation_time`
)
VALUES (
user_email, user_password, user_first_name, user_last_name, user_dob,NOW()
);
SELECT LAST_INSERT_ID() ;
END
;;
DELIMITER ;
表
-- ----------------------------
-- Table structure for user_basic_info
-- ----------------------------
DROP TABLE IF EXISTS `user_basic_info`;
CREATE TABLE `user_basic_info` (
`user_id` int(11) NOT NULL AUTO_INCREMENT,
`user_email` varchar(250) DEFAULT NULL,
`user_password` text,
`user_first_name` varchar(200) DEFAULT NULL,
`user_last_name` varchar(200) DEFAULT NULL,
`user_dob` date DEFAULT NULL,
`user_creation_time` datetime DEFAULT NULL,
PRIMARY KEY (`user_id`),
UNIQUE KEY `user_email` (`user_email`)
) ENGINE=InnoDB AUTO_INCREMENT=36 DEFAULT CHARSET=latin1;
我可以从php正常调用
i can call this from php without any problem
$resultUsp = mysql_query($query) or die("Error: " . mysql_error());
,但每次成功插入仅返回1.我想这是说有1行受影响了!
but it only returns 1 for each successful insert. i guess it is saying 1 row affected!
我希望它返回LAST_INSERT_ID
该怎么做? 顺便说一句,我不想添加任何OUT参数. mysql_insert_id返回0
how to do it? by the way i don't want to add any OUT parameter. mysql_insert_id returns 0
推荐答案
您可以在SP中获得像这样的最后一个插入ID:
You can get last insert id like this in SP:
DECLARE LID int;
SET LID = LAST_INSERT_ID();
这篇关于如何通过php中的存储过程获取LAST_INSERT_ID的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文