mysql - 由于自动增量，无法让调用 addusers 过程用于测试

Question

我对 Addusers 过程有问题，因为我添加 UserID 作为自动增量，因为我基本上想通过:

CALL addusers
(

  'Rory',
   'Covertry',
   '5566',
   'rory@gmail.com'
   '1234',
   'U'
)

我应该插入数据，但我最终收到了这样的错误。

“错误

SQL query:

    CALL addusers
    (

    'Rory',
       'Covertry',
       '5566',
       'rory@gmail.com'
       '1234',
       'U'
    )

MySQL said: Documentation 1318 - Incorrect number of arguments for PROCEDURE construction.addusers; expected 7, got 5"

这是我的存储过程

 addusers



DELIMITER go
Create procedure Addusers(
  Out UserID int(11),
 IN FirstName varchar(30),
 IN LastName varchar(30),
 IN Password varchar(30),
 IN EmailAddress varchar(30),
 IN Salt varchar(40),
 IN RoleID varchar(1))
BEGIN
insert into users(

FirstName,
 LastName ,
 Password ,
 EmailAddress ,
 Salt ,
 RoleID 
 )
Values
(
FirstName,
 LastName ,
 Password ,
 EmailAddress ,
 Salt ,
 RoleID 
);
set UserID = AUTO_INCREMENT;  

End
go
DELIMITER ;    

Answer 1

您必须使用 last_insert_id() 而不是 AUTO_INCRMENT 保留字，并在调用时输入所有 7 个参数:

DELIMITER go
Create procedure Addusers(
  Out UserID int(11),
 IN FirstName varchar(30),
 IN LastName varchar(30),
 IN Password varchar(30),
 IN EmailAddress varchar(30),
 IN Salt varchar(40),
 IN RoleID varchar(1))
BEGIN
insert into users(

FirstName,
 LastName ,
 Password ,
 EmailAddress ,
 Salt ,
 RoleID 
 )
Values
(
FirstName,
 LastName ,
 Password ,
 EmailAddress ,
 Salt ,
 RoleID 
);
set UserID = last_insert_id();  

End
go
DELIMITER ;    

要获取 OUT 参数，您必须使用如下 session 变量:

set @new_id = null;
call addusers(@new_id,'Rory','Covertry','5566','rory@gmail.com','1234','U' );
select @new_id; -- contains the inserted id