-- 创建函数前，设置 set global log_bin_trust_function_creators=TRUE;
-- 新建函数-产生随机的字符串
drop function if exists rand_str;
delimiter ##
create function rand_str(n int) returns varchar(255)
begin
declare chars_str varchar(100) default 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
declare return_str varchar(255) default '';
declare i int default 0;
while i < n do
set return_str=concat(return_str, substring(chars_str, floor(1+rand()*52), 1));
set i=i+1;
end while;
return return_str;
end ##
delimiter ;
-- 新建函数-产生随机的整数
drop function if exists rand_num;
delimiter ##
create function rand_num() returns int
begin
declare i int default 0;
set i=floor(100+rand()*10);
return i;
end ##
delimiter ;
-- 创建存储过程，函数没法单独被调用，只能通过存储过程进行调用；
-- 新建存储过程-调用函数批量插入数据
drop procedure if exists insert_emp;
delimiter ##
create procedure insert_emp(in start_num int, in max_num int)
begin
declare i int default 0;
set autocommit=0;
repeat
set i=i+1;
INSERT INTO mybatis.emp_tbl (emp_id, dept_id, name)VALUES(rand_num(), rand_num(), rand_str(20));
until i = max_num
end repeat;
commit;
end ##
delimiter ;
-- 批量插入10万条数据以进行压力测试
call insert_emp(0, 100000)
;
| 
转播
