[MySql]函数-生成特定规则自增编号

一.前言

在项目中经常会有需求,要给订单或合同生成有特定规则的编号,比如自增.
这里用mysql函数来实现.

规则:固定大写字母DT +当前时间戳+五位编号[00001-99999]
生成编号:DT-20220421010101-00001
下一次获取编号:DT-20220421010101-00002

二.MySQL新建一张表sys_sequence

seq_name 序列名称
current_val 当前编号
increment_val 步长

结构sql如下:

DROP TABLE IF EXISTS sys_sequence; CREATE TABLE sys_sequence (
seq_name VARCHAR ( 50 ) CHARACTER SET utf8 COLLATE
utf8_general_ci NOT NULL COMMENT ‘序列名称’, current_val INT ( 0 ) NOT
NULL COMMENT ‘当前值’, increment_val INT ( 0 ) NOT NULL DEFAULT 1
COMMENT ‘步长’, PRIMARY KEY ( seq_name ) USING BTREE ) ENGINE =
INNODB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT =
‘系统自增序列’ ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;

表中数据如图
步长为1的意思是下次编号会比当前编号递增1

三.在MySQL新建函数

这里需要新建两个函数,一个为currval,一个为nextval

1. 在navicat中,函数部分,右键选新建函数

2. 填函数名,勾选函数,点击完成

3. 新建函数之后需要补全函数定义

currval函数完整定义为:

CREATE DEFINER=root@% FUNCTION currval(v_seq_name VARCHAR(50)) RETURNS int
BEGIN
declare value integer;
set value = 0;
select current_val into value from sys_sequence where seq_name = v_seq_name;
return value;
END

nextval函数完整定义为:

CREATE DEFINER=root@% FUNCTION nextval(v_seq_name VARCHAR(50)) RETURNS int
BEGIN
update sys_sequence set current_val = current_val + increment_val where seq_name = v_seq_name;
return currval(v_seq_name);
END

四. 创建函数时的常见错误

4.1 报错1：

报错内容:This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its de

解决方法:
在服务器用root账号登录数据库,登录语句

mysql -u root -p;

执行如下sql,就可以创建了

use testdatabase;
set global log_bin_trust_function_creators=TRUE;

---

4.2.报错2：

• The user specified as a definer (‘root‘@‘%‘) does not exist

解决方法：查询中执行如下sql,给root 权限

grant all privileges on . to ‘root’@‘%’ identified by “.”;
flush privileges;

4.3报错3:

Access denied; you need (at least one of) the SYSTEM_USER privilege(s)
for this operation

MySQL8.0.16版本中新增了一个system_user帐户类型，我们需要把权限给添加进去，此处的root可以是你当前使用的账户

解决:

grant system_user on . to ‘root’;

五.java代码

1.SequenceMapper

package com.xx.common.seq.mapper;

import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;
import org.springframework.stereotype.Repository;

@Repository
public interface SequenceMapper {

    /**
     * 新建序列
     *
     * @param name  序列名
     * @param start 起始值
     * @param incr  递增值
     */
    @Insert("INSERT INTO sys_sequence VALUES(#{name},${start},${incr})")
    public void insertSequence(@Param("name") String name, @Param("start") int start, @Param("incr") int incr);


    /**
     * 重置序列到某个值
     *
     * @param name 序列名
     * @param val  起始值
     */
    @Update("UPDATE sys_sequence SET current_val = ${val} WHERE seq_name = #{name}")
    public void resetSequence(@Param("name") String name, @Param("val") int val);

    /**
     * 获取指定序列是否存在
     *
     * @param name 序列名
     * @return 0 不存在 1 存在
     */
    @Select("SELECT COUNT(1) FROM sys_sequence WHERE seq_name = #{name}")
    public int selectSequenceExists(String name);

    /**
     * 获取指定序列下一个值
     *
     * @param name 序列名
     * @return 值
     */
    @Select("SELECT nextval(#{name})")
    public Integer nextVal(String name);


    /**
     * 获取指定序列当前值
     *
     * @param name 序列名
     * @return 值
     */
    @Select("SELECT currval(#{name})")
    public Integer currVal(String name);
}

2.ISequenceService

package com.xx.common.seq.service;

public interface ISequenceService {

    public String next(String seq);

}

3.SequenceServiceImpl

package com.xx.common.seq.service.impl;


import com.agriculture.common.seq.mapper.SequenceMapper;
import com.agriculture.common.seq.service.ISequenceService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

@Service("sequenceService")
public class SequenceServiceImpl implements ISequenceService {

    @Autowired
    private SequenceMapper sequenceMapper;

    @Override
    public String next(String seq) {
        // 如果该序列不存在，那么新增
        int exists = sequenceMapper.selectSequenceExists(seq);
        if (exists == 0) {
            sequenceMapper.insertSequence(seq, 0, 1);
        }
        // 如果超出9999, 那么重置
        Integer val = sequenceMapper.currVal(seq);
        if (val >= 9999) {
            sequenceMapper.resetSequence(seq, 0);
        }

        String result = "0000" + sequenceMapper.nextVal(seq);

        return result.substring(result.length() - 5);
    }

}

六.添加工具类

package com.agriculture.common.utils;


import com.agriculture.common.seq.service.ISequenceService;
import com.agriculture.common.utils.spring.SpringUtils;

import java.text.SimpleDateFormat;
import java.util.Date;

public abstract class GenerateSerialUtilscOPR {


    private GenerateSerialUtilscOPR() {
    }


    private static volatile ISequenceService sequenceService;


    //订单编号
    public synchronized static String index(String seq) {
        return seq.concat("-").concat(timestamp()) .concat("-"). concat(getService().next(seq.toUpperCase()));
    }

    /**
     * 获取Service
     *
     * @return service
     */
    private static ISequenceService getService() {
        if (sequenceService == null){ sequenceService = SpringUtils.getBean(ISequenceService.class);}
        return sequenceService;
    }

    /**
     * 获取时间
     *
     * @return timestamp
     */
    private static String timestamp() {
        return new SimpleDateFormat("yyyyMMdd").format(new Date());
    }
}

七.完成啦测试方法

public static void main(String[] args) {
    System.out.println(index("DT"));
}

测试结果为:DT-20220421020101-00002