Mysql批量插入1000w条数据

• 1 登陆mysql，新建bigData库

//登录MySQL
[root@hadoop1 ~]# cd /usr/database/mysql5.7/bin
[root@hadoop1 bin]# ./mysql -u root -p
Enter password:
//SUCCESS
create database bigData;
use bigData;

• 2 新建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 ;

• 3 新建 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(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=GBK ;

• 4 设置参数 log_bin_trust_function_creators

创建函数，假如报错: This function has none of DETERMINISTI.....
#由于开启过慢查询日志，因为我们开启了bin-log, 我们就必须为我们的function指定一个参数。
show variables like 'log_bin_trust_function_creators';

set global log_bin_trust_function_creators=1;

#这样添加了参数以后，如果mysqld重启，. 上述参数又会消失，

• 5 创建函数，保证每条数据都不同
  随机产生字符串

DELIMITER $$
     CREATE FUNCTION rand_string(n INT) RETURNS VARCHAR(255)
     BEGIN
     DECLARE chars_str VARCHAR(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
     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 $$
//sql默认每一行的分号结尾，函数代码中有很多分号，为了让程序整体执行，暂时用$$结尾；

随机产生部门编号

#用于随机产生部门编号
DELIMITER $$
CREATE FUNCTION rand_num( )
RETURNS INT(5)
BEGIN
 DECLARE i INT DEFAULT 0;
SET i= FLOOR(100+RAND()*10);
RETURN i;
END $$

• 6 创建存储过程

创建往emp表中插入数据的存储过程

DELIMITER $$
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 $$

创建往dept表中插入数据的存储过程

  #执行存储过程，往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 $$

6 调用存储过程

//DELIMITER ; 恢复默认；结尾
CALL insert_dept(100,10);

执行存储过程，向emp表中插入50万条数据，差多了怕机器挂了

CALL insert_emp(100001,500000)

END 2018/10/3-15:16