mysql生成百万条测试数据的sql

mysql生成百万条测试数据的sql

在公司测试中难免使用几百万条数据测试，这就需要向自己的数据库中短时间插入百万数据，在这就有脚本可以现成使用，本案例用的是员工和部门的关系。

1、新建库

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 ;

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

3、设置参数

当开启二进制日志后(可以执行show variables like 'log_bin’查看是否开启)，如果变量log_bin_trust_function_creators为OFF，那么创建或修改存储函数就会报“ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you might want to use the less safe log_bin_trust_function_creators variable)”这样的错误。

show variables like 'log_bin_trust_function_creators';

set global log_bin_trust_function_creators=1;

4、创建函数

随机产生字符串

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

随机产生数字做部门编号

DELIMITER $$

CREATE FUNCTION rand_num( )

RETURNS INT(5)

BEGIN

DECLARE i INT DEFAULT 0;

SET i = FLOOR(100+RAND()*10);

RETURN i;

END $$

5、创建存储过程

向部门中插入数据过程

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

向员工表中插入数据过程

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

6、调用存储过程进行插入数据

DELIMITER ;

#执行存储过程，往dept中插入10条数据

CALL insert_dept(100,10);

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

DELIMITER ;

CALL insert_emp(100001,1000000);