Mysql利用儲存過程插入500W條資料

OldBoy~發表於2017-05-17
MySql儲存過程

比如插入1000W資料，不建議一次性插入，比如一次插入500W，分批插入。

建立表

/*部門表*/
CREATE TABLE dept( 
 id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,  /*id*/
 deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0, /*編號*/
 dname VARCHAR(20) NOT NULL DEFAULT "",/*名稱*/
 loc VARCHAR(13) 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 0,/*編號*/
  ename 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=MyISAM DEFAULT CHARSET=utf8;
/*工資級別表*/
CREATE TABLE salgrade( 
 grade MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,
 losal DECIMAL(17,2) NOT NULL,
 hisal DECIMAL(17,2) NOT NULL
)ENGINE=MyISAM DEFAULT CHARSET=utf8;
#定義一個新的命令結束符
delimiter $$

建立函式

Mysql在插入大量資料的時候，Mysql自身會報錯，我們需要設定引數一個二進位制日誌開啟的功能模組。
比如建立函式的時候，會報錯

ERROR 1418 (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 = 1; //注意命令結束符

如果命令方式設定引數，mysql重啟還會失效，想要永久有效必須修改my.cnf檔案

[mysqld]
log_bin_trust_function_creators = 1

好了，下面開始建立函式

#rand_string(n INT) rand_string 是函式名(n INT)  //該函式傳參一個整數
create function rand_string(n INT)
returns varchar(255) 
begin
declare chars_str varchar(100) default 
 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
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 $$
//生成部門號函式
create function rand_num( )
returns int(5)
begin
 declare i int default 0;
 set i = floor(10+rand()*500);
return i;
 end $$

 建立儲存過程

create procedure insert_emp(in start int(10),in max_num int(10))
begin
declare i int default 0;
#set autocommit = 0  //把autocommit設定成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 $$

 修改命令結束符後，呼叫剛剛寫好的函式，從100001號開始,產生5000000記錄，這裡一次插入了

call insert_emp(100001,5000000);

 

相關文章