mysql-测试数据

创建测试数据

create database backuptest;
use backuptest;


# 创建innodb类型的数值辅助表和插入数据的存储过程
CREATE TABLE num_innodb (n INT NOT NULL PRIMARY KEY) ENGINE = INNODB ;

DROP PROCEDURE IF EXISTS proc_num2;
DELIMITER $$
CREATE PROCEDURE proc_num2 (num INT) 
BEGIN
    DECLARE rn INT DEFAULT 1 ;
    TRUNCATE TABLE backuptest.num_innodb ;
    INSERT INTO backuptest.num_innodb VALUES(1) ;
    dd: WHILE rn * 2 < num DO 
        BEGIN
            INSERT INTO backuptest.num_innodb 
            SELECT rn + n FROM backuptest.num_innodb;
            SET rn = rn * 2 ;
        END ;
    END WHILE dd;
    INSERT INTO backuptest.num_innodb 
    SELECT n + rn 
    FROM backuptest.num_innodb 
    WHERE n + rn <= num ;
END ;
$$
DELIMITER ;

# 分别向两个数值辅助表中插入100W条数据
CALL proc_num2 (10000) ;


select * from num_innodb limit 20;

ruboob

CREATE TABLE IF NOT EXISTS `runoob_tbl`(
   `runoob_id` INT UNSIGNED AUTO_INCREMENT,
   `runoob_title` VARCHAR(100) NOT NULL,
   `runoob_author` VARCHAR(40) NOT NULL,
   `submission_date` DATE,
   PRIMARY KEY ( `runoob_id` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO runoob_tbl (runoob_title, runoob_author, submission_date) VALUES ("PHP", "run", NOW());
INSERT INTO runoob_tbl (runoob_title, runoob_author, submission_date) VALUES ("MySQL", "run-py", NOW());
INSERT INTO runoob_tbl (runoob_title, runoob_author, submission_date) VALUES ("JAVA", "RUNOOB.COM", '2016-05-06');
    
select * from runoob_tbl;

DROP TABLE runoob_tbl;

GRANT SELECT,INSERT,DELETE,UPDATE ON *.* TO app_readwrite;

[root@bx173.sae ~]# mysql -h10.13.56.173 -P21000 -usae_rds_ha -p2GQHNg6pIZ1dZaDlze7uzweSx4LxEyLY5qYFlxx0  -e "FLUSH TABLES WITH READ LOCK"