关于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 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