mysql 存储过程批量处理带分隔符的字符串

CREATE DEFINER=root@% PROCEDURE updateMerchantOrderNo()
BEGIN

#Routine body goes here...
declare orderstr text;
declare ordernum int; /**订单个数**/
declare i int default 1;
declare orderno text;
declare updateSql text;


set orderstr = "ddd";
# 数组长度 = 逗号个数
select length(orderstr) - length(REPLACE(orderstr,",","")) + 1 into @ordernum;

/* 执行查看订单数量*/
select @ordernum;

while i <= @ordernum DO

set @orderno = substring_index(SUBSTRING_INDEX(orderstr,",",i), ",", -1);

/*执行查看解析出来的订单号*/
select @orderno;


/*构造更新语句*/
set @updateSql = CONCAT("UPDATE `kettle`.`orders` SET `merchant_order_no` = concat(",@orderno,",'-cancel',DATE_FORMAT(NOW(),'%h%i%s')) where `merchant_order_no` = ", @orderno);

/*显示sql语句*/
select @updateSql;

/*执行sql语句*/
update `kettle`.`orders` set `merchant_order_no` = CONCAT(@orderno,'-cancel',DATE_FORMAT(NOW(),'%h%i%s'))
where `merchant_order_no` = @orderno;

set i = i + 1;
end while;

END