问题描述:
使用mysql workbench执行一段存储过程,如下,实现分段插入的功能,但是会出现Error Code: 2014 Commands out of sync; you can't run this command now,
设定SQL editor——Query Editor——Max number of result set:从10改到500,会出现同样的错误,但是出现的时间不一样。
同样的代码在navicat上实现没有问题。
CREATE DEFINER=`root`@`localhost` PROCEDURE `test`()
BEGIN#Routine body goes here...
DECLARE no_more_record INT DEFAULT 0;
DECLARE session_record text;
DECLARE numberofid int DEFAULT 0;
declare nmbid int default 0;
DECLARE cur_record CURSOR for select distinct session_trace from exact_reg_users;#the important part
#DECLARE cur_record CURSOR for select ordertime from cookie_order;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_record = 1; /*这个是个条件处理,针对NOT FOUND的条件,当没有记录时赋值为1*/
open cur_record;
fetch cur_record into session_record;
drop table if exists cookie_to_id;
create table if not exists cookie_to_id
(
id VARCHAR(255),
session_changed_users VARCHAR(255)
);
set @numberofid=1;
while no_more_record !=1 DO
fetch cur_record into session_record;
select session_record;
SELECT DISTINCT tuniu_user_id,session_trace FROM webflow1207 where session_trace = session_record;
select @numberofid:=COUNT(DISTINCT tuniu_user_id) from webflow1207 where session_trace = session_record;
/*if @numberofid = 2 then
INSERT into cookie_to_id(id,session_changed_users)
SELECT DISTINCT tuniu_user_id,session_trace FROM webflow1207 where session_trace = session_record;
SELECT "insert success";
end if;*/
end WHILE;
CLOSE cur_record;
END
解决方法:
尚不明确。