MySQL批量插入数据(load data 和存储过程方式)
发布日期:2021-06-29 21:37:53
浏览次数:3
分类:技术文章
本文共 3814 字,大约阅读时间需要 12 分钟。
文章内容来自于: |
MySQL批量插入数据最简单的就是循环遍历,调用多次INSERT语句不就可以插入多条记录了吗!但是这种方法会增加服务器的负荷,因为,执行每一次SQL,服务器都要同样对SQL进行分析、优化等操作。MySQL提供了另一种解决方案,就是使用一条INSERT语句来插入多条记录。这并不是标准的SQL语法,因此只能在MySQL中使用。
文章目录
方式一、load data infile命令
首先修改my.ini(linux是/etc/my.cnf)下secure-file-priv为你存放txt的地址:
secure-file-priv="D:/mysql_import_data/"
然后就可以使用命令导入了
load data infile "D:\mysql_import_data\\文件名.txt" into table 表 fields terminated by '|' lines terminated by '\n' ;
这里要注意 \\文件 这里,一定是双斜杠,否则导入会出错,如果出现中文乱码,先检查数据库本身编码问题,其次检查txt文件编码,都为utf8即可。
方式二、通过插入存储过程方式插入
使用脚本进行大数据量的批量插入,对特定情况下测试数据集的建立非常有用。
创建数据表1、创建tb_dept_bigdata(部门表)。
create table tb_dept_bigdata(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=utf8;
2、创建tb_emp_bigdata(员工表)。
create table tb_emp_bigdata(id int unsigned primary key auto_increment,empno mediumint unsigned not null default 0,/*编号*/empname varchar(20) not null default '',/*名字*/job varchar(9) 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=utf8;
3、开启log_bin_trust_function_creators参数。
由于在创建函数时,可能会报:This function has none of DETERMINISTIC.....
因此我们需开启函数创建的信任功能。
show variables like '%log_bin_trust_function_creators%';可通过
set global log_bin_trust_function_creators=1
的形式开启该功能,也可通过在my.ini(linux中是my.cnf)
中永久配置的方式开启该功能,在[mysqld]
下配置log_bin_trust_function_creators=1
。 3.1 创建函数,保证每条数据都不同
3.1.1 创建随机生成字符串的函数。
delimiter $$drop function if exists rand_string; //如果存在函数rand_string,则删除create function rand_string(n int) returns varchar(255) //创建函数rand_string,带一个int参数,返回varchar对应到java就是string了begindeclare chars_str varchar(52) default 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ'; //申明字符串declare return_str varchar(255) default '';declare i int default 0;while i
3.1.2 创建随机生成编号的函数。
delimiter $$drop function if exists rand_num;create function rand_num() returns int(5)begindeclare i int default 0;set i=floor(100+rand()*100);return i;end $$
3.2 创建存储过程用于批量插入数据
3.2.1 创建往tb_dept_bigdata表中插入数据的存储过程。
delimiter $$drop procedure if exists insert_dept;create procedure insert_dept(in start int(10),in max_num int(10))begindeclare i int default 0;set autocommit=0;repeatset i=i+1;insert into tb_dept_bigdata (deptno,dname,loc) values(rand_num(),rand_string(10),rand_string(8));until i=max_numend repeat;commit;end $$
3.2.2 创建往tb_emp_bigdata表中插入数据的存储过程。
delimiter $$drop procedure if exists insert_emp;create procedure insert_emp(in start int(10),in max_num int(10))begindeclare i int default 0;set autocommit=0;repeatset i=i+1;insert into tb_emp_bigdata (empno,empname,job,mgr,hiredate,sal,comm,deptno) values((start+i),rand_string(6),'developer',0001,curdate(),2000,400,rand_num());until i=max_numend repeat;commit;end $$
3.2.3 具体执行过程批量插入数据
- 首先执行随机生成字符串的函数。
- 然后执行随机生成编号的函数。
- 使用命令查看函数是否创建成功。
查看函数是否创建成功,这里我这边linux下查看的比较混乱,就直接使用阳哥的图了。
show function status;查看存储过程是否创建成功
show procedure status;执行存储过程,插入数据 a.首先执行insert_dept存储过程。
delimiter ; //注意中间的空格,这个就是将mysql语句结束改回分号;因为上面创建存储过程时候改成了$$call insert_dept(100,100); //调用存储过程插入100条数据select count(*) from tb_dept_bigdata; //查看记录条数说明:deptno的范围[100,110),因为deptno的值使用了rand_num()函数。
b.然后执行insert_emp存储过程。
delimiter ; call insert_emp(100,300);select count(*) from tb_emp_bigdata;说明:tb_emp_bigdata表中deptno编号的范围[100,110),使用rand_num()函数。
注:对于部门表的deptno和员工表中deptno的数据都使用了rand_num()函数进行赋值,确保两边的值能对应。
4、删除函数与存储过程
4.1 删除函数
drop function rand_num;drop function rand_string;
4.2 删除存储过程
drop procedure insert_dept;drop procedure insert_emp;
5、总结
- 注意mysql中函数和存储过程的写法。
- 注意存储过程的调用,
call procedurename
。 - 注意开启对函数的信任,
log_bin_trust_function_creators
参数。
转载地址:https://dh-butterfly.blog.csdn.net/article/details/110945255 如侵犯您的版权,请留言回复原文章的地址,我们会给您删除此文章,给您带来不便请您谅解!
发表评论
最新留言
网站不错 人气很旺了 加油
[***.192.178.218]2024年05月01日 09时44分57秒
关于作者
喝酒易醉,品茶养心,人生如梦,品茶悟道,何以解忧?唯有杜康!
-- 愿君每日到此一游!
推荐文章
css技巧--给选中的tab加下划线
2021-07-03
css技巧---位置中间的竖线|垂直居中
2021-07-03
css技巧---电子表体字体引入
2021-07-03
随笔---如何启动Redis
2021-07-03
css技巧---menu菜单加new
2021-07-03
freemarker模板当标签内的元素为空报错解决方案
2021-07-03
如何解决中文乱码问题
2021-07-03
关于如何彻底卸载SQL SERVER2005 2008
2021-07-03
前端技巧:如何让一个div 在另一个div上面显示,却不会影响下一个div的位置?
2021-07-03
前端技巧:echarts中国地图外边框设置阴影投影效果------荧光效果 随笔
2021-07-03
随笔:简单的蒙版加载页面实现
2021-07-03
处理echarts地图省份坐标重叠的方法
2021-07-03
获取浏览器可见窗口大小(转载)
2021-07-03
给文字加一个渐变色
2021-07-03
使用网格在父元素中水平和垂直地居中定位子元素
2021-07-03
Box-sizing reset
2021-07-03
underscore学习笔记一
2021-07-03