有些语法触发了文章的格式,影响观看。。。
1. 生成随机字符
delimiter $$ -- 结束符重定义
drop FUNCTION rand_string $$ -- 删除函数rand_string
#-- 下面为创建函数rand_string
CREATE FUNCTION rand_string (n INT)
returns VARCHAR (255)
begin
DECLARE chars_str VARCHAR (100) DEFAULT 'abcdefghijklmnopqrstuvwxyz';
DECLARE return_str VARCHAR (55) 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 ;
delimiter $$ -- 结束符重定义
#-- 下面为创建函数rand_num
drop FUNCTION rand_num $$ -- 删除函数rand_string
CREATE FUNCTION rand_num ()
RETURNS int (5)
begin
DECLARE i int DEFAULT 0;
set i = floor (10+rand()*50);
return i;
end $$
delimiter ;
2. 最简单模板插入DB
delimiter $$ -- 结束符重定义
-- drop PROCEDURE myproc $$
create PROCEDURE myproc()
Begin
DECLARE i int DEFAULT 0;
WHILE i <=10 DO
INSERT INTO user_info (id,money)
VALUES (i, 40+i);
set i = i + 1;
END WHILE;
END $$
delimiter ;
CALL myproc()
示例结果图
3. 插入随机字符如DB模板
delimiter $$ -- 结束符重定义
DROP PROCEDURE insert_pro $$
CREATE PROCEDURE insert_pro (in start int (10), in max_num int (10))
begin
DECLARE i int default start;
# set autocommit = 0 ;
declare taskid varchar(64) default '20190116'; -- 定义变量名
declare ecifno varchar(16) default 'ecif0116';
declare bizseqno varchar(64) default 'biz1016';
while i < max_num do
#REPEAT
set i = i+1;
set taskid = concat('20190116', cast(i as CHAR)); -- concat字符串连加,cast 将int变为字符串
set ecifno = CONCAT('ecif0116',cast(rand_num() as char)); -- rand_num 插入随机数值
set bizseqno = concat('biz1016', rand_string(16)); -- 插入随机字符串
INSERT into user_info(id,money) VALUES (i,bizseqno); -- 根据需要选择等差、随机数、随机字符
##until i = max_num
# end REPEAT;
end WHILE;
COMMIT;
end $$
delimiter ;
call INSERT_pro (0, 5);
#下面为多行注释,如果使用until模式,也可以实现,和while结果一样。
/*
delimiter $$ -- 结束符重定义
# DROP PROCEDURE insert_pro $$
CREATE PROCEDURE insert_pro (in start int (10), in max_num int (10))
begin
DECLARE i int default start;
# set autocommit = 0 ;
declare taskid varchar(64) default '20190116'; -- 定义变量名
declare ecifno varchar(16) default 'ecif0116';
declare bizseqno varchar(64) default 'biz1016';
#while i < max_num do
REPEAT
set i = i+1;
set taskid = concat('20190116', cast(i as CHAR)); -- concat字符串连加,cast 将int变为字符串
set ecifno = CONCAT('ecif0116',cast(rand_num() as char)); -- rand_num 插入随机数值
set bizseqno = concat('biz1016', rand_string(16)); -- 插入随机字符串
INSERT into user_info(id,money) VALUES (i,bizseqno); -- 根据需要选择等差、随机数、随机字符
until i = max_num
end REPEAT;
#end WHILE;
COMMIT;
end $$
delimiter ;
call INSERT_pro (0, 5);
*/
示例图: