mysql数据库sequence序列表使用记录

最新推荐文章于 2024-05-20 18:36:43 发布

德鲁大叔撸代码

最新推荐文章于 2024-05-20 18:36:43 发布

阅读量1.5k

点赞数

分类专栏：数据库文章标签： mysql java

本文链接：https://blog.csdn.net/m0_43584016/article/details/106664679

版权

数据库专栏收录该内容

6 篇文章 0 订阅

订阅专栏

一、需求背景

如何确保一个序列号是按自己定义的规则依次递增？
实际项目需求如下：在商户表有商户费率模板号这个字段，我们要求该字段的规则是：ST+当前日期YYYYMMDD+6位序列号(左边不够是用0填补) 如：TS20200610000001和TS20200610000002
备注：当前字段非自增主键，单我们又要求该字段的值是按照我们约束的规则依次增加！！！！
在一个项目中，这种字段可能会很多，那怎么去很好的解决这种问题呢？

二、如何解决

通常，对于这种需求，我们都会用一个专门的序列表(sequence)，表结构如下：

CREATE TABLE `sequence`  (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '记录流水号',
  `seq_name` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '序列名称',
  `current_value` bigint(20) NOT NULL DEFAULT 0 COMMENT '当前值',
  `increment_value` int(10) UNSIGNED NOT NULL DEFAULT 1 COMMENT '递增值',
  `minimum` bigint(20) NOT NULL DEFAULT 1 COMMENT '最小值ֵ',
  `maximum` bigint(20) NOT NULL DEFAULT 9223372036854775807 COMMENT '最大值ֵ',
  `cycle_flg` char(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '循环使用标记Y:是N:否',
  `create_time` datetime(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0) COMMENT '创建时间',
  `update_time` datetime(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0) ON UPDATE CURRENT_TIMESTAMP(0) COMMENT '修改时间',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE INDEX `uq_seqname`(`seq_name`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 71 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = 'star序列生成表' ROW_FORMAT = Dynamic;

备注：
seq_name序列名称通常是字段名称，如商户费率模板号、服务商号等对应的seq_name分别是TEMP_ID_SEQ和BAGENT_ID
increment_value 字段代表每次的递增值，通常为1
current_value 字段代表当前值如当前只是1，当经过一次递增之后，当前值变成2，下次生成的值就是3
通过sequence这张表，我们会统一的管理需要按我们自定义要求递增的一个唯一序列

三、项目中如何使用？

1、公共常量类CommonConstants中定义不够长度依次用0补全、定义目标序列号前缀

    public static final char SEQUENCE_PREFIX = '0';

    public static final String TEMP_ID_SEQ_PREFIX = "ST";

2、实体类、mybatis接口文件和数据库映射文件

@Data
public class SequenceDTO {

    private Long seqId;

    private String seqName;

    private Long currentValue;

    private Long incrementValue;

    private Long minimum;

    private Long maximum;

    private String cycleFlg;

    private Date createTime;

    private Date updTime;
}

@Mapper
public interface SequenceRepository {
    /**
     * 功能描述: 获取指定的序列行，并进行for update的行锁操作
     */
    SequenceDTO getSequenceByIdForUpdate(@Param("seqName") String seqName);

    /**
     * 功能描述: 更新指定的Sequence值
     */
    int updateSequenceById(SequenceDTO sequenceDTO);

    /**
     * 功能描述: 获取全部序列行
     */
    List<SequenceDTO> getAllSequence();

    /**
     * 功能描述: 从mysql获取uuid
     */
    String getUuidFromMysql();
}

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.mtx.kyrieboot.sequence.SequenceRepository">
  <resultMap id="BaseResultMap" type="com.mtx.kyrieboot.sequence.SequenceDTO">
    <id column="id" jdbcType="BIGINT" property="seqId" />
    <result column="seq_name" jdbcType="VARCHAR" property="seqName" />
    <result column="current_value" jdbcType="BIGINT" property="currentValue" />
    <result column="increment_value" jdbcType="BIGINT" property="incrementValue" />
    <result column="minimum" jdbcType="BIGINT" property="minimum" />
    <result column="maximum" jdbcType="BIGINT" property="maximum" />
    <result column="cycle_flg" jdbcType="CHAR" property="cycleFlg" />
    <result column="create_time" jdbcType="TIMESTAMP" property="createTime" />
    <result column="update_time" jdbcType="TIMESTAMP" property="updTime" />
  </resultMap>
  <select id="getSequenceByIdForUpdate" parameterType="java.lang.String" resultMap="BaseResultMap">
    select * from sequence where seq_name = #{seqName,jdbcType=VARCHAR} for update
  </select>

  <select id="getAllSequence" resultMap="BaseResultMap">
    select * from sequence
  </select>

  <update id="updateSequenceById" parameterType="com.mtx.kyrieboot.sequence.SequenceDTO">
    update sequence
    <set>
      <if test="currentValue != null">
        current_value = #{currentValue,jdbcType=BIGINT},
      </if>
      <if test="updTime != null">
        update_time = #{updTime,jdbcType=TIMESTAMP},
      </if>
    </set>
    where seq_name = #{seqName,jdbcType=VARCHAR}
  </update>

  <select id="getUuidFromMysql" resultType="java.lang.String">
    select uuid() as uuid
  </select>

</mapper>

3、序列号工厂类

@Service
@Slf4j
public class MySqlSequenceFactory {

    private final Lock lock = new ReentrantLock();
    private int getRetryNum = 5;
    private Map<String, MySqlSequenceHolder> holderMap = new ConcurrentHashMap<>();
    private int initRetryNum = 5;
    @Autowired
    private SequenceRepository sequenceRepository;
    @Autowired
    private SequenceService sequenceService;

    public long getNextVal(String seqName) {
        log.info("开始获取{}序列值", seqName);
        MySqlSequenceHolder holder = holderMap.get(seqName);
        if (holder == null) {
            log.warn("获取{}序列值出现异常，指定sequenceHolder在map中不存在，开始重新获取操作", seqName);
            try {
                lock.lock();
                holder = holderMap.get(seqName);
                if (holder != null) {
                    return holder.getNextVal();
                }
                SequenceRange sequenceRange = new SequenceRange(-1, -1);
                holder = new MySqlSequenceHolder(seqName, sequenceRepository, sequenceService, sequenceRange, initRetryNum, getRetryNum);
                holder.init();
                holderMap.put(seqName, holder);
            } finally {
                lock.unlock();
            }
        }
        long finalLongNum = holder.getNextVal();
        log.info("获取{}序列值完成，获取的值为{}", seqName, finalLongNum);
        return finalLongNum;

    }

    private void init() {
        log.info("开始初始化所有sequence序列");
        initAll();
        log.info("初始化sequence序列完成");
    }

    private void initAll() {
        try {
            lock.lock();
            List<SequenceDTO> sequenceDTOList = sequenceRepository.getAllSequence();
            if (sequenceDTOList == null || sequenceDTOList.size() == 0) {
                throw new SequenceException("The sequenceDTOList is null!");
            }
            for (SequenceDTO SequenceDTO : sequenceDTOList) {
                SequenceRange sequenceRange = new SequenceRange(-1, -1);
                MySqlSequenceHolder holder = new MySqlSequenceHolder(SequenceDTO.getSeqName(), sequenceRepository, sequenceService, sequenceRange, initRetryNum, getRetryNum);
                holder.init();
                holderMap.put(SequenceDTO.getSeqName(), holder);
            }
        } finally {
            lock.unlock();
        }
    }
}

4、MySQL序列生成器

@Slf4j
public class MySqlSequenceHolder {
    private final Lock lock = new ReentrantLock();
    private String seqName;
    private SequenceRepository sequenceRepository;
    private SequenceService sequenceService;
    private SequenceRange sequenceRange;
    private volatile boolean isInitialize = false;
    private int initRetryNum;
    private int getRetryNum;

    public MySqlSequenceHolder(String seqName, SequenceRepository SequenceRepository, SequenceService sequenceService, SequenceRange sequenceRange, int initRetryNum, int getRetryNum) {
        this.seqName = seqName;
        this.sequenceRepository = SequenceRepository;
        this.sequenceService = sequenceService;
        this.sequenceRange = sequenceRange;
        this.initRetryNum = initRetryNum;
        this.getRetryNum = getRetryNum;
    }

    public void init() {
        log.info("开始初始化{}序列", seqName);
        if (isInitialize == true) {
            log.error("初始化{}序列失败，序列已经初始化", seqName);
            throw new SequenceException("[" + seqName + "] the MySqlSequenceHolder has inited");
        }
        if (sequenceRepository == null) {
            log.error("初始化{}序列失败，SequenceRepository对象为空", seqName);
            throw new SequenceException("[" + seqName + "] the SequenceRepository has inited");
        }
        if (sequenceService == null) {
            log.error("初始化{}序列失败，sequenceService", seqName);
            throw new SequenceException("[" + seqName + "] the sequenceService has inited");
        }
        if (StringUtils.isBlank(seqName)) {
            log.error("初始化序列失败，seqName没有置入");
            throw new SequenceException("[" + seqName + "] the seqName has inited");
        }
        initSequenceRecord();
        isInitialize = true;
        log.info("初始化{}序列完成", seqName);
    }


    public long getNextVal() {
        if (isInitialize == false) {
            log.error("获取下一个序列值失败，此序列还未初始化");
            throw new SequenceException("[" + seqName + "] the MySqlSequenceHolder not inited");
        }
        if (sequenceRange == null) {
            log.error("获取下一个序列值失败，此序列区间对象还未初始化");
            throw new SequenceException("[" + seqName + "] the sequenceRange is null");
        }
        long curValue = sequenceRange.getAndIncrement();
        if (curValue == -1) {
            try {
                lock.lock();
                curValue = sequenceRange.getAndIncrement();
                if (curValue != -1) {
                    return curValue;
                }
                sequenceRange = retryRange();
                curValue = sequenceRange.getAndIncrement();
            } finally {
                lock.unlock();
            }
        }
        return curValue;
    }

    public void initSequenceRecord() {
        for (int i = 0; i < initRetryNum; i++) {
            SequenceRange sequenceRangeTmp = sequenceService.getNewSequenceRange(seqName);
            if (sequenceRangeTmp == null) {
                continue;
            } else {
                sequenceRange = sequenceRangeTmp;
                return;
            }
        }
    }

    private SequenceRange retryRange() {
        for (int i = 0; i < getRetryNum; i++) {
            SequenceRange sequenceRangeTmp = sequenceService.getNewSequenceRange(seqName);
            if (sequenceRangeTmp == null) {
                log.error("初始化{}序列异常，无法获取指定的数据", seqName);
                continue;
            } else {
                return sequenceRangeTmp;
            }
        }
        throw new SequenceException("[" + seqName + "] CardSequence update error");
    }
}

5、序列区间，用于缓存序列

public class SequenceRange {

    /**
     * 最小值
     */
    private final long min;
    /**
     * 最大值
     */
    private final long max;
    /**
     * 当前值
     */
    private final AtomicLong value;
    /**
     * 是否超限
     */
    private volatile boolean over = false;

    public SequenceRange(long min, long max) {
        this.min = min;
        this.max = max;
        this.value = new AtomicLong(min);
    }

    public long getAndIncrement() {
        long currentValue = value.getAndIncrement();
        if (min == -1 || max == -1 || currentValue > max) {
            over = true;
            return -1;
        }
        return currentValue;
    }
}

6、异常

public class SequenceException extends RuntimeException {

    private static final long serialVersionUID = -6896586285280037814L;

    public SequenceException() {
        super();
    }

    public SequenceException(String s) {
        super(s);
    }

    public SequenceException(String message, Throwable cause) {
        super(message, cause);
    }

    public SequenceException(Throwable cause) {
        super(cause);
    }
}

7、序列号服务类

@Service
@Slf4j
public class SequenceService {

    @Autowired
    private SequenceRepository sequenceRepository;
    private static final long overLong = 8223372036854775807L;
    @Transactional(propagation = Propagation.REQUIRES_NEW, rollbackFor = Exception.class)
    public SequenceRange getNewSequenceRange(String seqName) {
        log.info("开始获取序列{}的数据", seqName);
        SequenceRange sequenceRange;
        SequenceDTO sequenceDTO = sequenceRepository.getSequenceByIdForUpdate(seqName);
        if (sequenceDTO == null) {
            return null;
        } else {
            if ("N".equals(sequenceDTO.getCycleFlg()) && sequenceDTO.getCurrentValue().equals(sequenceDTO.getMaximum())) {
                log.error("获取{}的数据失败，序列已经使用完，请速度重新设计解决方案", seqName);
                return null;
            } else {
                long newMinValue = sequenceDTO.getCurrentValue();
                long newMaxValue = sequenceDTO.getCurrentValue() + sequenceDTO.getIncrementValue();
                if (newMaxValue > sequenceDTO.getMaximum()) {
                    newMaxValue = sequenceDTO.getMaximum();
                    /*新的起始值*/
                    if ("Y".equals(sequenceDTO.getCycleFlg())) {
                        sequenceDTO.setCurrentValue(1L);
                    } else {
                        sequenceDTO.setCurrentValue(sequenceDTO.getMaximum());
                    }
                } else {
                    /*新的起始值*/
                    sequenceDTO.setCurrentValue(newMaxValue);
                }
                /*最后更新时间*/
                if (sequenceDTO.getCurrentValue() > overLong) {
                    log.error("{}序列已经只剩10^19次方个，请及时分配新的，请速度重新设计解决方案", seqName);
                }
                sequenceDTO.setUpdTime(new Date());
                int result = sequenceRepository.updateSequenceById(sequenceDTO);
                if (result > 0) {
                    log.info("获取序列{}的数据成功，新的序列范围为[{},{}]", seqName, newMinValue, newMaxValue - 1);
                    sequenceRange = new SequenceRange(newMinValue, newMaxValue - 1);
                    return sequenceRange;
                } else {
                    log.error("获取{}的数据失败，数据库中没有查询到此数据", seqName);
                    return null;
                }
            }
        }
    }

}

8、生成各种Sequence序列

@Service
@Slf4j
public class SequenceClient {

    @Autowired
    MySqlSequenceFactory mySqlSequenceFactory;

    /**
     * 功能描述: 获取新的序列号
     */
    public String getSeqNo(String seqName,String prefix,int len){
        log.info("准备获取新的{}序列号",seqName);
        Long initNum = mySqlSequenceFactory.getNextVal(seqName);
        StringBuffer stringBuffer = new StringBuffer(prefix);
        stringBuffer.append(StringUtil.leftPad(String.valueOf(initNum), len, CommonConstants.SEQUENCE_PREFIX));
        String newCashTokenNo = stringBuffer.toString();
        log.info("获取新的{}序列号成功，编号为{}", seqName, newCashTokenNo);
        return newCashTokenNo;

    }
    /**
     * 功能描述: 获取新的序列号
     */
    public String getSeqNoEnum(SequenceEnum seqName, String prefix) {
        log.info("准备获取新的{}序列号", seqName.getCode());
        Long initNum = mySqlSequenceFactory.getNextVal(seqName.getCode());
        StringBuffer stringBuffer = new StringBuffer(prefix);
        stringBuffer.append(StringUtil.leftPad(String.valueOf(initNum), seqName.getLen(), CommonConstants.SEQUENCE_PREFIX));
        String newCashTokenNo = stringBuffer.toString();
        log.info("获取新的{}序列号成功，编号为{}", seqName.getCode(), newCashTokenNo);
        return newCashTokenNo;
    }
}

四、单元测试

@Test
    public void 唯一序列验证(){
        //商户费率模板号要求  TS+当前日期YYYYMMDD+6位序列号  如TS20200610000001
        String prefix = CommonConstants.TEMP_ID_SEQ_PREFIX+DateUtil.getCurrentDate();
        String tempId = sequenceClient.getSeqNo(KeyConstants.TEMP_ID_SEQ,prefix,6);
        System.err.println("运行结果："+tempId);
    }

运行结果：
在这里插入图片描述
生成序列号后，该序列号的当前值：

德鲁大叔撸代码

关注

0
点赞
踩
3

收藏

觉得还不错? 一键收藏
0
评论
mysql数据库sequence序列表使用记录

一、需求背景如何确保一个序列号是按自己定义的规则依次递增？实际项目需求如下：在商户表有商户费率模板号这个字段，我们要求该字段的规则是：ST+当前日期YYYYMMDD+6位序列号(左边不够是用0填补) 如：TS20200610000001和TS20200610000002备注：当前字段非自增主键，单我们又要求该字段的值是按照我们约束的规则依次增加！！！！在一个项目中，这种字段可能会很多，那怎么去很好的解决这种问题呢？二、如何解决通常，对于这种需求，我们都会用一个专门的序列表(sequence)，
复制链接

扫一扫