mysql实现字符串分割

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