MySQL查询测试环境创建脚本

        在研究MySQL数据库的查询优化时，需要先有一个测试环境，然后再进行分析。

        自己在生产环境上测试了一个可以顺利执行的mysql数据库脚本，内容如下：

# The sql script is used to create a test mysql database.
# Begtin the sql script.
# Create test database;
create databases test;
use test;
show tables;
select now() as Systemtime;

# Create 5 test tables in test database;
DROP TABLE IF EXISTS `t1`;
DROP TABLE IF EXISTS `t2`;
DROP TABLE IF EXISTS `t3`;
DROP TABLE IF EXISTS `t4`;
DROP TABLE IF EXISTS `t5`;
CREATE TABLE t1(id1 INT,a1 INT,b1 INT,PRIMARY KEY(id1));
CREATE TABLE t2(id2 INT,a2 INT,b2 INT);
CREATE TABLE t3(id3 INT UNIQUE,a3 INT,b3 INT);
CREATE TABLE t4(id4 INT,a4 INT,b4 INT);
CREATE TABLE t5(id5 INT UNIQUE,a5 INT,b5 INT);
show tables;
select now() as Systemtime;

# Create procedure to insert test data into test tables.
select name from mysql.proc where db='test';
show procedure status where db='test';
drop procedure proc01;
drop procedure proc02;
drop procedure proc03;
drop procedure proc04;
drop procedure proc05;

# Create proc01 to insert table t1 into 10000 rows 
DELIMITER //  
CREATE PROCEDURE proc01()  
begin 
declare var int;  
set var=0;  
while var<10000 do  
insert into t1 values(var,var,var);  
set var=var+1;  
end while;  
end;  
//  
DELIMITER ;
select count(*) from t1;
call proc01;
select count(*) from t1;
select now() as Systemtime;

# Create proc02 to insert table t2 into 100 rows 
DELIMITER //  
CREATE PROCEDURE proc02()  
begin 
declare var int;  
set var=0;  
while var<100 do  
insert into t2 values(var,var,var);  
set var=var+1;  
end while;  
end;  
//  
DELIMITER ;
select count(*) from t2;
call proc02;
select count(*) from t2;
select now() as Systemtime;

# Create proc03 to insert table t3 into 100 rows 
DELIMITER //  
CREATE PROCEDURE proc03()  
begin 
declare var int;  
set var=0;  
while var<100 do  
insert into t3 values(var,var,var);  
set var=var+1;  
end while;  
end;  
//  
DELIMITER ;
select count(*) from t3;
call proc03;
select count(*) from t3;
select now() as Systemtime;

# Create proc04 to insert table t4 into 7 rows 
DELIMITER //  
CREATE PROCEDURE proc04()  
begin 
declare var int;  
set var=0;  
while var<7 do  
insert into t4 values(var,var,var);  
set var=var+1;  
end while;  
end;  
//  
DELIMITER ;
select count(*) from t4;
call proc04;
select count(*) from t4;
select now() as Systemtime;

# Create proc05 to insert table t5 into 10 rows 
DELIMITER //  
CREATE PROCEDURE proc05()  
begin 
declare var int;  
set var=0;  
while var<10 do  
insert into t5 values(var,var,var);  
set var=var+1;  
end while;  
end;  
//  
DELIMITER ;
select count(*) from t5;
call proc05;
select count(*) from t5;
select now() as Systemtime;

# Drop the 5 procedure
select name from mysql.proc where db='test';
show procedure status where db='test';
drop procedure proc01;
drop procedure proc02;
drop procedure proc03;
drop procedure proc04;
drop procedure proc05;
select name from mysql.proc where db='test';
show procedure status where db='test';
select now() as Systemtime;

# The end of sql script.

            以上脚本是在test数据库都没有建立的情况下，执行直接该脚本创建test数据库，然后创建测试表格，插入数据。

           创建后，也可以使用下面的命令将文件导出：

mysqldump -uroot -p'123456' -S /data/mysqldata/3306/mysql.sock test t1 t2 t3 t4 t5 > /tmp/test_env.sql

         我已经在测试环境中，执行上面的命令，并将备份集保存在附件中，可以通过这个URL下载：http://download.youkuaiyun.com/detail/yumushui/8266469

           以后需要创建test测试环境的话， 只需要登录到test数据库上执行下面的命令即可：

msyql -uroot -p
use test;
source test_env.sql