MYSQL用存储过程创建百万级测试数据表- JAVA

1.创建随机字符串函数，便于创建名称

-- delimiter $$
-- 执行出现“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)” 错误时需要先设置log_bin_trust_function_creators
-- set global log_bin_trust_function_creators=1;
 
DROP function if EXISTS rand_string;
#创建一个指定字符个数的函数
create function rand_string(n INT)
#返回字符串，注意：此处关键字是returns 而不是return
returns varchar(255)
BEGIN
    #定义一个临时变量，给变量赋值'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ'
    DECLARE chars_str varchar(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
    # 定义返回结果字符串
    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

2.创建随机编号生成函数

drop function if exists rand_num;
CREATE function rand_num()
returns int(5)
BEGIN
	DECLARE i int default 0;
	set i = FLOOR(10+RAND()*500);
	return i;
END

1
2
3
4
5
6
7
8
9

3.创建数据表 – 部门表 dept

drop table if EXISTS dept;
create table dept (
	deptno MEDIUMINT UNSIGNED not null DEFAULT 0,
  dname varchar(20) default '',
	ioc varchar(13) default''
) ENGINE = myisam default CHARSET ='utf8';
ALTER table dept add PRIMARY key(deptno);

1
2
3
4
5
6
7
8

4.创建数据表 – 员工表 emp

drop table if EXISTS emp;
CREATE TABLE `emp` (
  `empno` mediumint(8) unsigned NOT NULL DEFAULT '0',
  `ename` varchar(20) NOT NULL DEFAULT '',
  `job` varchar(9) NOT NULL DEFAULT '',
  `mgr` mediumint(8) unsigned NOT NULL DEFAULT '0' COMMENT '上级编号',
  `hiredate` date NOT NULL COMMENT '入职日期',
  `salary` decimal(7,2) NOT NULL COMMENT '薪水',
  `comm` decimal(7,2) NOT NULL COMMENT '红利',
  `deptno` mediumint(8) unsigned NOT NULL DEFAULT '0' COMMENT '部门编号'
) ENGINE=MYISAM DEFAULT CHARSET=utf8;

1
2
3
4
5
6
7
8
9
10
11
12

5.插入测试数据

drop PROCEDURE IF EXISTS insert_emp ;
 
CREATE PROCEDURE insert_emp (in start_no int(10),in max_num int(10))
 
BEGIN
	DECLARE i int default 0;
	# 设置自动提交为false
	set autocommit =0;
	# 开启循环
	REPEAT
		set i = i+1;
		insert into emp values((start_no+i),rand_string(6),'SALESMAN',0001,CURDATE(),rand_num(),400,0002);
	
	UNTIL i=max_num
	END REPEAT;
END

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17