mysql创建序列表，根据每日日期和该序列生成单号

mysql创建序列表，根据每日日期和该序列生成单号

1.创建序列表
DROP TABLE IF EXISTS guns_sys_sequence;
CREATE TABLE guns_sys_sequence (
seq_name varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT ‘序列名’,
current_val bigint NOT NULL COMMENT ‘序列值’,
increment_val bigint NOT NULL DEFAULT 1 COMMENT ‘每次递增跨度’,
PRIMARY KEY (seq_name) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = ‘序列’ ROW_FORMAT = Dynamic;

2.创建存储过程
1）currval–当前值
CREATE FUNCTION currval(v_seq_name VARCHAR(50)) RETURNS int
READS SQL DATA
DETERMINISTIC
BEGIN
DECLARE VALUE INTEGER;
SET VALUE = 0;
SELECT current_val INTO VALUE FROM guns_sys_sequence WHERE seq_name = v_seq_name;
RETURN VALUE;
END

2）nextval–下一个值
CREATE FUNCTION nextval(v_seq_name VARCHAR(50)) RETURNS int
DETERMINISTIC
BEGIN
UPDATE guns_sys_sequence SET current_val = current_val + increment_val WHERE seq_name = v_seq_name;
RETURN currval(v_seq_name);
END

3.代码实现
1）mapper

/**
     * 获取当前值
     *
     * @param seqName 序列名称。如果序列名称不存在，sequence 表自动创建一条记录
     * @return
     */
    Long currval(@Param("seqName") String seqName);

    /**
     * 获取下一个值
     * @param seqName
     * @return
     */
    Long nextval(@Param("seqName") String seqName);

<select flushCache="true" id="currval" parameterType="string" resultType="java.lang.Long">
        SELECT currval(#{seqName})
    </select>

    <select flushCache="true" id="nextval" parameterType="string" resultType="java.lang.Long">
        SELECT nextval(#{seqName})
    </select>

2. service实现
   
   
   
   
   (controller方法这里就不写了)

大体方法就这些了。
如果想生成单号，调用generateNum方法