Question

Mysql下写一个存储过程并将更新的数据插入到日志表里。运行时报错了，报的sql语句错了，求大神解决

begin
declare done int default 0;
declare order_id int;
declare domain varchar(30);
declare shop_id int ;
declare order_type varchar(20);
declare opera_explain varchar(100);
declare record_time = now() varchar(20);
declare record_user varchar(20);
declare cur cursor for select fo.shop_id, fo.order_id fo.domain fo.order_type fo.opera_explain fo.record_user from shop_food_order fo where fo.state='wait_confirm' and (now()-fo.order_time)>(24*3600);
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
open cur;
fetch next from cur into order_id;
repeat;
if not done then
update shop_food_orderitem foi set foi.state='trade_closed' ,foi.closed_time=now() where foi.order_id = fo.order_id;
update shop_food_order set state='trade_closed', order_time=now(),closed_time=now() where order_id = order_id;
insert into log_order(shop_id ,domain ,order_id ,order_type ,opera_explain ,record_time ,record_user ) values(?,?,?,?,?,?,?);
end if;
fetch next from cur into order_id;
UNTIL done END REPEAT;
close cur;
end；

Answer 1

update shop_food_orderitem foi set foi.state='trade_closed' ,foi.closed_time=now() where foi.order_id = fo.order_id;

fo是什么表的别名？
例子：

update A set A.field=B.field1 from B where A.id=B.id

update shop_food_order set state='trade_closed', order_time=now(),closed_time=now() where order_id = order_id;

你这个where条件永远是真的

insert into log_order(shop_id ,domain ,order_id ,order_type ,opera_explain ,record_time ,record_user ) values(?,?,?,?,?,?,?);
？是什么值？

Answer 2

写的太潦草了，能不错吗

Answer 3

最后一个分号是手误吗，中文符号！

追问

是的