mysql 存储过程 模拟 php的explode 函数

今天 想模拟数据 为了 数据库分区用，想直接用mysql 实现 但是没有类似于php的explode 函数

gold.txt内容如下：

activity_find_back
add_jjc_chalenge_times
all_server_cup_egg
all_server_cup_encourange
all_server_cup_flower
all_server_cup_praise
all_server_cup_support
amulet_advance
amulet_growup_growup
amulet_growup_soul
amulet_growup_spirit
amulet_refresh_skill_book
equipment_lingyun

-- 获取有字符串 如上-面的例子 此方法会去掉'\n'

drop FUNCTION if exists `func_get_split_string`;
CREATE DEFINER=`pay_center`@`%` FUNCTION `func_get_split_string`( f_string varchar(1000),f_delimiter varchar(5),f_order int) RETURNS varchar(255) CHARSET utf8
DETERMINISTIC
BEGIN
declare result varchar(255) default '';
set result = reverse(substring_index(reverse(substring_index(f_string,f_delimiter,f_order)),f_delimiter,1));
return result;
END

=======================================================================

-- 根据'\n'为分割 统计有多少个
drop FUNCTION if exists `func_get_split_string_total`;
CREATE DEFINER=`pay_center`@`%` FUNCTION `func_get_split_string_total`(
f_string varchar(1000),f_delimiter varchar(5)
) RETURNS int(11)
DETERMINISTIC
BEGIN

-- Get the total number of given string.
return 1+(length(f_string) - length(replace(f_string,f_delimiter,'')));
END

=========================================================================

-- 执行
drop procedure if exists `sp_print_result`;
CREATE DEFINER=`pay_center`@`%` procedure `sp_print_result`(
 IN rand_str varchar(10000),IN f_delimiter varchar(5)
)

set @rand_str = load_file('/tmp/gold.txt');

drop procedure if exists proce_range_partition_by_primary;
create procedure proce_range_partition_by_primary(count int(11),rand_str varchar(1000),f_delimiter char(5))
begin
declare i int(11)  default 0;
declare j int(11)  default 0;

declare cnt int default 0;

//这一行是注视

-- username     id     uid     roleid     changecount     newcount     reason     create_time     type_str

set cnt = func_get_split_string_total(rand_str,f_delimiter);

select cnt;

-- 因为mysqll 里没有类似于 php的 获取一个数组的随即单元的函数，所以我存到了 内存表里

drop table if exists tmp_print;
create temporary table tmp_print (reason varchar(30)character set utf8 default null)engine=memory default CHARSET=utf8;
while j < cnt
do
set j = j + 1;
insert into tmp_print(`reason`) values (func_get_split_string(rand_str,f_delimiter,j));
end while;
select * from tmp_print;
while i<= count do
set @i_s = i;
set @str='QWERTYUIOPLKJHGFDSAZXCVBNMqwertyuioplkjhgfdsazxcvbnm1234567890';
set @roleid = bin(@i_s);

set @mod = mod(@i_s,2);
if @mod is null then
 set @changecount = @i_s;
 set @newcount =  @i_s/2;
else
set @newcount =  round(@i_s/2);
set @changecount =  -i;
end if;
set i=i+1;

set @username = upper(md5(concat(@i_s,@str)));

-- 随机取出一个值

select * into @reasons from tmp_print order by rand() limit 1;
set @create_time = UNIX_TIMESTAMP()+i;
select @username,@i_s,@roleid,@changecount,@newcount,@reasons,@create_time;
insert into log_role_gold_change_bak set username = @username ,roleid = @roleid,changecount= @changecount, newcount = @newcount,reason = @reasons,create_time = @create_time;
set i=i+1;
end while;

end;