Mysql利用存储过程插入500W条数据

小程序：扫一扫查出行
【扫一扫了解最新限行尾号】
复制小程序

比如插入1000W数据，不建议一次性插入，比如一次插入500W，分批插入。

创建表

/*部门表*/
CREATE TABLE dept(
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, /*id*/
deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT , /*编号*/
dname VARCHAR() NOT NULL DEFAULT "",/*名称*/
loc VARCHAR() NOT NULL DEFAULT "" /*地点*/
)ENGINE=MyISAM DEFAULT CHARSET=utf8;

/*EMP雇员表*/
CREATE TABLE emp(
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, /*id*/
empno MEDIUMINT UNSIGNED NOT NULL DEFAULT ,/*编号*/
ename VARCHAR() NOT NULL DEFAULT "",/*名字*/
job VARCHAR() NOT NULL DEFAULT "",/*工作*/
mgr MEDIUMINT UNSIGNED NOT NULL DEFAULT ,/*是哪个及编号*/
hiredate DATE NOT NULL,/*入职时间*/
sal DECIMAL(,) NOT NULL,/*薪水*/
comm DECIMAL(,) NOT NULL,/*红利*/
deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT /*部门编号*/
)ENGINE=MyISAM DEFAULT CHARSET=utf8;

/*工资级别表*/
CREATE TABLE salgrade(
grade MEDIUMINT UNSIGNED NOT NULL DEFAULT ,
losal DECIMAL(,) NOT NULL,
hisal DECIMAL(,) NOT NULL
)ENGINE=MyISAM DEFAULT CHARSET=utf8;

#定义一个新的命令结束符
delimiter $$

创建函数

Mysql在插入大量数据的时候，Mysql自身会报错，我们需要设置参数一个二进制日志开启的功能模块。
比如创建函数的时候，会报错

ERROR (HY000): This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declar

假如开启了慢查询日志，因为开启了bin-log，那么我们必须为function指定一个参数

MySQL [test_db]> show variables like 'log_bin_trust_function_creators'; //注意命令结束符
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| log_bin_trust_function_creators | OFF |
+---------------------------------+-------+

set global log_bin_trust_function_creators = ; //注意命令结束符

如果命令方式设定参数，mysql重启还会失效，想要永久有效必须修改my.cnf文件

[mysqld]
log_bin_trust_function_creators =

好了，下面开始创建函数

#rand_string(n INT) rand_string 是函数名(n INT) //该函数传参一个整数
create function rand_string(n INT)
returns varchar()
begin
declare chars_str varchar() default
'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
declare return_str varchar() default '';
declare i int default ;
while i < n do
set return_str=concat(return_str,substring(chars_str,floor(+rand()*),));
set i = i +;
end while;
return return_str;
end $$

//生成部门号函数
create function rand_num( )
returns int()
begin
declare i int default ;
set i = floor(+rand()*);
return i;
end $$

创建存储过程

create procedure insert_emp(in start int(),in max_num int())
begin
declare i int default ;
#set autocommit = //把autocommit设置成0，这样可以只提交一次，否则。。。。。
set autocommit = ;
repeat
set i = i +;
insert into emp (empno, ename,job,mgr,hiredate,sal,comm,deptno) values ((start+i),rand_string(),'SALESMAN',,curdate(),,,rand_num());
until i = max_num
end repeat;
commit;
end $$