MySQL 函数使用

业务需求：实现账号的自动创建，自动创建活动下的账号，密码为6位随机密码

DELIMITER ;;
CREATE PROCEDURE insert_region_member()
BEGIN

set @memberCount=0;
set @regionId='1001';
set @allMemberCount=100;

select @memberCount:=count(a.Id) from Q_Member a join Q_region b on a.Q_RegionId=b.Id
where a.deletedOn is null and b.deletedOn is null and a.cOpenId is null and b.Id=@regionId;

select @allMemberCount:=count(a.Id) from Q_Member a join Q_region b on a.Q_RegionId=b.Id
where a.deletedOn is null and b.deletedOn is null and b.Id=@regionId;

while (@memberCount<100) do
set @allMemberCount:=@allMemberCount+1;
insert into Q_Member(Id,CNo,CPWd,CreatedOn,UpdatedOn,Q_RegionId)
select CONCAT(CNo,LPAD(@allMemberCount,3,0)),CONCAT(CNo,LPAD(@allMemberCount,3,0)),substring(MD5(RAND()),1,6),now(),now(),@regionId from Q_Region where deletedOn is null and Id=@regionId;
set @memberCount:=@memberCount+1;
end while;

commit;
END;;
CALL insert_region_member();

使用到的函数

CONCAT 拼接字符串

LPAD 前(左侧)补位填充 LPAD(3,6,0)=>000003 需要补位的值，最大位数，部位使用的字符 RPAD 后(右侧)补位

RAND() 随机数 带小数 FLOOR(RAND() * 10000) 取5位整数

MD5() MD5转换

substring() 截取字符串

now() 获取当前时间
------while 循环---------------------------------------------------------------------

while 判断条件 do

执行语句

end while

--------------------------------------------------------------------------

DELIMITER 分隔符，说明这一段是需要一起执行的，类似与多行一起执行

-------PROCEDURE 存储过程---------------------------------------------------------------------

CREATE PROCEDURE name

BEGIN

END

------------------------------------------------------------------------------

CALL 存储过程名称 执行存储过程