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

最新推荐文章于 2025-01-06 20:55:49 发布

原创最新推荐文章于 2025-01-06 20:55:49 发布 · 1.9k 阅读

0 ·

CC 4.0 BY-SA版权

mysql 专栏收录该内容

26 篇文章

订阅专栏

本文介绍了如何在MySQL中使用存储过程模拟PHP的explode函数，以实现字符串分割功能。通过创建两个函数`func_get_split_string`和`func_get_split_string_total`，分别用于获取指定分隔符和位置的子字符串以及统计分割后的字符串数量。此外，还提供了一个存储过程`sp_print_result`，演示了如何结合这两个函数处理文件内容并进行随机选择。

今天想模拟数据为了数据库分区用，想直接用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;