乐趣区

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

退出移动版