mysql insert 分号_Mysql数据迁移——按分号split一列字段插入另一张表

环境描述：

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，用存储过程完成拆分及插入操作。

代码实现

1 # 函数：func_split_TotalLength2 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 # 计算传入字符串的总length8 return 1+(length(f_string) - length(replace(f_string,f_delimiter,'')));9 END$$10 DELIMITER;11

12 # 函数：func_split13 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 utf817 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 returnresult;22 END$$23 DELIMITER;24

25 # 存储过程：splitString26 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

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 FROMshop_goods ;55 DECLARE cur2 CURSOR FOR SELECT goodsid FROMshop_goods;56 declare CONTINUE HANDLER FOR SQLSTATE '02000' SET tmpName = null;57 --开游标

58 OPENcur1 ;59 OPENcur2;60 FETCH cur1 INTOtmpImage;61 FETCH cur2 INTOimageid;62 --清空带插入的表

63 TRUNCATEshop_goods_image;64 WHILE ( tmpName is not null) DO65 --调取分隔字段的存储过程splitString

66 call splitString(tmpImage,imageid,";");67 INSERT INTO shop_goods_image (location,shop_goods_id) SELECT status,ids fromtmp_split;68 FETCH cur1 INTOtmpImage;69 FETCH cur2 INTOimageid;70 END WHILE;71 CLOSEcur1;72 CLOSEcur2;73 select * fromshop_goods_image;74 END$$75 DELIMITER;76

77 call valinsert();

View Code

图示效果：

执行代码后的结果如图：