基于Mysql的Sequence實(shí)現(xiàn)方法
更新時(shí)間：2017年09月08日 10:11:51 作者：阿拉丁神棍
下面小編就為大家?guī)硪黄贛ysql的Sequence實(shí)現(xiàn)方法。小編覺得挺不錯(cuò)的，現(xiàn)在就分享給大家，也給大家做個(gè)參考。一起跟隨小編過來看看吧
團(tuán)隊(duì)更換新框架。新的業(yè)務(wù)全部使用新的框架，甚至是新的數(shù)據(jù)庫--Mysql。
這邊之前一直是使用oracle，各種訂單號(hào)、流水號(hào)、批次號(hào)啥的，都是直接使用oracle的sequence提供的數(shù)字序列號(hào)?，F(xiàn)在數(shù)據(jù)庫更換成Mysql了，顯然以前的老方法不能適用了。
需要新寫一個(gè)：
•分布式場景使用
•滿足一定的并發(fā)要求
找了一些相關(guān)的資料，發(fā)現(xiàn)mysql這方面的實(shí)現(xiàn)，原理都是一條數(shù)據(jù)庫記錄，不斷update它的值。然后大部分的實(shí)現(xiàn)方案，都用到了函數(shù)。
貼一下網(wǎng)上的代碼：
基于mysql函數(shù)實(shí)現(xiàn)
表結(jié)構(gòu)
CREATE TABLE `t_sequence` ( `sequence_name` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '序列名稱' , `value` int(11) NULL DEFAULT NULL COMMENT '當(dāng)前值' , PRIMARY KEY (`sequence_name`) ) ENGINE=InnoDB DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci ROW_FORMAT=COMPACT ;
獲取下一個(gè)值
CREATE DEFINER = `root`@`localhost` FUNCTION `nextval`(sequence_name varchar(64)) RETURNS int(11) BEGIN declare current integer; set current = 0; update t_sequence t set t.value = t.value + 1 where t.sequence_name = sequence_name; select t.value into current from t_sequence t where t.sequence_name = sequence_name; return current; end;
并發(fā)場景有可能會(huì)出問題，雖然可以在業(yè)務(wù)層加鎖，但分布式場景就無法保證了，然后效率應(yīng)該也不會(huì)高。
自己實(shí)現(xiàn)一個(gè)，java版
原理：
•讀取一條記錄，緩存一個(gè)數(shù)據(jù)段，如：0-100，將記錄的當(dāng)前值從0修改為100
•數(shù)據(jù)庫樂觀鎖更新，允許重試
•讀取數(shù)據(jù)從緩存中讀取，用完再讀取數(shù)據(jù)庫
不廢話，上代碼：
基于java實(shí)現(xiàn)
表結(jié)構(gòu)
每次update，都是將SEQ_VALUE設(shè)置為SEQ_VALUE+STEP
CREATE TABLE `t_pub_sequence` ( `SEQ_NAME` varchar(128) CHARACTER SET utf8 NOT NULL COMMENT '序列名稱', `SEQ_VALUE` bigint(20) NOT NULL COMMENT '目前序列值', `MIN_VALUE` bigint(20) NOT NULL COMMENT '最小值', `MAX_VALUE` bigint(20) NOT NULL COMMENT '最大值', `STEP` bigint(20) NOT NULL COMMENT '每次取值的數(shù)量', `TM_CREATE` datetime NOT NULL COMMENT '創(chuàng)建時(shí)間', `TM_SMP` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改時(shí)間', PRIMARY KEY (`SEQ_NAME`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='流水號(hào)生成表';
sequence接口
/**
* <p></p>
* @author coderzl
* @Title MysqlSequence
* @Description 基于mysql數(shù)據(jù)庫實(shí)現(xiàn)的序列
* @date 2017/6/6 23:03
*/
public interface MysqlSequence {
/**
* <p>
* 獲取指定sequence的序列號(hào)
* </p>
* @param seqName sequence名
* @return String 序列號(hào)
*/
public String nextVal(String seqName);
}
序列區(qū)間
用于本地緩存一段序列，從min到max區(qū)間
/**
* <p></p>
*
* @author coderzl
* @Title SequenceRange
* @Description 序列區(qū)間，用于緩存序列
* @date 2017/6/6 22:58
*/
@Data
public class SequenceRange {
private final long min;
private final long max;
/** */
private final AtomicLong value;
/** 是否超限 */
private volatile boolean over = false;
/**
* 構(gòu)造.
*
* @param min
* @param max
*/
public SequenceRange(long min, long max) {
this.min = min;
this.max = max;
this.value = new AtomicLong(min);
}
/**
* <p>Gets and increment</p>
*
* @return
*/
public long getAndIncrement() {
long currentValue = value.getAndIncrement();
if (currentValue > max) {
over = true;
return -1;
}
return currentValue;
}
}
BO
對(duì)應(yīng)數(shù)據(jù)庫記錄
@Data
public class MysqlSequenceBo {
/**
* seq名
*/
private String seqName;
/**
* 當(dāng)前值
*/
private Long seqValue;
/**
* 最小值
*/
private Long minValue;
/**
* 最大值
*/
private Long maxValue;
/**
* 每次取值的數(shù)量
*/
private Long step;
/** */
private Date tmCreate;
/** */
private Date tmSmp;
public boolean validate(){
//一些簡單的校驗(yàn)。如當(dāng)前值必須在最大最小值之間。step值不能大于max與min的差
if (StringUtil.isBlank(seqName) || minValue < 0 || maxValue <= 0 || step <= 0 || minValue >= maxValue || maxValue - minValue <= step ||seqValue < minValue || seqValue > maxValue ) {
return false;
}
return true;
}
}
DAO
增刪改查，其實(shí)就用到了改和查
public interface MysqlSequenceDAO {
/**
*
*/
public int createSequence(MysqlSequenceBo bo);
public int updSequence(@Param("seqName") String seqName, @Param("oldValue") long oldValue ,@Param("newValue") long newValue);
public int delSequence(@Param("seqName") String seqName);
public MysqlSequenceBo getSequence(@Param("seqName") String seqName);
public List<MysqlSequenceBo> getAll();
}
Mapper
<?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.xxxxx.core.sequence.impl.dao.MysqlSequenceDAO" >
<resultMap id="BaseResultMap" type="com.xxxxx.core.sequence.impl.MysqlSequenceBo" >
<result column="SEQ_NAME" property="seqName" jdbcType="VARCHAR" />
<result column="SEQ_VALUE" property="seqValue" jdbcType="BIGINT" />
<result column="MIN_VALUE" property="minValue" jdbcType="BIGINT" />
<result column="MAX_VALUE" property="maxValue" jdbcType="BIGINT" />
<result column="STEP" property="step" jdbcType="BIGINT" />
<result column="TM_CREATE" property="tmCreate" jdbcType="TIMESTAMP" />
<result column="TM_SMP" property="tmSmp" jdbcType="TIMESTAMP" />
</resultMap>
<delete id="delSequence" parameterType="java.lang.String" >
delete from t_pub_sequence
where SEQ_NAME = #{seqName,jdbcType=VARCHAR}
</delete>
<insert id="createSequence" parameterType="com.xxxxx.core.sequence.impl.MysqlSequenceBo" >
insert into t_pub_sequence (SEQ_NAME,SEQ_VALUE,MIN_VALUE,MAX_VALUE,STEP,TM_CREATE)
values (#{seqName,jdbcType=VARCHAR}, #{seqValue,jdbcType=BIGINT},
#{minValue,jdbcType=BIGINT}, #{maxValue,jdbcType=BIGINT}, #{step,jdbcType=BIGINT},
now())
</insert>
<update id="updSequence" parameterType="com.xxxxx.core.sequence.impl.MysqlSequenceBo" >
update t_pub_sequence
set SEQ_VALUE = #{newValue,jdbcType=BIGINT}
where SEQ_NAME = #{seqName,jdbcType=VARCHAR} and SEQ_VALUE = #{oldValue,jdbcType=BIGINT}
</update>
<select id="getAll" resultMap="BaseResultMap" >
select SEQ_NAME, SEQ_VALUE, MIN_VALUE, MAX_VALUE, STEP
from t_pub_sequence
</select>
<select id="getSequence" resultMap="BaseResultMap" >
select SEQ_NAME, SEQ_VALUE, MIN_VALUE, MAX_VALUE, STEP
from t_pub_sequence
where SEQ_NAME = #{seqName,jdbcType=VARCHAR}
</select>
</mapper>
接口實(shí)現(xiàn)
@Repository("mysqlSequence")
public class MysqlSequenceImpl implements MysqlSequence{
@Autowired
private MysqlSequenceFactory mysqlSequenceFactory;
/**
* <p>
* 獲取指定sequence的序列號(hào)
* </p>
*
* @param seqName sequence名
* @return String 序列號(hào)
* @author coderzl
*/
@Override
public String nextVal(String seqName) {
return Objects.toString(mysqlSequenceFactory.getNextVal(seqName));
}
}
工廠
工廠只做了兩件事
•服務(wù)啟動(dòng)的時(shí)候，初始化數(shù)據(jù)庫中所有sequence【完成序列區(qū)間緩存】
•獲取sequence的下一個(gè)值
@Component
public class MysqlSequenceFactory {
private final Lock lock = new ReentrantLock();
/** */
private Map<String,MysqlSequenceHolder> holderMap = new ConcurrentHashMap<>();
@Autowired
private MysqlSequenceDAO msqlSequenceDAO;
/** 單個(gè)sequence初始化樂觀鎖更新失敗重試次數(shù) */
@Value("${seq.init.retry:5}")
private int initRetryNum;
/** 單個(gè)sequence更新序列區(qū)間樂觀鎖更新失敗重試次數(shù) */
@Value("${seq.get.retry:20}")
private int getRetryNum;
@PostConstruct
private void init(){
//初始化所有sequence
initAll();
}
/**
* <p> 加載表中所有sequence，完成初始化 </p>
* @return void
* @author coderzl
*/
private void initAll(){
try {
lock.lock();
List<MysqlSequenceBo> boList = msqlSequenceDAO.getAll();
if (boList == null) {
throw new IllegalArgumentException("The sequenceRecord is null!");
}
for (MysqlSequenceBo bo : boList) {
MysqlSequenceHolder holder = new MysqlSequenceHolder(msqlSequenceDAO, bo,initRetryNum,getRetryNum);
holder.init();
holderMap.put(bo.getSeqName(), holder);
}
}finally {
lock.unlock();
}
}
/**
* <p> </p>
* @param seqName
* @return long
* @author coderzl
*/
public long getNextVal(String seqName){
MysqlSequenceHolder holder = holderMap.get(seqName);
if (holder == null) {
try {
lock.lock();
holder = holderMap.get(seqName);
if (holder != null){
return holder.getNextVal();
}
MysqlSequenceBo bo = msqlSequenceDAO.getSequence(seqName);
holder = new MysqlSequenceHolder(msqlSequenceDAO, bo,initRetryNum,getRetryNum);
holder.init();
holderMap.put(seqName, holder);
}finally {
lock.unlock();
}
}
return holder.getNextVal();
}
}
單一sequence的Holder
•init() 初始化 其中包括參數(shù)校驗(yàn)，數(shù)據(jù)庫記錄更新，創(chuàng)建序列區(qū)間
•getNextVal() 獲取下一個(gè)值
public class MysqlSequenceHolder {
private final Lock lock = new ReentrantLock();
/** seqName */
private String seqName;
/** sequenceDao */
private MysqlSequenceDAO sequenceDAO;
private MysqlSequenceBo sequenceBo;
/** */
private SequenceRange sequenceRange;
/** 是否初始化 */
private volatile boolean isInitialize = false;
/** sequence初始化重試次數(shù) */
private int initRetryNum;
/** sequence獲取重試次數(shù) */
private int getRetryNum;
/**
* <p> 構(gòu)造方法 </p>
* @Title MysqlSequenceHolder
* @param sequenceDAO
* @param sequenceBo
* @param initRetryNum 初始化時(shí)，數(shù)據(jù)庫更新失敗后重試次數(shù)
* @param getRetryNum 獲取nextVal時(shí)，數(shù)據(jù)庫更新失敗后重試次數(shù)
* @return
* @author coderzl
*/
public MysqlSequenceHolder(MysqlSequenceDAO sequenceDAO, MysqlSequenceBo sequenceBo,int initRetryNum,int getRetryNum) {
this.sequenceDAO = sequenceDAO;
this.sequenceBo = sequenceBo;
this.initRetryNum = initRetryNum;
this.getRetryNum = getRetryNum;
if(sequenceBo != null)
this.seqName = sequenceBo.getSeqName();
}
/**
* <p> 初始化 </p>
* @Title init
* @param
* @return void
* @author coderzl
*/
public void init(){
if (isInitialize == true) {
throw new SequenceException("[" + seqName + "] the MysqlSequenceHolder has inited");
}
if (sequenceDAO == null) {
throw new SequenceException("[" + seqName + "] the sequenceDao is null");
}
if (seqName == null || seqName.trim().length() == 0) {
throw new SequenceException("[" + seqName + "] the sequenceName is null");
}
if (sequenceBo == null) {
throw new SequenceException("[" + seqName + "] the sequenceBo is null");
}
if (!sequenceBo.validate()){
throw new SequenceException("[" + seqName + "] the sequenceBo validate fail. BO:"+sequenceBo);
}
// 初始化該sequence
try {
initSequenceRecord(sequenceBo);
} catch (SequenceException e) {
throw e;
}
isInitialize = true;
}
/**
* <p> 獲取下一個(gè)序列號(hào) </p>
* @Title getNextVal
* @param
* @return long
* @author coderzl
*/
public long getNextVal(){
if(isInitialize == false){
throw new SequenceException("[" + seqName + "] the MysqlSequenceHolder not inited");
}
if(sequenceRange == null){
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;
}
/**
* <p> 初始化當(dāng)前這條記錄 </p>
* @Title initSequenceRecord
* @Description
* @param sequenceBo
* @return void
* @author coderzl
*/
private void initSequenceRecord(MysqlSequenceBo sequenceBo){
//在限定次數(shù)內(nèi)，樂觀鎖更新數(shù)據(jù)庫記錄
for(int i = 1; i < initRetryNum; i++){
//查詢bo
MysqlSequenceBo curBo = sequenceDAO.getSequence(sequenceBo.getSeqName());
if(curBo == null){
throw new SequenceException("[" + seqName + "] the current sequenceBo is null");
}
if (!curBo.validate()){
throw new SequenceException("[" + seqName + "] the current sequenceBo validate fail");
}
//改變當(dāng)前值
long newValue = curBo.getSeqValue()+curBo.getStep();
//檢查當(dāng)前值
if(!checkCurrentValue(newValue,curBo)){
newValue = resetCurrentValue(curBo);
}
int result = sequenceDAO.updSequence(sequenceBo.getSeqName(),curBo.getSeqValue(),newValue);
if(result > 0){
sequenceRange = new SequenceRange(curBo.getSeqValue(),newValue - 1);
curBo.setSeqValue(newValue);
this.sequenceBo = curBo;
return;
}else{
continue;
}
}
//限定次數(shù)內(nèi)，更新失敗，拋出異常
throw new SequenceException("[" + seqName + "] sequenceBo update error");
}
/**
* <p> 檢查新值是否合法 新的當(dāng)前值是否在最大最小值之間</p>
* @param curValue
* @param curBo
* @return boolean
* @author coderzl
*/
private boolean checkCurrentValue(long curValue,MysqlSequenceBo curBo){
if(curValue > curBo.getMinValue() && curValue <= curBo.getMaxValue()){
return true;
}
return false;
}
/**
* <p> 重置sequence當(dāng)前值 ：當(dāng)前sequence達(dá)到最大值時(shí)，重新從最小值開始 </p>
* @Title resetCurrentValue
* @param curBo
* @return long
* @author coderzl
*/
private long resetCurrentValue(MysqlSequenceBo curBo){
return curBo.getMinValue();
}
/**
* <p> 緩存區(qū)間使用完畢時(shí)，重新讀取數(shù)據(jù)庫記錄，緩存新序列段 </p>
* @Title retryRange
* @param SequenceRange
* @author coderzl
*/
private SequenceRange retryRange(){
for(int i = 1; i < getRetryNum; i++){
//查詢bo
MysqlSequenceBo curBo = sequenceDAO.getSequence(sequenceBo.getSeqName());
if(curBo == null){
throw new SequenceException("[" + seqName + "] the current sequenceBo is null");
}
if (!curBo.validate()){
throw new SequenceException("[" + seqName + "] the current sequenceBo validate fail");
}
//改變當(dāng)前值
long newValue = curBo.getSeqValue()+curBo.getStep();
//檢查當(dāng)前值
if(!checkCurrentValue(newValue,curBo)){
newValue = resetCurrentValue(curBo);
}
int result = sequenceDAO.updSequence(sequenceBo.getSeqName(),curBo.getSeqValue(),newValue);
if(result > 0){
sequenceRange = new SequenceRange(curBo.getSeqValue(),newValue - 1);
curBo.setSeqValue(newValue);
this.sequenceBo = curBo;
return sequenceRange;
}else{
continue;
}
}
throw new SequenceException("[" + seqName + "] sequenceBo update error");
}
}
總結(jié)
•當(dāng)服務(wù)重啟或異常的時(shí)候，會(huì)丟失當(dāng)前服務(wù)所緩存且未用完的序列
•分布式場景，多個(gè)服務(wù)同時(shí)初始化，或者重新獲取sequence時(shí)，樂觀鎖會(huì)保證彼此不沖突。A服務(wù)獲取0-99，B服務(wù)會(huì)獲取100-199，以此類推
•當(dāng)該sequence獲取較為頻繁時(shí)，增大step值，能提升性能。但同時(shí)服務(wù)異常時(shí)，損失的序列也較多
•修改數(shù)據(jù)庫里sequence的一些屬性值，比如step，max等，再下一次從數(shù)據(jù)庫獲取時(shí)，會(huì)啟用新的參數(shù)
•sequence只是提供了有限個(gè)序列號(hào)（最多max-min個(gè)），達(dá)到max后，會(huì)循環(huán)從頭開始。
•由于sequence會(huì)循環(huán)，所以達(dá)到max后，再獲取，就不會(huì)唯一。建議使用sequence來做業(yè)務(wù)流水號(hào)時(shí)，拼接時(shí)間。如：20170612235101+序列號(hào)
業(yè)務(wù)id拼接方法
@Service
public class JrnGeneratorService {
private static final String SEQ_NAME = "T_SEQ_TEST";
/** sequence服務(wù) */
@Autowired
private MySqlSequence mySqlSequence;
public String generateJrn() {
try {
String sequence = mySqlSequence.getNextValue(SEQ_NAME);
sequence = leftPadding(sequence,8);
Calendar calendar = Calendar.getInstance();
SimpleDateFormat sDateFormat = new SimpleDateFormat("yyyyMMddHHmmss");
String nowdate = sDateFormat.format(calendar.getTime());
nowdate.substring(4, nowdate.length());
String jrn = nowdate + sequence + RandomUtil.getFixedLengthRandom(6);//10位時(shí)間＋8位序列 + 6位隨機(jī)數(shù)＝24位流水號(hào)
return jrn;
} catch (Exception e) {
//TODO
}
}
private String leftPadding(String seq,int len){
String res ="";
String str ="";
if(seq.length()<len){
for(int i=0;i<len-seq.length();i++){
str +="0";
}
}
res =str+seq;
return res;
}
}
以上這篇基于Mysql的Sequence實(shí)現(xiàn)方法就是小編分享給大家的全部內(nèi)容了，希望能給大家一個(gè)參考，也希望大家多多支持腳本之家。
相關(guān)文章
文章介紹了MySQL 5.7.9 GA版本的更新過程和一些常見警告的解決方法,包括設(shè)置`secure-file-priv`參數(shù)、跳過SSL連接、使用`skip-networking`代替`skip-name-resolve`等,感興趣的朋友一起看看吧2025-02-02
MYSQL METADATA LOCK(MDL LOCK)MDL鎖問題分析
這篇文章主要介紹了MYSQL METADATA LOCK(MDL LOCK)MDL鎖問題分析，并通過實(shí)例給大家例句的問題處理辦法，需要的朋友參考學(xué)習(xí)下。2017-12-12
Win2003服務(wù)器安裝及設(shè)置教程 MySQL安全設(shè)置圖文教程
Win2003服務(wù)器安裝及設(shè)置教程，一般腳本之家為客戶都是將MySQL運(yùn)行于普通用戶下，這樣才會(huì)盡量避免提權(quán)，新建用戶什么的2012-05-05
MySQL的查詢緩存機(jī)制基本學(xué)習(xí)教程
這篇文章主要介紹了MySQL的查詢緩存機(jī)制基本學(xué)習(xí)教程,默認(rèn)針對(duì)InnoDB存儲(chǔ)引擎下來將,需要的朋友可以參考下2015-11-11
這篇文章主要介紹了MySQL 復(fù)制表的方法，幫助大家更好的理解和學(xué)習(xí)使用MySQL，感興趣的朋友可以了解下2021-03-03
這篇文章主要為大家詳細(xì)介紹了mysql5.7以上版本配置my.ini的詳細(xì)步驟，文中每一步介紹的非常詳細(xì)，具有一定的參考價(jià)值，感興趣的小伙伴們可以參考一下2019-10-10
這篇文章主要為大家詳細(xì)介紹了MySQL預(yù)編譯功能的相關(guān)資料，具有一定的參考價(jià)值，感興趣的小伙伴們可以參考一下2017-10-10
大家好，本篇文章主要講的是MySQL圖形化管理工具Navicat安裝步驟，感興趣的同學(xué)趕快來看看吧，對(duì)你有幫助的話記得收藏一下哦2021-12-12
mysql server is running with the --skip-grant-tables option
今天在mysql中新建數(shù)據(jù)庫提示The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement，原來是數(shù)據(jù)中配置的--skip-grant-tables，這樣安全就降低了，這個(gè)一般當(dāng)忘記root密碼的時(shí)候需要這樣操作2017-07-07
mysql+mybatis實(shí)現(xiàn)存儲(chǔ)過程+事務(wù)?+?多并發(fā)流水號(hào)獲取
這篇文章主要介紹了mysql+mybatis+存儲(chǔ)過程+事務(wù)?+?多并發(fā)流水號(hào)獲取,本文給大家介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值，需要的朋友可以參考下2021-12-12