Mysql存储过程

CREATE DEFINER=`root`@`%` PROCEDURE `db`.`test_stored_procedure`()
begin
    --发生sql异常时继续执行，还有一个选项：exit
    declare continue handler for sqlexception

begin
    

--捕获异常信息，其中RETURNED_SQLSTATE是返回状态码，MESSAGE_TEXT是错误信息
get diagnostics condition 1 @p1 = RETURNED_SQLSTATE,
@p2 = MESSAGE_TEXT;
--打印状态码和错误信息
select
    @p1 as RETURNED_SQLSTATE ,
    @p2 as MESSAGE_TEXT;


--发生异常了就回滚事务
rollback;

end;
-- 上面的begin  end结束，上面是固定写法，begin end中间不要加入其它东西

--开始事务
start transaction;
--写你的sql语句
insert
    into
        db.t_sys_user(username, userInfoId,createBy,createDate, lastUpdateBy, lastUpdateDate,
        source)
    values('1xxxx', -1, 'system', now(), 'system', now(),
    'logisticMini');

insert
    into
        db.t_sys_user(username,
        source)
    values('1',
    'logisticMini');

insert
    into
        db.t_sys_user(username,
        source)
    values('1',
    'logisticMini');

-- 如果没有发生异常，就提交； 发生异常就会走上面的回滚。
commit;

end

参考：https://stackoverflow.com/questions/26338033/mysql-stored-procedure-print-error-message-and-rollback?tdsourcetag=s_pctim_aiomsg

另外一个存储过程，10进制转32进制

CREATE DEFINER=`root`@`%` FUNCTION `db`.`bigint_to_32str`(
    `ownerId` BIGINT
) RETURNS varchar(10) CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci
BEGIN
declare i int default 0;
declare digits varchar(32);
declare radix int default 32;
declare result varchar(20) default '';

if ownerId < -10000000 or ownerId > 100000000 then
--往外抛异常
 SIGNAL SQLSTATE '45000'
 SET MESSAGE_TEXT = 'ownerId too small or too big', MYSQL_ERRNO = 1001;
end if;

set digits='0123456789ABCDEFGHJKMNPQRSTVWXYZ';
set i = -(ownerId + 10000000);

while i<=-radix do
set result = concat(substring(digits, -mod(i, radix)+1, 1), result);
set i = i div radix;
end while;
set result = concat(substring(digits, -i+1, 1), result);

while length(result) <5 do 
set result = concat('0', result);
end while;

return result;
END

数据库存储过程太坑人了。