MySQL查询测试环境创建脚本

r囧r小猫 2022-08-06 03:07 126阅读 0赞

在研究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.csdn.net/detail/yumushui/8266469

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

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