存储过程不适用于插入数据

Question

我有一张桌子：

User

user_id int PK
username varchar(20)
secret_code varchar(20)
name varchar(20)
age int
gender varchar(20)
city varchar(20)
latest_signin_time timestamp
latest_signout_time timestamp
loc_list json
buddy_list json

我创建了一个存储过程：

create procedure insert_users(IN user_id int , in username varchar(20),in secret_code varchar(20), 
                   in name varchar(20), in age int, in gender varchar(20), in city varchar(20), 
                   in latest_signin_time timestamp, in latest_signout_time timestamp, 
                   in loc_list json , in buddy_list json)
begin

insert into user values(user_id, username, secret_code, name, age, gender, city, 
                        latest_signin_time, latest_signout_time, loc_list,buddy_list)
end ;


call insert_user(​'1', 'avs431','pwd1','Ameya','22','Male','Mumbai',null,null,'[]','[]'​​);

但是，我的代码没有运行，并且我一直收到“错误代码: 1064.您的SQL语法中有一个错误；请查看与您的MySQL服务器版本相对应的手册，了解在第9行使用的正确语法。”

我不确定这里出了什么问题。有人能帮上忙吗？

谢谢!

Answer 1

create procedure insert_users(IN user_id int , in username varchar(20),in secret_code varchar(20), 
                   in name varchar(20), in age int, in gender varchar(20), in city varchar(20), 
                   in latest_signin_time timestamp, in latest_signout_time timestamp, 
                   in loc_list json , in buddy_list json)
/* begin */

insert into user values(user_id, username, secret_code, name, age, gender, city, 
                        latest_signin_time, latest_signout_time, loc_list,buddy_list)
/* end */ ;

Answer 2

过程中的命令以分号(;)终止。在过程参数中使用前缀，这样参数就不会与列名混淆。另外，在INSERT中列出列名，这样即使向表中添加了列，您的过程也可以正常工作。

delimiter //
create procedure insert_users(
in_user_id int, 
in_username varchar(20),
in_secret_code varchar(20), 
in_name varchar(20), 
in_age int, 
in_gender varchar(20), 
in_city varchar(20), 
in_latest_signin_time timestamp, 
in_latest_signout_time timestamp, 
in_loc_list json, 
in_buddy_list json
)
begin

insert into user (user_id, username, secret_code, name, age, gender, 
       city, latest_signtime, latest_signout_time, loc_list, buddy_list)
values(in_user_id, in_username, in_secret_code, in_name, in_age, in_gender, 
       in_city, in_latest_signin_time, in_latest_signout_time, in_loc_list, in_buddy_list);

end
//
delimiter ;

call insert_user(​'1', 'avs431','pwd1','Ameya','22','Male','Mumbai',null,null,'[]','[]'​​);