mysql造海量数据

 

1. 造schema表中数据

 

 

TRUNCATE TABLE schema;

DELIMITER $$
DROP PROCEDURE IF EXISTS `create_schema` $$
CREATE PROCEDURE `create_schema`()
BEGIN
	INSERT INTO minas_perform.schema(id,gmt_create,gmt_modified,name)values('dev',now(),now(),'dev');
	INSERT INTO minas_perform.schema(id,gmt_create,gmt_modified,name)values('test',now(),now(),'test');
	INSERT INTO minas_perform.schema(id,gmt_create,gmt_modified,name)values('hz',now(),now(),'hz');
	INSERT INTO minas_perform.schema(id,gmt_create,gmt_modified,name)values('us',now(),now(),'us');
	INSERT INTO minas_perform.schema(id,gmt_create,gmt_modified,name)values('hk',now(),now(),'hk');
	INSERT INTO minas_perform.schema(id,gmt_create,gmt_modified,name)values('in',now(),now(),'in');
END $$
DELIMITER ;

call `create_schema`();

 

 

2. 造主干数据

 

TRUNCATE TABLE application_instance;
DELIMITER $$
DROP PROCEDURE IF EXISTS create_trunk $$
CREATE PROCEDURE create_trunk()
BEGIN
	DECLARE app_num INT DEFAULT 1;
	WHILE app_num <= 100 DO
		START TRANSACTION;
		INSERT INTO minas_perform.application(id,gmt_create,gmt_modified,description,deleted)VALUES(CONCAT('app',app_num),NOW(),NOW(),CONCAT('app',app_num),'N');
		INSERT INTO minas_perform.application_instance(gmt_create,gmt_modified,application_id,schema_id,tag_type,tag_name,deleted)VALUES(NOW(),NOW(),CONCAT('app',app_num),'dev','TRUNK',CONCAT('app',app_num,'.','dev'),'N');
		INSERT INTO minas_perform.application_instance(gmt_create,gmt_modified,application_id,schema_id,tag_type,tag_name,deleted)VALUES(NOW(),NOW(),CONCAT('app',app_num),'test','TRUNK',CONCAT('app',app_num,'.','test'),'N');
		INSERT INTO minas_perform.application_instance(gmt_create,gmt_modified,application_id,schema_id,tag_type,tag_name,deleted)VALUES(NOW(),NOW(),CONCAT('app',app_num),'hz','TRUNK',CONCAT('app',app_num,'.','hz'),'N');
		INSERT INTO minas_perform.application_instance(gmt_create,gmt_modified,application_id,schema_id,tag_type,tag_name,deleted)VALUES(NOW(),NOW(),CONCAT('app',app_num),'us','TRUNK',CONCAT('app',app_num,'.','us'),'N');
		INSERT INTO minas_perform.application_instance(gmt_create,gmt_modified,application_id,schema_id,tag_type,tag_name,deleted)VALUES(NOW(),NOW(),CONCAT('app',app_num),'hk','TRUNK',CONCAT('app',app_num,'.','hk'),'N');
		INSERT INTO minas_perform.application_instance(gmt_create,gmt_modified,application_id,schema_id,tag_type,tag_name,deleted)VALUES(NOW(),NOW(),CONCAT('app',app_num),'in','TRUNK',CONCAT('app',app_num,'.','in'),'N');
		SET app_num = app_num + 1;
	END WHILE;
	COMMIT;
END $$
DELIMITER ;

CALL create_trunk();

 

3. 造分支数据

 

DELIMITER $$
DROP PROCEDURE IF EXISTS create_branch $$
CREATE PROCEDURE create_branch()
BEGIN
	DECLARE app_num INT DEFAULT 1;
	DECLARE branch_num INT DEFAULT 1;
	WHILE app_num <= 100 DO
		START TRANSACTION;
		SET branch_num = 1;
		WHILE branch_num <=100 DO
			INSERT INTO minas_perform.application_instance(gmt_create,gmt_modified,application_id,schema_id,tag_type,tag_name,deleted)VALUES(NOW(),NOW(),CONCAT('app',app_num),'dev','BRANCH',CONCAT('app',app_num,'.','dev','.20110106.',branch_num),'N');
			INSERT INTO minas_perform.application_instance(gmt_create,gmt_modified,application_id,schema_id,tag_type,tag_name,deleted)VALUES(NOW(),NOW(),CONCAT('app',app_num),'test','BRANCH',CONCAT('app',app_num,'.','test','.20110106.',branch_num),'N');
			INSERT INTO minas_perform.application_instance(gmt_create,gmt_modified,application_id,schema_id,tag_type,tag_name,deleted)VALUES(NOW(),NOW(),CONCAT('app',app_num),'hz','BRANCH',CONCAT('app',app_num,'.','hz','.20110106.',branch_num),'N');
			INSERT INTO minas_perform.application_instance(gmt_create,gmt_modified,application_id,schema_id,tag_type,tag_name,deleted)VALUES(NOW(),NOW(),CONCAT('app',app_num),'hk','BRANCH',CONCAT('app',app_num,'.','hk','.20110106.',branch_num),'N');
			INSERT INTO minas_perform.application_instance(gmt_create,gmt_modified,application_id,schema_id,tag_type,tag_name,deleted)VALUES(NOW(),NOW(),CONCAT('app',app_num),'us','BRANCH',CONCAT('app',app_num,'.','us','.20110106.',branch_num),'N');
			INSERT INTO minas_perform.application_instance(gmt_create,gmt_modified,application_id,schema_id,tag_type,tag_name,deleted)VALUES(NOW(),NOW(),CONCAT('app',app_num),'in','BRANCH',CONCAT('app',app_num,'.','in','.20110106.',branch_num),'N');			
			SET branch_num = branch_num + 1;
		END WHILE;
		COMMIT;
		SET app_num = app_num + 1;
	END WHILE;
END $$
DELIMITER ;

CALL create_branch();

   

 

注意一点：

DELIMITER ;中间有一空格，否则报error: 1046