1.创建function
drop function if exists split;
delimiter $$
create function split (s text , sp char(1) , i int)
returns varchar(1024)
deterministic
sql security invoker
begin
declare n int ;
set n = length(s) - length(replace(s, sp, '')) + 1;
if i > n then
return null ;
else
return substring_index(substring_index(s, sp, i) , sp , -1 ) ;
end if;
end$$
delimiter ;
2.创建表
create table `base_split` (
`id` int(11) not null auto_increment,
primary key (`id`)
) engine=innodb auto_increment=22 default charset=utf8;
3.向表插入数据
create procedure init_base_split()
begin
declare num int;
set num = 1;
while num < 10000 do
insert into base_split(id) value (num);
set num = num + 1;
end while;
end;
call init_base_split();
4.查询语句
select split('1,2,3,4,6', ',', id) from base_split
where split('1,2,3,4,6', ',', id) is not null;
或者
select split('1,2,3,4,6', ',', id) from base_split
where id <= length('1,2,3,4,6') - length(replace('1,2,3,4,6', ',', '')) + 1