/*参数:插入记录的起始ID,客户ID,记录数,初始用户名*/
DELIMITER $$
CREATE PROCEDURE batchInsert(IN init INT , IN cid INT , IN loops INT , IN username VARCHAR(64))
BEGIN
DECLARE myid INT;
DECLARE myname VARCHAR(64);
DECLARE num INT;
SET myid = init;
SET myname = username;
SET num = 1;
WHILE num <= loops DO
SET myname = CONCAT(username , CONVERT(myid , CHAR));
/*往用户表插入一个用户*/
INSERT INTO user (id , cid , NAME , realname , photo , experience , register_date , modify_date)
VALUES (myid , cid , myname , myname , 'www.baidu.com' , 1200.00 , NOW() , NOW());
/*往经验表中插入该用户的经验记录*/
INSERT INTO user_experience (cid , NAME , qid , aid , exp_reg , tags , create_date , modify_date , exp_status)
VALUES (cid , myname , 10000 , 10000 , 3.14 , 'ab,cd,ee' , NOW() , NOW() , 2);
SET num = num+1;
SET myid = myid+1;
END WHILE;
END $$
CALL batchInsert(1,1,5000,'aaa');
CALL batchInsert(5001,3,5000,'bbb');
DELIMITER $$
CREATE PROCEDURE batchInsert(IN init INT , IN cid INT , IN loops INT , IN username VARCHAR(64))
BEGIN
DECLARE myid INT;
DECLARE myname VARCHAR(64);
DECLARE num INT;
SET myid = init;
SET myname = username;
SET num = 1;
WHILE num <= loops DO
SET myname = CONCAT(username , CONVERT(myid , CHAR));
/*往用户表插入一个用户*/
INSERT INTO user (id , cid , NAME , realname , photo , experience , register_date , modify_date)
VALUES (myid , cid , myname , myname , 'www.baidu.com' , 1200.00 , NOW() , NOW());
/*往经验表中插入该用户的经验记录*/
INSERT INTO user_experience (cid , NAME , qid , aid , exp_reg , tags , create_date , modify_date , exp_status)
VALUES (cid , myname , 10000 , 10000 , 3.14 , 'ab,cd,ee' , NOW() , NOW() , 2);
SET num = num+1;
SET myid = myid+1;
END WHILE;
END $$
CALL batchInsert(1,1,5000,'aaa');
CALL batchInsert(5001,3,5000,'bbb');