MySQL用存储过程与函数批量插入数据
20.存储过程与函数
-
函数(FUNCTION)和存储过程(PROCEDURE),最大区别在于函数有返回值,存储过程没有返回值。
-
批量创建数据案例:
# 创库 create database bigData; use bigData; # dept建表 create table dept( id int unsigned primary key auto_increment, deptno mediumint unsigned not null default 0, dname varchar(20) not null default '', loc varchar(13) not null default '' )engine=innodb default charset=gbk; # 建表emp create table emp( id int unsigned primary key auto_increment, empno mediumint unsigned not null default 0, ename varchar(20) not null default '', job varchar(20) not null default '', mgr mediumint unsigned not null default 0, hiredate date not null, sal decimal(7,2) not null, comm decimal(7,2) not null, deptno mediumint unsigned not null default 0 )engine=innodb default charset=GBK;
-
由于进行大批量数据插入,mysql会报一个错误。需要设置参数log_bin_trust_function_creators,它功效用于开启二进制模块,否则会报错:This function has none of DETERMINISTIC...
-
查看log_bin_trust_function_creators是否开启:默认关闭
show variables like 'log_bin_trust_function_creators';
-
开启
1.终端开启: set global log_bin_trust_function_creators=1; # 这样添加参数以后,如果mysqld重启,上述参数会消失 2.永久方式开启: windows 下my.ini [mysqld]添加:log_bin_trust_function_creators=1 linux下 /etc/my.cnf [mysqld]加上 log_bin_trust_function_creators=1
-
创建函数,函数功能随机生成字符串。保证每条数据都不同:
DELIMITER $$ CREATE FUNCTION rand_string(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 $$ # 默认DELIMITER 为 ';',但是我们在编辑我们创建函数使用';'' 会终端我们编辑函数,这样,我们通过 更给DELIMITER为$$,这样就得到解决。 CREATE FUNCTION rand_string(n INT) RETURNS VARCHAR(255) # 创建函数 rand_string 传入n为int类型, 返回值 为varchar(255) DECLARE 变量名称 类型 DEFAULT 默认值 # while循环 WHILE 条件 DO END WHILE; # FLOOR函数: FLOOR(RAND()*2) # 表示0~2 随机生成一个数 # SUBSTRING SUBSTRING('HELLO WORLD',1,5) # 表示截取'HELLO WORLD' 1-5位也就是'HELLO' # CONCAT 用于拼接 SELECT CONCAT('NO.',2);
-
函数:所及产生部门编号
DELIMITER $$ CREATE FUNCTION rand_num() RETURNS INT(5) BEGIN DECLARE i INT DEFAULT 0; SET i=FLOOR(100+RAND()*10); RETURN i; END $$
-
如果删除函数只需执行:drop function 函数名;
-
创建存储过程,往emp表中插入数据的存储过程:
DELIMITER $$ CREATE PROCEDURE insert_emp(IN START INT(10),IN max_num INT(10)) BEGIN DECLARE i INT DEFAULT 0; SET autocommit=0; REPEAT SET i=i+1; INSERT INTO emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) VALUES ((START+i),rand_string(6),'SALESMAN',0001,CURDATE(),2000,400,rand_num()); UNTIL i=max_num END REPEAT; COMMIT; END $$
解释: # SET autocommit=0; 每提交一条数据就会在终端打印,造成爆屏,所以可以关闭autocommit,最后我们再commit # REPEAT ...UNTIL ... END REPEAT; 重复操作,知道UNTIL条件满足就END REPEAT # CURDATE 年月日
-
创建存储过程:往dept表中插入数据的存储过程
DELIMITER $$ CREATE PROCEDURE insert_dept(IN START INT(10),IN max_num INT(10)) BEGIN DECLARE i INT DEFAULT 0; SET autocommit=0; REPEAT SET i=i+1; INSERT INTO dept (deptno,dname,loc) VALUES ((START+i),rand_string(10),rand_string(8)); UNTIL i=max_num END REPEAT; COMMIT; END $$
-
更改DELIMITER ; 成默认
-
调用存储过程插入10条数据 到dept
CALL insert_dept(100,10);
-
调用存储过程插入500000条数据 到emp
CALL insert_emp(100001,500000);
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 用 C# 插值字符串处理器写一个 sscanf
· Java 中堆内存和栈内存上的数据分布和特点
· 开发中对象命名的一点思考
· .NET Core内存结构体系(Windows环境)底层原理浅谈
· C# 深度学习:对抗生成网络(GAN)训练头像生成模型
· 趁着过年的时候手搓了一个低代码框架
· 本地部署DeepSeek后,没有好看的交互界面怎么行!
· 为什么说在企业级应用开发中,后端往往是效率杀手?
· 用 C# 插值字符串处理器写一个 sscanf
· 乌龟冬眠箱湿度监控系统和AI辅助建议功能的实现