关于mysql: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 DOset @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