mysql批量生成测试表

#mysql 测试数据生成
 #设置超时时间
CREATE DATABASE testdb charset = utf8mb4;
USE testdb;
CREATE TABLE `test_innodb` (
	`id` INT ( 11 ) NOT NULL AUTO_INCREMENT,
	`user_id` VARCHAR ( 20 ) NOT NULL,
	`group_id` INT ( 11 ) NOT NULL,
	`create_time` datetime NOT NULL,
	PRIMARY KEY ( `id` ),
	KEY `index_user_id` ( `user_id` ) USING HASH 
) ENGINE = INNODB AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8mb4;#存储过程 生成

delimiter $$

CREATE FUNCTION `rand_string`(n INT) RETURNS varchar(255) CHARSET latin1
BEGIN 
DECLARE chars_str varchar(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789'; 
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()*62 ),1)); 
SET i = i +1; 
END WHILE; 
RETURN return_str; 
END $$delimiter;#存储过程 插入存储过程插入生成的数据到test_innodb中

delimiter $$
CREATE PROCEDURE `insert_data` ( IN n INT ,In table_num INT) BEGIN
	DECLARE
		table_name VARCHAR ( 200 );

 DECLARE sql_text VARCHAR(10000);

	DECLARE
		i INT DEFAULT 1;
	WHILE
			( i <= n ) DO
			INSERT INTO test_innodb ( user_id, group_id, create_time )
		VALUES
			( rand_string ( 20 ), FLOOR( RAND() * 100 ), now() );
		
		SET i = i + 1;
		
	END WHILE;
	 SET i=0;
	 WHILE
			( i <= table_num ) DO
			
			SET table_name = CONCAT( 'test_table_', i );
		
		SET sql_text = CONCAT( 'CREATE TABLE ', table_name, ' SELECT * from test_innodb ' );
		
		SET @sql_text = sql_text;
		PREPARE stmt 
		FROM
			@sql_text;
		EXECUTE stmt;
		
		SET i = i + 1;
		
	END WHILE;
	 
END $$delimiter;

#mysql 测试数据生成
 #设置超时时间
CREATE DATABASE testdb charset = utf8mb4;
USE testdb;
CREATE TABLE `test_innodb` (
	`id` INT ( 11 ) NOT NULL AUTO_INCREMENT,
	`user_id` VARCHAR ( 20 ) NOT NULL,
	`group_id` INT ( 11 ) NOT NULL,
	`create_time` datetime NOT NULL,
	PRIMARY KEY ( `id` ),
	KEY `index_user_id` ( `user_id` ) USING HASH 
) ENGINE = INNODB AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8mb4;#存储过程 生成

delimiter $$

CREATE FUNCTION `rand_string`(n INT) RETURNS varchar(255) CHARSET latin1
BEGIN 
DECLARE chars_str varchar(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789'; 
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()*62 ),1)); 
SET i = i +1; 
END WHILE; 
RETURN return_str; 
END $$delimiter;
#存储过程 插入存储过程用于插入生成的数据到test_innodb中生成数据

delimiter $$
CREATE PROCEDURE `insert_data` ( IN n INT ,In table_num INT) BEGIN
	DECLARE
		table_name VARCHAR ( 200 );

 DECLARE sql_text VARCHAR(10000);

	DECLARE
		i INT DEFAULT 1;
	WHILE
			( i <= n ) DO
			INSERT INTO test_innodb ( user_id, group_id, create_time )
		VALUES
			( rand_string ( 20 ), FLOOR( RAND() * 100 ), now() );
		
		SET i = i + 1;
		
	END WHILE;
	 SET i=0;
	 WHILE
			( i <= table_num ) DO
			
			SET table_name = CONCAT( 'test_table_', i );
		
		SET sql_text = CONCAT( 'CREATE TABLE ', table_name, ' SELECT * from test_innodb ' );
		
		SET @sql_text = sql_text;
		PREPARE stmt 
		FROM
			@sql_text;
		EXECUTE stmt;
		
		SET i = i + 1;
		
	END WHILE;
	 
END $$delimiter;

#存储过程批量生成表并进行插入test_innodb的数据进行插入
CALL insert_data ( 1000000 ,600);

#删除存储过程

drop PROCEDURE `insert_data`;
drop FUNCTION rand_string;