drop procedure if exists buildtable; delimiter // create procedure buildtable(in str varchar(128),in sep varchar(10)) begin declare cnt smallint(4) default 0; create table if not exists tmptable(id smallint(4) not null default 0, val varchar(32) not null default '' )engine = myisam default character set = utf8; truncate table tmptable;set cnt = length(str) - length(replace(str,sep,'')) + 1; set @i = 1; while @i <= cnt do set @tmp = substring_index(substring_index(str,sep,@i),sep,-1); insert into tmptable(id,val) values(@i,@tmp); set @i = @i + 1; end while;select * from tmptable; end; // delimiter ;call buildtable('yuan.yong.zhi','.');
set @i=0; select @i:=@i+1,mid(a.a1,b.id,1) as newid from (select 'a,b,c,d,e,f,g' as a1) as a inner join lsb1 b on length('a,b,c,d,e,f,g')>=b.id where mid(a.a1,b.id,1)<>','lsb1:字段ID,内容为1-10000的数字,根据你的需要
set @i=0; select @i:=@i+1 AS ID,mid(a.a1,b.id,1) as value from (select 'a,b,c,d,e,f,g' as a1) as a inner join lsb1 b on length('a,b,c,d,e,f,g')>=b.id where mid(a.a1,b.id,1) <>','
drop procedure if exists buildtable;
delimiter //
create procedure buildtable(in str varchar(128),in sep varchar(10))
begin
declare cnt smallint(4) default 0;
create table if not exists tmptable(id smallint(4) not null default 0,
val varchar(32) not null default ''
)engine = myisam default character set = utf8;
truncate table tmptable;set cnt = length(str) - length(replace(str,sep,'')) + 1;
set @i = 1;
while @i <= cnt do
set @tmp = substring_index(substring_index(str,sep,@i),sep,-1);
insert into tmptable(id,val) values(@i,@tmp);
set @i = @i + 1;
end while;select * from tmptable;
end;
//
delimiter ;call buildtable('yuan.yong.zhi','.');
select @i:=@i+1,mid(a.a1,b.id,1) as newid from (select 'a,b,c,d,e,f,g' as a1) as a
inner join lsb1 b
on length('a,b,c,d,e,f,g')>=b.id where mid(a.a1,b.id,1)<>','lsb1:字段ID,内容为1-10000的数字,根据你的需要
select @i:=@i+1 AS ID,mid(a.a1,b.id,1) as value from (select 'a,b,c,d,e,f,g' as a1) as a
inner join lsb1 b
on length('a,b,c,d,e,f,g')>=b.id where mid(a.a1,b.id,1) <>','