前言:需要创建500w条数据,用来测试sql的效率,执行时间等。由于网上没有找到一键执行的,于是整理了一下网上的答案,生成500w条数据的代码如下
Code:
/*部门表*/
CREATE TABLE dept
(
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, /*id*/
deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0, /*编号*/
dname VARCHAR(20) NOT NULL DEFAULT '',/*名称*/
loc VARCHAR(13) NOT NULL DEFAULT '' /*地点*/
) ENGINE = MyISAM
DEFAULT CHARSET = utf8;
/*EMP雇员表*/
CREATE TABLE emp
(
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, /*id*/
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 = MyISAM
DEFAULT CHARSET = utf8;
/*工资级别表*/
CREATE TABLE salgrade
(
grade MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,
losal DECIMAL(17, 2) NOT NULL,
hisal DECIMAL(17, 2) NOT NULL
) ENGINE = MyISAM
DEFAULT CHARSET = utf8;
#定义一个新的命令结束符
delimiter $$;
#rand_string(n INT) rand_string 是函数名(n INT) //该函数传参一个整数
create function rand_string(n INT)
returns varchar(255)
begin
declare chars_str varchar(100) default
'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
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(10 + rand() * 500);
return i;
end $$;
#创建存储过程
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 $$;
call insert_emp(100001, 5000000);