在mySql中拆分字符串

最新推荐文章于 2019-07-23 15:50:25 发布

青龙白虎米老鼠

最新推荐文章于 2019-07-23 15:50:25 发布

阅读量873

点赞数

分类专栏：数据库文章标签： mysql insert table sql c

本文链接：https://blog.csdn.net/lhy55040817/article/details/8103724

版权

数据库专栏收录该内容

10 篇文章 0 订阅

订阅专栏

# DROP TEMPORARY TABLE IF EXISTS test_tim; 删表用
create TEMPORARY table test_tim (
   id int,
   a1 varchar(128),
   a2 decimal(16,2),
   a21 decimal(16,2),
   a3 varchar(1024),
   a4 datetime,
   a5 int,
   a6 int
);

#DROP TEMPORARY TABLE IF EXISTS test; 删表用
create TEMPORARY table test (
   id int,
   a1 varchar(128),
   a2 decimal(16,2),
   a21 decimal(16,2),
   a3 varchar(1024),
   a4 datetime,
   a5 int,
   a6 int
);

#插入样本

insert into test values(136,'test',1234567.8,987654.2,'com.a.b,1,6,error,IOEofFilea,b','2012/10/22 12:00',0,4);
insert into test values(137,'test2',1234567.9,987654.3,'com.a.b,1,6,error,IOEofFileb,c','2012/10/22 13:00',1,5);

#校验

select * from test;

136	test	1234567.80	987654.20	com.a.b,1,6,error,IOEofFilea,b	2012-10-22 12:00:00	0	4
137	test2	1234567.90	987654.30	com.a.b,1,6,error,IOEofFileb,c	2012-10-22 13:00:00	1	5

#处理逻辑

delimiter $$;
create procedure lhy()
READS SQL DATA
begin
   declare did int;
   declare da1 varchar(128);
   declare da2 decimal(16,2);
   declare da21 decimal(16,2);
   declare da3 varchar(1024);
   declare da4 datetime;
   declare da5 int;
   declare da6 int;
   DECLARE STOP INT DEFAULT 0;

   declare _cs cursor for    select id, a1, a2, a21, a3,a4,a5,a6 from test;

   DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET STOP=1;

   open _cs;
   fetch _cs into did, da1, da2, da21, da3,da4,da5,da6;
   while STOP<>1 DO
       begin
           declare i int;
           declare s int;
           set i=1 ;
           set s=1 ;
           while(i>0) DO
               set i=LOCATE(',',da3,s) ;
               if(i>0) then
                       insert test_tim values(did, da1, da2, da21, substring(da3,s,i-s),da4,da5,da6 ) ;
               end if;
               if(i<=0) then
                       insert test_tim values(did, da1, da2, da21, substring(da3,s,length(da3)-s+1),da4,da5,da6) ;
               end if;
               set s = i + 1   ;
           end while;
       end;
   fetch _cs into did, da1, da2, da21, da3,da4,da5,da6;
   END while;
   close _cs;
end$$;

#drop procedure lhy;删除procedure 用
#call lhy; 呼叫lhy用

#校验结果用--先create ,再call,再校验结果

#select * from test_tim;

136	test	1234567.80	987654.20	com.a.b	2012-10-22 12:00:00	0	4
136	test	1234567.80	987654.20	1	2012-10-22 12:00:00	0	4
136	test	1234567.80	987654.20	6	2012-10-22 12:00:00	0	4
136	test	1234567.80	987654.20	error	2012-10-22 12:00:00	0	4
136	test	1234567.80	987654.20	IOEofFilea	2012-10-22 12:00:00	0	4
136	test	1234567.80	987654.20	b	2012-10-22 12:00:00	0	4
137	test2	1234567.90	987654.30	com.a.b	2012-10-22 13:00:00	1	5
137	test2	1234567.90	987654.30	1	2012-10-22 13:00:00	1	5
137	test2	1234567.90	987654.30	6	2012-10-22 13:00:00	1	5
137	test2	1234567.90	987654.30	error	2012-10-22 13:00:00	1	5
137	test2	1234567.90	987654.30	IOEofFileb	2012-10-22 13:00:00	1	5
137	test2	1234567.90	987654.30	c	2012-10-22 13:00:00	1	5