1.表结构
Field
Type
Comment
tm_sequence_id
bigint(20) NOT NULL
主键id
seq_name
varchar(30) NULL
序列名称
current_value
bigint(20) unsigned NULL
序列当前值
max_id
bigint(20) NULL
序列最大值(不填为数据类型的最大值)
cycle
smallint(6) NULL
0：默认不循环;1:循环
increment
int(10) unsigned NULL
增量(默认1)
remark
varchar(100) NULL
注释说明
2.索引
Indexes
Columns
Index Type
PRIMARY
tm_sequence_id
Unique
idx_seq_name
seq_name
Unique
3.建表语句(以下是mysql数据库执行脚本)
CREATE TABLE `tm_sequence` (
`tm_sequence_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT ‘主键id’,
`seq_name` varchar(30) DEFAULT NULL COMMENT ‘序列名称’,
`current_value` bigint(20) unsigned DEFAULT ‘1’ COMMENT ‘序列当前值’,
`max_id` bigint(20) DEFAULT NULL COMMENT ‘序列最大值(不填为数据类型的最大值)’,
`cycle` smallint(6) DEFAULT ‘0’ COMMENT ‘0：默认不循环;1:循环’,
`increment` int(10) unsigned DEFAULT ‘1’ COMMENT ‘增量(默认1)’,
`remark` varchar(100) DEFAULT NULL COMMENT ‘注释说明’,
PRIMARY KEY (`tm_sequence_id`),
UNIQUE KEY `idx_seq_name` (`seq_name`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=19 DEFAULT CHARSET=utf8 COMMENT=’序列表(实现mysql序列)’
4.获取序列号的流水数字值——2个函数FUNCTION
4.1 获取当前值
DELIMITER $$
DROP FUNCTION IF EXISTS `fn_seq_currval`$$
CREATE FUNCTION `fn_seq_currval`(P_SEQ_NAME VARCHAR (30)) RETURNS BIGINT(20)
COMMENT ‘获取序列当前值’
BEGIN
DECLARE CURRVAL BIGINT ;
SELECT
CURRENT_VALUE INTO CURRVAL
FROM
TM_SEQUENCE T
WHERE SEQ_NAME = P_SEQ_NAME ;
RETURN CURRVAL ;
END$$
DELIMITER ;
4.2 获取下一个值，当前值加1
DELIMITER $$
DROP FUNCTION IF EXISTS `fn_seq_nextval`$$
CREATE FUNCTION `fn_seq_nextval`(P_SEQ_NAME VARCHAR(30)) RETURNS BIGINT(20)
COMMENT ‘获取序列NEXT值’
BEGIN
UPDATE
TM_SEQUENCE
SET
CURRENT_VALUE = IF(
CYCLE = 0,
CURRENT_VALUE + INCREMENT,
CASE
WHEN CURRENT_VALUE + INCREMENT <= IFNULL(MAX_ID, – 1)
THEN CURRENT_VALUE + INCREMENT
ELSE 1
END
)
WHERE SEQ_NAME = P_SEQ_NAME ;
RETURN FN_SEQ_CURRVAL (P_SEQ_NAME) ;
END$$
DELIMITER ;
5.流水号拼接——字母加数字拼接定长流水号
DELIMITER $$
DROP FUNCTION IF EXISTS `fn_gen_batch_no`$$
CREATE FUNCTION `fn_gen_batch_no`(`P_HEAD_NAME` VARCHAR(20), `P_DATE` DATE) RETURNS VARCHAR(32) CHARSET utf8
COMMENT ‘获取发送批次号’
BEGIN
RETURN CONCAT(UPPER(CONCAT(RPAD(P_HEAD_NAME,4,’X’))),CONCAT(DATE_FORMAT(P_DATE,’%y%m%d’),LPAD(fn_seq_nextval(‘SEQ_PROCESS_NO’),10,0)));
END$$
DELIMITER ;