批量数据脚本

1.建表

#新建库
create database bigData;

use bigData;

#1建表dept
CREATE TABLE dept(

id lNT 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 ;

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

2.设置参数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

3.创建函数,保证每条数据都不同

3.1随机产生字符串

DELIMITER $$

CREATE FUNCTION rand_string(n lNT)RETURNS VARCHAR(255)

BEGIN

DECLARE chars_str VARCHAR(100)DEFAULT 'abcdefghjikimnoparstuvwxyzABCDEFJHIJKLMNOPQRSTUWWXYz';

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

3.2随机产生部门编号
 

#用于随机产生部门编号

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;
 

4.创建存储过程

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

#删除
# DELIMITER;
# drop PROCEDURE insert_emp;
 

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

#执行存储过程，往dept表添加随机数据

DELIMITER $$

CREATE PROCEDURE insert_dept(IN START INT(10),IN max_num lNT(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 $$
 

5.调用存储过程

5.1dept

DELIMITER；

CALL insert_dept(100,10);

5.2emp

#执行存储过程，往emp表添加50万条数据

DELIMITER;
CALL insert_emp(100001,500000);