mysql实现流水码

java: 

/** 
    * @Description: 根据长度获取流水号
    * @Param: [length] 
    * @return: java.lang.String 
    * @Author: yusy
    * @Date: 2019/5/31 
    */ 
    @Override
    public String getSequenceNextValue(int length){
        String sequenceName = "SEQ_"+length;
        Integer no =  baseDao.getSequenceNextValue(sequenceName);
        if(no != null) {
            return String.format("%0" + length + "d", no);
        }else{
            return null;
        }
    }


@Override
    public Integer getSequenceNextValue(String sequenceName){
        String sql =  "SELECT NEXTVAL('"+sequenceName+"');";
        Query query = entityManager.createNativeQuery(sql);
        String rangeValue = "99999999999999999";
        Integer maxValue = Integer.parseInt(rangeValue.substring(0,Integer.parseInt(sequenceName.substring(sequenceName.length() -1))));
        List list = query.getResultList();
        if(list != null && list.size() > 0){
            int no = (Integer)list.get(0);
            if(no >= maxValue){
                String sqlSet = "SELECT SETVAL('"+sequenceName+"', 1);";
                query = entityManager.createNativeQuery(sqlSet);
                query.getResultList();
            }
            return no;
        }
        return null;
    }

mysql:建表，并且分别建立4、6、8位数的流水码数据条目

CREATE TABLE `sequence` (
  `name` varchar(50) NOT NULL,
  `current_value` int(11) NOT NULL,
  `increment` int(11) NOT NULL DEFAULT '1',
  PRIMARY KEY (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;



INSERT INTO `chinaff_new`.`sequence`(`name`, `current_value`, `increment`) VALUES ('SEQ_4', 0, 1);
INSERT INTO `chinaff_new`.`sequence`(`name`, `current_value`, `increment`) VALUES ('SEQ_6', 0, 1);
INSERT INTO `chinaff_new`.`sequence`(`name`, `current_value`, `increment`) VALUES ('SEQ_8', 0, 1);

 mysql:函数

CREATE DEFINER=`root`@`%` FUNCTION `currval`(seq_name VARCHAR(50)) RETURNS int(11)
    DETERMINISTIC
BEGIN
     DECLARE value INTEGER; 
     SET value = 0; 
     SELECT current_value INTO value 
          FROM sequence
          WHERE name = seq_name; 
     RETURN value; 
END


CREATE DEFINER=`root`@`%` FUNCTION `nextval`(seq_name VARCHAR(50)) RETURNS int(11)
    DETERMINISTIC
BEGIN
     UPDATE sequence
          SET current_value = current_value + increment 
          WHERE name = seq_name; 
     RETURN currval(seq_name); 
END

CREATE DEFINER=`root`@`%` FUNCTION `setval`(seq_name VARCHAR(50), value INTEGER) RETURNS int(11)
    DETERMINISTIC
BEGIN
     UPDATE sequence
          SET current_value = value 
          WHERE name = seq_name; 
     RETURN currval(seq_name); 
END