在Mysql中造数据的时候， 需要写大量的存储过程来完成。
一、为表PERSON_PERMISSION造数据：
1、数据来自于accout表中的400万数据
2、person_permission表中的数据， 其中vaccount_id为account表中的member_id, 且将每个member_id对应10个vaccount_id，其中0~4的前5个的status为‘ENABLED’状态，5~9的后5个的status为‘DISABLED’状态； 在每5个的前4个的GRANDTED_TYPE为‘ENTERPRISE’，后1个为‘PERSON’； ACL字段始终为1，PERMISSION_ID为21、PERMISSION_TYPE为‘PACKAGE’，GMT_CREATED、 GMT_MODIFIED的时间均为当前时间。
解决方案：
DELIMITER $$
DROP PROCEDURE IF EXISTS `PERSON_PERMISSION_SUBSCRIPTION` $$
CREATE PROCEDURE `PERSON_PERMISSION_SUBSCRIPTION`()
BEGIN
DECLARE iloop SMALLINT DEFAULT 0;
DECLARE iNum MEDIUMINT DEFAULT 0;
DECLARE iMod MEDIUMINT DEFAULT 0;
DECLARE strMemberID VARCHAR(40) DEFAULT 0;
DECLARE Cur_account CURSOR FOR SELECT member_id FROM account;
OPEN Cur_account;
FETCH Cur_account INTO strMemberID;
WHILE iNum<4001 DO
START TRANSACTION;
WHILE iloop<=1000 DO
SET iMod=0;
WHILE iMod < 10 DO
IF iMod < 4 THEN
INSERT INTO PERSON_PERMISSION(person_id,vaccount_id, acl, permission_id, permission_type, gmt_create, gmt_modified, STATUS, granted_type)
VALUES(CONCAT(strMemberID, '_P', iMod),strMemberID,1,21, 'PACKAGE',NOW(), NOW(), 'ENABLED', 'ENTERPRISE');
ELSEIF iMod = 4 THEN
INSERT INTO PERSON_PERMISSION(person_id,vaccount_id, acl, permission_id, permission_type, gmt_create, gmt_modified, STATUS, granted_type)
VALUES(CONCAT(strMemberID, '_P', iMod),strMemberID,1,21, 'PACKAGE',NOW(), NOW(), 'ENABLED', 'PERSON');
ELSEIF iMod < 9 THEN
INSERT INTO PERSON_PERMISSION(person_id,vaccount_id, acl, permission_id, permission_type, gmt_create, gmt_modified, STATUS, granted_type)
VALUES(CONCAT(strMemberID, '_P', iMod),strMemberID,1,21, 'PACKAGE',NOW(), NOW(), 'DISABLED', 'ENTERPRISE');
ELSE
INSERT INTO PERSON_PERMISSION(person_id,vaccount_id, acl, permission_id, permission_type, gmt_create, gmt_modified, STATUS, granted_type)
VALUES(CONCAT(strMemberID, '_P', iMod),strMemberID,1,21, 'PACKAGE',NOW(), NOW(), 'DISABLED', 'PERSON');
END IF;
SET iMod=iMod+1;
END WHILE;
FETCH Cur_account INTO strMemberID;
SET iloop=iloop+1;
END WHILE;
COMMIT;
SET iloop=0;
SET iNum=iNum+1;
END WHILE;
CLOSE Cur_account;
END $$
DELIMITER ;
CALL `PERSON_PERMISSION_SUBSCRIPTION`();
DROP PROCEDURE IF EXISTS `PERSON_PERMISSION_SUBSCRIPTION` $$
CREATE PROCEDURE `PERSON_PERMISSION_SUBSCRIPTION`()
BEGIN
DECLARE iloop SMALLINT DEFAULT 0;
DECLARE iNum MEDIUMINT DEFAULT 0;
DECLARE iMod MEDIUMINT DEFAULT 0;
DECLARE strMemberID VARCHAR(40) DEFAULT 0;
DECLARE Cur_account CURSOR FOR SELECT member_id FROM account;
OPEN Cur_account;
FETCH Cur_account INTO strMemberID;
WHILE iNum<4001 DO
START TRANSACTION;
WHILE iloop<=1000 DO
SET iMod=0;
WHILE iMod < 10 DO
IF iMod < 4 THEN
INSERT INTO PERSON_PERMISSION(person_id,vaccount_id, acl, permission_id, permission_type, gmt_create, gmt_modified, STATUS, granted_type)
VALUES(CONCAT(strMemberID, '_P', iMod),strMemberID,1,21, 'PACKAGE',NOW(), NOW(), 'ENABLED', 'ENTERPRISE');
ELSEIF iMod = 4 THEN
INSERT INTO PERSON_PERMISSION(person_id,vaccount_id, acl, permission_id, permission_type, gmt_create, gmt_modified, STATUS, granted_type)
VALUES(CONCAT(strMemberID, '_P', iMod),strMemberID,1,21, 'PACKAGE',NOW(), NOW(), 'ENABLED', 'PERSON');
ELSEIF iMod < 9 THEN
INSERT INTO PERSON_PERMISSION(person_id,vaccount_id, acl, permission_id, permission_type, gmt_create, gmt_modified, STATUS, granted_type)
VALUES(CONCAT(strMemberID, '_P', iMod),strMemberID,1,21, 'PACKAGE',NOW(), NOW(), 'DISABLED', 'ENTERPRISE');
ELSE
INSERT INTO PERSON_PERMISSION(person_id,vaccount_id, acl, permission_id, permission_type, gmt_create, gmt_modified, STATUS, granted_type)
VALUES(CONCAT(strMemberID, '_P', iMod),strMemberID,1,21, 'PACKAGE',NOW(), NOW(), 'DISABLED', 'PERSON');
END IF;
SET iMod=iMod+1;
END WHILE;
FETCH Cur_account INTO strMemberID;
SET iloop=iloop+1;
END WHILE;
COMMIT;
SET iloop=0;
SET iNum=iNum+1;
END WHILE;
CLOSE Cur_account;
END $$
DELIMITER ;
CALL `PERSON_PERMISSION_SUBSCRIPTION`();
进一步要求：把person_id以‘_P0'结尾的数据person_id更新为‘*’
DELIMITER $$
DROP PROCEDURE IF EXISTS `PERSON_PERMISSION_UP` $$
CREATE PROCEDURE `PERSON_PERMISSION_UP`()
BEGIN
DECLARE iNum MEDIUMINT DEFAULT 0;
DECLARE iLoop MEDIUMINT DEFAULT 0;
WHILE iNum<40010000 DO
START TRANSACTION;
UPDATE person_permission SET person_id= '*' WHERE person_id LIKE '%_P0' AND id > iNum ANDid < iNum + 10000;
COMMIT;
SET iNum=iNum+10000;
END WHILE;
END $$
DELIMITER ;
CALL `PERSON_PERMISSION_UP`();
DROP PROCEDURE IF EXISTS `PERSON_PERMISSION_UP` $$
CREATE PROCEDURE `PERSON_PERMISSION_UP`()
BEGIN
DECLARE iNum MEDIUMINT DEFAULT 0;
DECLARE iLoop MEDIUMINT DEFAULT 0;
WHILE iNum<40010000 DO
START TRANSACTION;
UPDATE person_permission SET person_id= '*' WHERE person_id LIKE '%_P0' AND id > iNum ANDid < iNum + 10000;
COMMIT;
SET iNum=iNum+10000;
END WHILE;
END $$
DELIMITER ;
CALL `PERSON_PERMISSION_UP`();
二、为表vaccount_parameter造1200万数据：
需求：
parameter_id:1, 11, 21
Vaccount_Id:从account表中的400万中取数据
realvalue：80
assign value：null
gtm_create: now()
gmt_modified:now()
status:'ENABLED'
解决方案：
DELIMITER $$
DROP PROCEDURE IF EXISTS `VACCOUNT_PARAMETER_PROC` $$
CREATE PROCEDURE `VACCOUNT_PARAMETER_PROC`()
BEGIN
DECLARE iloop SMALLINT DEFAULT 0;
DECLARE iNum MEDIUMINT DEFAULT 0;
DECLARE strMemberID VARCHAR(40) DEFAULT 0;
DECLARE Cur_account CURSOR FOR SELECT member_id FROM account;
OPEN Cur_account;
FETCH Cur_account INTO strMemberID;
WHILE iNum<=4000 DO
START TRANSACTION;
WHILE iloop<=1000 DO
INSERT INTO vaccount_parameter(parameter_id,vaccount_id, real_value, gmt_create, gmt_modified, STATUS )
VALUES(1,strMemberID, '80',NOW(), NOW(), 'ENABLED');
INSERT INTO vaccount_parameter(parameter_id,vaccount_id, real_value, gmt_create, gmt_modified, STATUS )
VALUES(11,strMemberID, '80',NOW(), NOW(), 'ENABLED');
INSERT INTO vaccount_parameter(parameter_id,vaccount_id, real_value, gmt_create, gmt_modified, STATUS )
VALUES(21,strMemberID, '80',NOW(), NOW(), 'ENABLED');
FETCH Cur_account INTO strMemberID;
SET iloop=iloop+1;
END WHILE;
COMMIT;
SET iloop=0;
SET iNum=iNum+1;
END WHILE;
CLOSE Cur_account;
END $$
DELIMITER ;
CALL `VACCOUNT_PARAMETER_PROC`();
DROP PROCEDURE IF EXISTS `VACCOUNT_PARAMETER_PROC` $$
CREATE PROCEDURE `VACCOUNT_PARAMETER_PROC`()
BEGIN
DECLARE iloop SMALLINT DEFAULT 0;
DECLARE iNum MEDIUMINT DEFAULT 0;
DECLARE strMemberID VARCHAR(40) DEFAULT 0;
DECLARE Cur_account CURSOR FOR SELECT member_id FROM account;
OPEN Cur_account;
FETCH Cur_account INTO strMemberID;
WHILE iNum<=4000 DO
START TRANSACTION;
WHILE iloop<=1000 DO
INSERT INTO vaccount_parameter(parameter_id,vaccount_id, real_value, gmt_create, gmt_modified, STATUS )
VALUES(1,strMemberID, '80',NOW(), NOW(), 'ENABLED');
INSERT INTO vaccount_parameter(parameter_id,vaccount_id, real_value, gmt_create, gmt_modified, STATUS )
VALUES(11,strMemberID, '80',NOW(), NOW(), 'ENABLED');
INSERT INTO vaccount_parameter(parameter_id,vaccount_id, real_value, gmt_create, gmt_modified, STATUS )
VALUES(21,strMemberID, '80',NOW(), NOW(), 'ENABLED');
FETCH Cur_account INTO strMemberID;
SET iloop=iloop+1;
END WHILE;
COMMIT;
SET iloop=0;
SET iNum=iNum+1;
END WHILE;
CLOSE Cur_account;
END $$
DELIMITER ;
CALL `VACCOUNT_PARAMETER_PROC`();
三、为表Person_parameter造2400万数据：
需求：
parameter_id:1, 11, 21
person_id: 分为2种， 一种为‘*’, 另一种为从
account表中的400万中取数据
Vaccount_Id:从account表中的400万中取数据
realvalue：10
gtm_create: now()
gmt_modified:now()
status:'ENABLED'
granted_type:‘ENTERPRISE’
解决方案：
TRUNCATE
TABLE person_parameter;
DELIMITER $$
DROP PROCEDURE IF EXISTS `PERSON_PARAMETER_PROC` $$
CREATE PROCEDURE `PERSON_PARAMETER_PROC`()
BEGIN
DECLARE iloop SMALLINT DEFAULT 0;
DECLARE iNum MEDIUMINT DEFAULT 0;
DECLARE iMod MEDIUMINT DEFAULT 0;#此参数未用到，可以去掉
DECLARE strMemberID VARCHAR(40) DEFAULT 0;
DECLARE Cur_account CURSOR FOR SELECT member_id FROM account;
OPEN Cur_account;
FETCH Cur_account INTO strMemberID;
WHILE iNum<=4000 DO
START TRANSACTION;
WHILE iloop<=1000 DO
INSERT INTO person_parameter(parameter_id, person_id, vaccount_id, real_value, gmt_create, gmt_modified, STATUS, granted_type)
VALUES(1, '*',strMemberID, '10', NOW(), NOW(), 'ENABLED', 'ENTERPRISE');
INSERT INTO person_parameter(parameter_id, person_id, vaccount_id, real_value, gmt_create, gmt_modified, STATUS, granted_type)
VALUES(11, '*',strMemberID, '10', NOW(), NOW(), 'ENABLED', 'ENTERPRISE');
INSERT INTO person_parameter(parameter_id, person_id, vaccount_id, real_value, gmt_create, gmt_modified, STATUS, granted_type)
VALUES(21, '*',strMemberID, '10', NOW(), NOW(), 'ENABLED', 'ENTERPRISE');
INSERT INTO person_parameter(parameter_id, person_id, vaccount_id, real_value, gmt_create, gmt_modified, STATUS, granted_type)
VALUES(1,strMemberID,strMemberID, '10', NOW(), NOW(), 'ENABLED', 'ENTERPRISE');
INSERT INTO person_parameter(parameter_id, person_id, vaccount_id, real_value, gmt_create, gmt_modified, STATUS, granted_type)
VALUES(11,strMemberID,strMemberID, '10', NOW(), NOW(), 'ENABLED', 'ENTERPRISE');
INSERT INTO person_parameter(parameter_id, person_id, vaccount_id, real_value, gmt_create, gmt_modified, STATUS, granted_type)
VALUES(21,strMemberID,strMemberID, '10', NOW(), NOW(), 'ENABLED', 'ENTERPRISE');
FETCH Cur_account INTO strMemberID;
SET iloop=iloop+1;
END WHILE;
COMMIT;
SET iloop=0;
SET iNum=iNum+1;
END WHILE;
CLOSE Cur_account;
END $$
DELIMITER ;
CALL `PERSON_PARAMETER_PROC`();
DELIMITER $$
DROP PROCEDURE IF EXISTS `PERSON_PARAMETER_PROC` $$
CREATE PROCEDURE `PERSON_PARAMETER_PROC`()
BEGIN
DECLARE iloop SMALLINT DEFAULT 0;
DECLARE iNum MEDIUMINT DEFAULT 0;
DECLARE iMod MEDIUMINT DEFAULT 0;#此参数未用到，可以去掉
DECLARE strMemberID VARCHAR(40) DEFAULT 0;
DECLARE Cur_account CURSOR FOR SELECT member_id FROM account;
OPEN Cur_account;
FETCH Cur_account INTO strMemberID;
WHILE iNum<=4000 DO
START TRANSACTION;
WHILE iloop<=1000 DO
INSERT INTO person_parameter(parameter_id, person_id, vaccount_id, real_value, gmt_create, gmt_modified, STATUS, granted_type)
VALUES(1, '*',strMemberID, '10', NOW(), NOW(), 'ENABLED', 'ENTERPRISE');
INSERT INTO person_parameter(parameter_id, person_id, vaccount_id, real_value, gmt_create, gmt_modified, STATUS, granted_type)
VALUES(11, '*',strMemberID, '10', NOW(), NOW(), 'ENABLED', 'ENTERPRISE');
INSERT INTO person_parameter(parameter_id, person_id, vaccount_id, real_value, gmt_create, gmt_modified, STATUS, granted_type)
VALUES(21, '*',strMemberID, '10', NOW(), NOW(), 'ENABLED', 'ENTERPRISE');
INSERT INTO person_parameter(parameter_id, person_id, vaccount_id, real_value, gmt_create, gmt_modified, STATUS, granted_type)
VALUES(1,strMemberID,strMemberID, '10', NOW(), NOW(), 'ENABLED', 'ENTERPRISE');
INSERT INTO person_parameter(parameter_id, person_id, vaccount_id, real_value, gmt_create, gmt_modified, STATUS, granted_type)
VALUES(11,strMemberID,strMemberID, '10', NOW(), NOW(), 'ENABLED', 'ENTERPRISE');
INSERT INTO person_parameter(parameter_id, person_id, vaccount_id, real_value, gmt_create, gmt_modified, STATUS, granted_type)
VALUES(21,strMemberID,strMemberID, '10', NOW(), NOW(), 'ENABLED', 'ENTERPRISE');
FETCH Cur_account INTO strMemberID;
SET iloop=iloop+1;
END WHILE;
COMMIT;
SET iloop=0;
SET iNum=iNum+1;
END WHILE;
CLOSE Cur_account;
END $$
DELIMITER ;
CALL `PERSON_PARAMETER_PROC`();
本文转自 tianya23 51CTO博客，原文链接：http://blog.51cto.com/tianya23/353910，如需转载请自行联系原作者
