存储过程脚本

stored-procedure.sql

## 所有的用户表：
update jmf_sys_user set invite_code = createInviteCode(id) where invite_code is null;
## 函数脚本:用完后删除即可
CREATE FUNCTION createInviteCode(num BIGINT(20)) RETURNS varchar(50) 
BEGIN
DECLARE remainder BIGINT (20);
DECLARE inviteCode VARCHAR (50);
DECLARE chars VARCHAR (62);
DECLARE str VARCHAR (2);
DECLARE scale BIGINT (20);
DECLARE temp BIGINT;
SET remainder = 0;
SET inviteCode = '';
SET chars = "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789";
SET str = '';
SET scale = 62;
SET temp = 0;
WHILE num > scale-1 DO
SET remainder = MOD (num, scale);
SET temp = remainder + 1;
SET str = substr(chars, temp, 1);
SET inviteCode = CONCAT(inviteCode, str);
SET num = floor(CAST(num / scale AS DECIMAL(24,4)));
END
WHILE;
SET temp = num + 1;
SET str = substr(chars, temp, 1);
SET inviteCode = CONCAT(inviteCode, str);
SET inviteCode = REVERSE(inviteCode);
SET inviteCode = lpad(inviteCode, 11, 'A');
RETURN inviteCode;
END