mysql 创建序列表 和 存储过程 用于生成编号

-- 1,创建序列表
DROP TABLE IF EXISTS `tb_st_asj_sequence`;
CREATE TABLE `tb_st_asj_sequence`  (
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '序列名',
  `current_value` bigint(12) NOT NULL COMMENT '序列当前值',
  `increment` int(12) NOT NULL DEFAULT 1,
  PRIMARY KEY (`name`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

-- 2.插入数据 初始化序列
INSERT INTO tb_st_asj_sequence  VALUES ('SEQ_TRZ_MEMBER_NO',100000000000,1);

-- 3 重置 序列
UPDATE tb_st_asj_sequence SET current_value='111111111111' WHERE name='SEQ_TRZ_MEMBER_NO';

-- 4 创建函数
DROP FUNCTION IF EXISTS tb_st_asj_seq;  
DELIMITER $$  
CREATE FUNCTION tb_st_asj_seq(seq_name char (20)) returns BIGINT
BEGIN
 UPDATE tb_st_asj_sequence SET current_value=last_insert_id(current_value+increment) WHERE name=seq_name;
 RETURN last_insert_id();
END $$
DELIMITER;

--  5 测试函数
SELECT tb_st_asj_seq('SEQ_TRZ_MEMBER_NO')

--  6 创建存储过程 用于生成 主键
CREATE PROCEDURE `tbstasj_create_id`(
  IN unit_code varchar(50),
  OUT serial    VARCHAR(64))
BEGIN
DECLARE currentDate VARCHAR (15);
DECLARE xl VARCHAR(12);

SELECT DATE_FORMAT(NOW(), '%Y%m') INTO currentDate;

SELECT tb_st_asj_seq('SEQ_TRZ_MEMBER_NO') INTO xl;
 
SELECT CONCAT(unit_code,currentDate,xl) into serial; 
end;

-- 7 测试存储过程
call tbstasj_create_id('0000001',@upwd);
select @upwd ;