获取最后插入行的唯一ID [英] Get the Unique ID for the Last Inserted Row
问题描述
我想获取存储过程中最后插入行的唯一ID,我这样做
I want to get the Unique ID for the Last Inserted Row inside stored procedure, I make like this
DELIMITER //
CREATE PROCEDURE addNewUsers(IN userName varchar(128),IN password varchar(128), IN addedBy INT)
BEGIN
DECLARE id int default 0;
id = mysqli_insert_id (insert into `system_users`( `username`,`password`) values (userName ,md5(password)) );
IF id <> 0 THEN
insert into `user_profile`( `full_name`,`Date_time_ added`,`added_by`) values (userName ,CURRENT_TIMESTAMP(),addedBy ) where `user_id`=id ;
END IF
END //
DELIMITER ;
发生此错误
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '= mysqli_insert_id (insert into `system_users`( `username`,`password`) values (' at line 7
我怀疑是来自mysqli_insert_id
,该怎么办?
I doubt it's from mysqli_insert_id
, what should I do ?
推荐答案
您的 mysqli_insert_id
是问题所在,您正在编写MySQL存储过程,而不是PHP.您要使用 last_insert_id()
函数:
Your mysqli_insert_id
is the problem, you're writing a MySQL stored procedure, not PHP. You want to use the last_insert_id()
function:
LAST_INSERT_ID()
(不带参数)返回BIGINT(64位)值,该值表示由于最近执行的INSERT
语句而成功为AUTO_INCREMENT
列成功插入的第一个自动生成的值.
LAST_INSERT_ID()
(with no argument) returns a BIGINT (64-bit) value representing the first automatically generated value successfully inserted for anAUTO_INCREMENT
column as a result of the most recently executedINSERT
statement.
您还需要修复分配语法.像这样:
You also need to fix your assignment syntax. Something more like this:
DELIMITER //
CREATE PROCEDURE addNewUsers(IN userName varchar(128),IN password varchar(128), IN addedBy INT)
BEGIN
DECLARE id int default 0;
insert into `system_users`( `username`,`password`) values (userName ,md5(password));
set id = last_insert_id();
if id <> 0 then
-- ...
这篇关于获取最后插入行的唯一ID的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!