批量数据脚本

建表

#新建库
create database bigData;
use bigData; 

#1建表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;

设置参数log_bin_trust_function_creators

创建函数，假如报错：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;

这样添加了参数以后，如果mysqld重启，上述参数又会消失，永久方法：
windows：my.ini[mysqld]加上log_bin_trust_function_creators=1
linux：/etc/my.cnf下my.cnf[mysqld]加上log_bin_trust_function_ creators=1

创建函数，保证每条数据都不同

#随机产生字符串
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 $$
#假如要删除
#drop function rand_string;

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

创建存储过程

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

#执行存储过程，往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),'销售',(START+i-1),CURDATE(),FLOOR(RAND() * 10000),ROUND((RAND() * 100),2),rand_num());
UNTIL i = max_num
END REPEAT;
COMMIT;
END $$

#删除
#ELIMITER;
#DROP PROCEDURE insert_emp;

创建往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 $$

#删除
#ELIMITER;
#DROP PROCEDURE insert_dept;

调用存储过程

dept

DELIMTER;
CALL insert_dept(100,10);

emp

DELIMTER;
#执行存储过程，往emp表添加12千万条数据
CALL insert_emp(100001,1000000);
CALL insert_emp(1100001,1000000);
CALL insert_emp(2100001,1000000);
CALL insert_emp(3100001,1000000);
CALL insert_emp(4100001,1000000);
CALL insert_emp(5100001,1000000);
CALL insert_emp(6100001,1000000);
CALL insert_emp(7100001,1000000);
CALL insert_emp(8100001,1000000);
CALL insert_emp(9100001,1000000);
CALL insert_emp(10100001,1000000);
CALL insert_emp(11100001,1000000);