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_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();

View Code

图示效果：

执行代码后的结果如图：