1.建表
CREATE TABLE `auto_id` (
`id` int(11) NOT NULL,
`num` int(11) NOT NULL DEFAULT '10000000' COMMENT '编码',
`type` tinyint(1) DEFAULT '0' COMMENT '编码类型：1资产',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
2.创建自增长函数
CREATE DEFINER=`数据库用户名`@`%` FUNCTION `AUTOID`(cType TINYINT UNSIGNED) RETURNS int(11)
BEGIN
SET @tempnum = 10000000;
SET @tempId = 1;
SELECT (`num` + 1) , id INTO @tempnum,@tempId FROM auto_id WHERE `type` = cType LIMIT 1;
UPDATE auto_id SET `num` = @tempnum WHERE id = @tempId;
RETURN @tempnum;
END
3.调用函数
select AUTOID(1) //1代表编码类型
