存储过程 ：字段按逗号拆分并插入到关联表

-- 需求： dfs_purchase_request表id对应多个order_num和order_id，原先order_num和order_id的多个值
-- 分别存在order_num和order_id中 按逗号分隔，现要求将其拆分到dfs_purchase_request_order_relation关联表中
-- 并将数据迁移到关联表中

-- 计算分割后字符串个数函数：func_split_sum
-- 传入待分割的字符串和分割符，返回分割后的字符串个数
DELIMITER $$
DROP function IF EXISTS `func_split_sum` $$
CREATE FUNCTION `func_split_sum`
(f_string varchar(1000),f_delimiter varchar(5)) RETURNS int(11)
DETERMINISTIC
BEGIN
return 1+(length(f_string) - length(replace(f_string,f_delimiter,'')));
END$$
DELIMITER;

-- func_split_index函数调试
select length('1,15,4,8')

select replace('1,15,4,8',',','')

select length(replace('1,15,4,8',',',''))

select (length('1,15,4,8') - length(replace('1,15,4,8',',','')))+1

select func_split_index('1,15,4,8',',')

 

-- 字符串分割函数：func_split_string
-- 传入待拆分的字符串和分割符,返回拆分后的字符串
DELIMITER $$
DROP function IF EXISTS `func_split_string` $$
CREATE FUNCTION `func_split_string`
(f_string varchar(1000),f_delimiter varchar(5),f_i int) RETURNS varchar(255) CHARSET utf8
DETERMINISTIC
BEGIN
declare result varchar(255) default '';
set result = reverse(substring_index(reverse(substring_index(f_string,f_delimiter,f_i)),f_delimiter,1));
return result;
END$$
DELIMITER;

-- func_split_string函数调试
select substring_index('1,15,4,8',',',3)

select reverse('1,15,4')

select reverse(substring_index('1,15,4,8',',',3))

select substring_index(reverse(substring_index('1,15,4,8',',',3)),',',1)

select reverse(substring_index(reverse(substring_index('1,15,4,8',',',3)),',',1))

select func_split_string('1,15,4,8',',',3)

 


-- 存储过程：proc_split
-- 传入待拆分的字段、绑定的id、分割符；构建临时表，插入已拆分数据
DELIMITER $$
DROP PROCEDURE IF EXISTS `proc_split` $$
CREATE PROCEDURE `proc_split`
(IN f_string_1 varchar(1000),IN f_string_2 varchar(1000),IN rid int, IN f_delimiter varchar(5))
BEGIN
declare s_sum int default 0;
declare i int default 0;
set s_sum = func_split_sum(f_string_1,f_delimiter);
DROP TABLE IF EXISTS `tmp_split`;
create temporary table `tmp_split` (`tmp_1` varchar(128) not null,`tmp_2` varchar(128) not null,`ids` int );
while i < s_sum
do
set i = i + 1;
insert into tmp_split(`tmp_1`,`tmp_2`,`ids`) values (func_split_string(f_string_1,f_delimiter,i),func_split_string(f_string_2,f_delimiter,i),rid);
end while;
select * from tmp_split;
END$$
DELIMITER;


call proc_split('1,15,4,8','1000,15000,4000,8000',2,',')


-- 存储过程：proc_tab_insert
-- 根据临时表拆分结果插入数据
DELIMITER $$
DROP PROCEDURE IF EXISTS `proc_tab_insert` $$
CREATE PROCEDURE `proc_tab_insert` ()
BEGIN
declare orderId varchar(500) default '' ;
declare orderNum varchar(500) default '' ;
declare requestId int default 0;
DECLARE isEnd int DEFAULT 0;
declare cur1 CURSOR FOR SELECT order_id FROM dfs_purchase_request ;
declare cur2 CURSOR FOR SELECT order_num FROM dfs_purchase_request ;
declare cur3 CURSOR FOR SELECT id FROM dfs_purchase_request;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET isEnd = 1;
OPEN cur1 ;
OPEN cur2 ;
OPEN cur3 ;
FETCH cur1 INTO orderId;
FETCH cur2 INTO orderNum;
FETCH cur3 INTO requestId;
-- 清空关联表
-- TRUNCATE dfs_purchase_request_order_relation;
WHILE isEnd<>1 DO
call proc_split(orderId,orderNum,requestId,",");
INSERT INTO dfs_purchase_request_order_relation (order_id,order_num,request_id) SELECT tmp_1,tmp_2,ids from tmp_split;
FETCH cur1 INTO orderId;
FETCH cur2 INTO orderNum;
FETCH cur3 INTO requestId;
END WHILE;
CLOSE cur1;
CLOSE cur2;
CLOSE cur3;
select * from dfs_purchase_request_order_relation;
END$$
DELIMITER;

call proc_tab_insert();

-- 执行完删除存储过程
DROP FUNCTION IF EXISTS `func_split_sum`;
DROP FUNCTION IF EXISTS `func_split_string`;
DROP PROCEDURE IF EXISTS `proc_split`;
DROP PROCEDURE IF EXISTS `proc_tab_insert`;


-- 删除历史重复数据 保留id最大的一条
delete from dfs_purchase_request_order_relation
where id not in (
select t.max_id
from (select max(id) as max_id from dfs_purchase_request_order_relation
group by request_id, order_id) as t);

 

-- 从order表更新order_type
update dfs_purchase_request_order_relation a, dfs_order b
set a.order_type = b.order_num_type
where a.order_id = b.order_id;

 未经允许 禁止转载