mysql 创建十万测试表_用MYSQL的存储过程创建百万级测试数据表

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

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

创建随机编号生成函数

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

创建数据表 -- 部门表 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);

创建数据表 -- 员工表 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;

创建插入测试数据的存储过程

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

调用存储过程，生成百万数据

call insert_emp(10000,10000000);