* 假设有一张用户表,表名为 my_user
* 业务逻辑是需要能指定区号(如:86),指定前缀(如:137),指定密码位数或指定密码,然后密码是md5再加盐的方式存储
* 因此需要创建一个测试账号记录表 my_user_test,来记录生成的测试账号及原始密码
* 示例存储过程代码如下,仅做参考:
DROP PROCEDURE IF EXISTS `create_test_account`;
CREATE PROCEDURE `create_test_account`(IN area VARCHAR(10),
IN phone_head VARCHAR(10),
IN phone_num INT,
IN pwd VARCHAR(20),
IN pwd_rand_num INT,
IN num INT,
IN add_user INT,
IN is_new INT)
COMMENT '创建测试账号\n -- 参数说明 --\n #area:区号,如:86\n #phone_head:需要生成的手机号前缀,如 136\n #phone_num:需要生成手机号后缀的位数,如 1,2\n #pwd:预制密码,如果是空字符串则会根据pwd_rand_num参数随机生成\n #pwd_rand_num:如果pwd参数是空字符串,则会生成指定位数的密码\n #num:需要创建的账号数量\n #add_user:0表示不插入到用户表,1表示要插入到用户表\n #is_new: 是否需要删除ma_user_test_th表并重建\n'
BEGIN
-- 参数说明 --
#area:区号,如:86
#phone_head:需要生成的手机号前缀,如 136
#phone_num:需要生成手机号后缀的位数,如 1,2
#pwd:预制密码,如果是空字符串则会根据pwd_rand_num参数随机生成
#pwd_rand_num:如果pwd参数是空字符串,则会生成指定位数的密码
#num:需要创建的账号数量
#add_user:0表示不插入到用户表,1表示要插入到用户表
#is_new: 是否需要删除my_user_test表并重建
DECLARE i,needPwdRandom, tryTimes, creatNum int;
DECLARE beginPhone, endPhone BIGINT;
DECLARE sqlHead1, sqlHead2, sqlHead3, sqlcmd1, sqlcmd2, phoneRandom, loginPwd, loginPwdMd5 VARCHAR(512);
-- 定义事务判断值 --
DECLARE t_error INTEGER DEFAULT 0;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET t_error=1;
-- 此存储过程需要使用 my_user_test 表来存储临时账号信息 --
-- 需要使用以下sql语句来生成此表 --
IF is_new > 0 THEN
DROP TABLE IF EXISTS `my_user_test`;
CREATE TABLE my_user_test LIKE my_user;
ALTER TABLE my_user_test ADD COLUMN `pwd` varchar(50) NOT NULL DEFAULT '' COMMENT '原始密码',
ADD COLUMN `pwd_md5` varchar(50) NOT NULL DEFAULT '' COMMENT '原始密码的md5值',
COMMENT '测试账号临时记录表';
END IF;
-- 循环变量 --
set i = 0;
set creatNum = 0;
-- 是否需要随机密码 --
set needPwdRandom = 1;
IF LENGTH(pwd) > 0 THEN
set needPwdRandom=0;
END IF;
-- RAND() 区间为 [0,1) --
-- 因此需要用位数作为起始值,多一位的一半作为结束值 --
set beginPhone = POWER(10,phone_num-1);
set endPhone = FLOOR(POWER(10,phone_num)/2);
-- 尝试次数 --
set tryTimes = num;
-- 组装插入语句 --
set sqlHead1 = "insert into ";
set sqlHead2 = "(mobile,mobile_prefix,`password`,register_ip";
set sqlHead3 = CONCAT(") values (CONCAT('",phone_head,"',?),'",area,"',md5(CONCAT(md5(?),'vNvM1')),'127.0.0.1'");
-- 组装插入 my_user_test 表的语句 --
SET sqlcmd1 = CONCAT(sqlHead1,"my_user_test",sqlHead2,",pwd,pwd_md5",sqlHead3,",?,?);");
-- 组装插入 my_user 表的语句 --
set sqlcmd2 = CONCAT(sqlHead1,"my_user",sqlHead2,sqlHead3,");");
#select sqlcmd1,sqlcmd2,needPwdRandom;
WHILE i<num DO
-- 随机手机号 --
set phoneRandom = FLOOR(beginPhone+RAND()*endPhone);
IF needPwdRandom > 0 THEN
-- 需要随机密码 --
set loginPwd = SUBSTR(md5(RAND()),1,pwd_rand_num);
ELSE
-- 使用指定的密码 --
set loginPwd = pwd;
END IF;
-- 记录密码的md5值 --
set loginPwdMd5 = md5(loginPwd);
#select phoneRandom,loginPwd,loginPwd;
-- 必须使用全局变量来进行sql的预执行配置 --
set @sqlExe = sqlcmd1;
set @phoneRandomExe = phoneRandom;
set @loginPwdExe = loginPwd;
set @loginPwdMd5Exe = loginPwdMd5;
-- 使用事务处理,可能出现随机到重复号码的问题,方便回滚重试 --
START TRANSACTION;
PREPARE stmt1 FROM @sqlExe;
EXECUTE stmt1 using @phoneRandomExe,@loginPwdExe,@loginPwdExe,@loginPwdMd5Exe;
DEALLOCATE PREPARE stmt1;
-- 如果需要插入到用户表,则还需要处理 --
IF add_user > 0 THEN
set @sqlExe = sqlcmd2;
PREPARE stmt2 FROM @sqlExe;
EXECUTE stmt2 using @phoneRandomExe,@loginPwdExe;
DEALLOCATE PREPARE stmt2;
END IF;
IF t_error = 1 THEN
ROLLBACK;
-- 重试,重试次数递减,操作次数不变 --
SELECT "have repeate account,try again", tryTimes;
set tryTimes = tryTimes-1;
ELSE
COMMIT;
set i = i+1;
END IF;
set t_error = 0;
-- 尝试次数满了,则不再处理 --
IF tryTimes <= 0 THEN
set creatNum = i;
-- 为了跳出循环 --
set i = num;
END IF;
END WHILE;
-- 显示处理的数量 --
IF tryTimes > 0 THEN
set creatNum = i;
END IF;
SELECT "process num:",creatNum;
END;
