MySQL序列表，自增序列生成合同编号

项目中使用mysql数据库，需要使用自增序列号生成合同编号，编号按年份每年更新，且根据合同模板不同需使用多个自增序列。为了考虑拓展性，也方便其他业务使用序列，使用了序列表进行集中管理，使用函数操作序列。

解决思路

• currval(name)函数获取当前序列值。
• nextval(name)函数获取下一个序列值，即先自增value更新后再调用currval(name)。
• nextvalForYear(name)函数适用于按年份每年更新重置value值的场景。
• setval(name,value)函数根据传入的参数手动重置value。
• 附： 合同编号的字符串拼接函数
  • CONCAT(str1,str2,…)：字符串拼接函数
  • LPAD(str,len,padstr)：字符串左填充函数
  • RPAD(str,len,padstr)：字符串右填充函数

例： 拼接格式 - 2020 xxxx第C0001号

//使用了左填充函数LPAD - 0001
select concat(YEAR(NOW()), ' ', #{contractTplName}, '第C', LPAD(nextval(#{seqName}),4,0), '号')

---

1. 创建Sequence管理表 seq_manage

DROP TABLE IF EXISTS seq_manage; 
CREATE TABLE seq_manage ( 
	seq_name VARCHAR(50) NOT NULL, 
	current_value INT NOT NULL, 
	increment INT NOT NULL DEFAULT 1, 
	year INT(4),
	PRIMARY KEY (seq_name) 
) ENGINE=InnoDB;

2. 取当前值 currval(name)

DROP FUNCTION IF EXISTS currval; 
DELIMITER $ 
CREATE FUNCTION currval (name VARCHAR(50)) 
RETURNS INTEGER
LANGUAGE SQL 
DETERMINISTIC 
CONTAINS SQL 
SQL SECURITY DEFINER 
COMMENT ''
BEGIN
	DECLARE value INTEGER; 
	SET value = 0; 
	SELECT current_value INTO value 
	FROM seq_manage
	WHERE seq_name = name; 
	RETURN value; 
END
$ 
DELIMITER ;

3. 取下一个值 nextval(name)

DROP FUNCTION IF EXISTS nextval; 
DELIMITER $ 
CREATE FUNCTION nextval (name VARCHAR(50)) 
RETURNS INTEGER
LANGUAGE SQL 
DETERMINISTIC 
CONTAINS SQL 
SQL SECURITY DEFINER 
COMMENT ''
BEGIN
	UPDATE seq_manage
	SET current_value = current_value + increment 
	WHERE seq_name = name; 
	RETURN currval(name); 
END
$ 
DELIMITER ;

4. 取下一个值 nextvalForYear(name)（按年份每年自动重置value)

DROP FUNCTION IF EXISTS nextvalForYear;
DELIMITER $
CREATE FUNCTION nextvalForYear (name VARCHAR(50))
RETURNS INTEGER
LANGUAGE SQL
DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
    set @year = (select year from seq_manage WHERE seq_name = name);

	IF YEAR(NOW()) > @year THEN
		UPDATE seq_manage
		SET current_value = 1, year = YEAR(NOW())
		WHERE seq_name = name;
		RETURN currval(name);
	ELSE
		UPDATE seq_manage
		SET current_value = current_value + increment
		WHERE seq_name = name;
		RETURN currval(name);

	END IF;
END
$
DELIMITER ;

---

5. 更新当前值 setval(name,value)

DROP FUNCTION IF EXISTS setval; 
DELIMITER $ 
CREATE FUNCTION setval (name VARCHAR(50), value INTEGER) 
RETURNS INTEGER
LANGUAGE SQL 
DETERMINISTIC 
CONTAINS SQL 
SQL SECURITY DEFINER 
COMMENT ''
BEGIN
	UPDATE seq_manage
	SET current_value = value 
	WHERE seq_name = name; 
	RETURN currval(name); 
END
$ 
DELIMITER ;

---

测试相关函数：

INSERT INTO seq_manage VALUES ('test', 0, 1);
--添加一个sequence，设置名称为'test',初始值为0，以及每次的自增值为1。
--如果需要按年份更新，则还需设置year字段初始值，获取序列时则调用nextvalForYear(name)函数即可

SELECT SETVAL('test', 10);
---设置'test'序列的初始值，这里设置test的初始值为10

SELECT CURRVAL('test');  
--查询'test'序列的当前值

SELECT NEXTVAL('test');  
--查询'test'序列的下一个值，不会每年重置value值

SELECT nextvalForYear('test');  
--查询'test'序列的下一个值，每年会更新重置value值
--可以考虑只使用nextvalForYear(name)函数，不用再创建NEXTVAL(name)函数。因为当序列不需要每年更新时，只需此序列的year字段为null即可。

---