Mysql基础学习（二十一）-- 批量插入数据 1000W条

创建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(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;

插入数据之前如果会报错 This function has none of DETERMINISTIC

由于开启过慢查询日志，因为我们开启了 bin-log，我们就必须要为我们的 function 指定一个元素

show variables like "%log_bin_trust_function_creators";

set global log_bin_trust_function_creators=1;
# 想要永久性开启，需要修改 my.cnf 或者 my.ini文件
log_bin_trust_function_creators=1

修改参数

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;
Query OK, 0 rows affected (0.00 sec)

# 再次查看
show variables like "%log_bin_trust_function_creators";
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| log_bin_trust_function_creators | ON    |
+---------------------------------+-------+
1 row in set, 1 warning (0.00 sec)

创建函数

随机字符串函数

CREATE FUNCTION rand_string(n INT) RETURNS VARCHAR(255)
    BEGIN
    DECLARE chars_str VARCHAR(100) DEFAULT 'qwertyuiopasdfghjklzxcvbnmQWERTYUIOPASDFGHJKLZXCVBNM';
    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(100+RAND() * 10);
	RETURN i;
END $$

创建存储过程

插入 emp 数据

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

插入 dept 数据

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 ;
CALL insert_dept(100,10);
CALL insert_dept(10001,500000);