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

一、需求背景

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

二、如何解决

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

1
2
3
4
5
6
7
8
9
10
11
12
13
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补全、定义目标序列号前缀

1
2
3
public static final char SEQUENCE_PREFIX = '0';

public static final String TEMP_ID_SEQ_PREFIX = "ST";

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
@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;
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
@Mapper
public interface SequenceRepository {
/**
* 功能描述: 获取指定的序列行,并进行for update的行锁操作
*/
SequenceDTO getSequenceByIdForUpdate(@Param("seqName") String seqName);

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

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

/**
* 功能描述: 从mysql获取uuid
*/
String getUuidFromMysql();
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
<?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、序列号工厂类

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
@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序列生成器

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
@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、序列区间,用于缓存序列

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
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、异常

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
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、序列号服务类

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
@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序列

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
@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;
}
}

四、单元测试

1
2
3
4
5
6
7
@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);
}

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

坚持原创技术分享,您的支持将鼓励我继续创作!