环境描述:
Mysql5.0.22
navicat10
问题描述:
shopgoods里面的字段image保存着多张相关商品的图片URL地址,如:
/resources/../upload-photo/commodity/20130428/75331367132321640.jpg;/resources/../upload-photo/commodity/20130428/92551367132322015.jpg;/resources/../upload-photo/commodity/20130428/17351367132322046.jpg;/resources/../upload-photo/commodity/20130428/5651367132322203.jpg;/resources/../upload-photo/commodity/20130428/92591367132322234.jpg;
现要将这一字段外键关联到另一张表,将字段分隔(split)为单个路径,并绑定shopgoods的主键id,用存储过程完成拆分及插入操作。
代码实现
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 # 函数:func_split_TotalLength 2 DELIMITER $$ 3 DROP function IF EXISTS `func_split_TotalLength` $$ 4 CREATE FUNCTION `func_split_TotalLength` 5 (f_string varchar(1000),f_delimiter varchar(5)) RETURNS int(11) 6 BEGIN 7 # 计算传入字符串的总length 8 return 1+(length(f_string) - length(replace(f_string,f_delimiter,''))); 9 END$$ 10 DELIMITER; 11 12 # 函数:func_split 13 DELIMITER $$ 14 DROP function IF EXISTS `func_split` $$ 15 CREATE FUNCTION `func_split` 16 (f_string varchar(1000),f_delimiter varchar(5),f_order int) RETURNS varchar(255) CHARSET utf8 17 BEGIN 18 # 拆分传入的字符串,返回拆分后的新字符串 19 declare result varchar(255) default ''; 20 set result = reverse(substring_index(reverse(substring_index(f_string,f_delimiter,f_order)),f_delimiter,1)); 21 return result; 22 END$$ 23 DELIMITER; 24 25 # 存储过程:splitString 26 DELIMITER $$ 27 DROP PROCEDURE IF EXISTS `splitString` $$ 28 CREATE PROCEDURE `splitString` 29 (IN f_string varchar(1000),IN idstr int, IN f_delimiter varchar(5)) 30 BEGIN 31 # 拆分结果 32 declare cnt int default 0; 33 declare i int default 0; 34 set cnt = func_split_TotalLength(f_string,f_delimiter); 35 DROP TABLE IF EXISTS `tmp_split`; 36 create temporary table `tmp_split` (`status` varchar(128) not null,`ids` int ) DEFAULT CHARSET=utf8; 37 while i < cnt 38 do 39 set i = i + 1; 40 insert into tmp_split(`status`,`ids`) values (func_split(f_string,f_delimiter,i),idstr); 41 end while; 42 END$$ 43 DELIMITER; 44 45 # 插入分隔数据 46 DELIMITER $$ 47 DROP PROCEDURE IF EXISTS `valinsert` $$ 48 CREATE PROCEDURE `valinsert` () 49 BEGIN 50 declare imageid int default 0; 51 declare tmpName varchar(100) default '' ; 52 declare tmpImage varchar(500) default '' ; 53 -- 定义游标 54 declare cur1 CURSOR FOR SELECT image FROM shop_goods ; 55 DECLARE cur2 CURSOR FOR SELECT goodsid FROM shop_goods; 56 declare CONTINUE HANDLER FOR SQLSTATE '02000' SET tmpName = null; 57 -- 开游标 58 OPEN cur1 ; 59 OPEN cur2; 60 FETCH cur1 INTO tmpImage; 61 FETCH cur2 INTO imageid; 62 -- 清空带插入的表 63 TRUNCATE shop_goods_image; 64 WHILE ( tmpName is not null) DO 65 -- 调取分隔字段的存储过程splitString 66 call splitString(tmpImage,imageid,";"); 67 INSERT INTO shop_goods_image (location,shop_goods_id) SELECT status,ids from tmp_split; 68 FETCH cur1 INTO tmpImage; 69 FETCH cur2 INTO imageid; 70 END WHILE; 71 CLOSE cur1; 72 CLOSE cur2; 73 select * from shop_goods_image; 74 END$$ 75 DELIMITER; 76 77 call valinsert();
图示效果:
执行代码后的结果如图: