mysql中自定义序列实现

在oracle中可以随意添加自定义序列，自定义序列可以用在一些单号的生成上，比如订单号，审批单号等。对于订单号之类的，可以用雪花算法来生成递增的序列号，但是有时候系统需要一些有意义规则的递增的单号，比如审批单号，规则为SP+日期+6位顺序号（SP20201028100012）这样的就需要序列来加持了。

但是在mysql中，没有序列这个概念，只有自增id，自增id就是加一条数据，设置为自增id的列，就会按添加的顺序，自动加1，但是这解决不了我们序列的问题。

可以用表+函数的方式来处理

新增表

DROP TABLE IF EXISTS base_sequence;
CREATE TABLE base_sequence(
    name VARCHAR(50) NOT NULL COMMENT '序列名称',
    current_value BIGINT NOT NULL COMMENT '当前值',
    increment INT NOT NULL DEFAULT 1 COMMENT '递增值',
    min_value  BIGINT NOT NULL COMMENT '最小值',
    max_value  BIGINT NOT NULL COMMENT '最大值',
    PRIMARY KEY (name)
);

新增函数

DROP FUNCTION IF EXISTS `nextval`;  
DELIMITER //  
CREATE  FUNCTION `nextval`(seq_name VARCHAR(50)) RETURNS bigint 
READS SQL DATA
BEGIN  		

    UPDATE base_sequence 
    SET current_value=last_insert_id(
    case when current_value+increment > max_value then min_value else current_value+increment end
    )
    WHERE name=seq_name;
    return last_insert_id();
 
END//  
DELIMITER ;

测试

INSERT INTO base_sequence VALUES ('seq_approve_no',100000,1,100000,999999); 

select nextval('seq_approve_no')

通过last_insert_id()的方式更新和查询最新的id，可以很好的处理并发的问题。

函数错误示例

  DECLARE lastInsertId BIGINT;
  DECLARE maxId BIGINT;
  DECLARE minId BIGINT;
  SET lastInsertId = 0;
  SET minId = 0;
  SET maxId = 0;		

  UPDATE base_sequence SET current_value=last_insert_id(current_value+increment) WHERE name=seq_name;
 
  select last_insert_id() into lastInsertId from base_sequence;
  select max_value into maxId from base_sequence where name=seq_name;
   
  if(lastInsertId>maxId) then
	  select min_value into minId from base_sequence where name=seq_name;

	  update base_sequence SET current_value = minId where name = seq_name;
        
    return minId;
   end if;

  return lastInsertId;

在解决循环序列的问题中，最开始的更新语句是查询当前最大值，与最大值比较，当有程序有并发请求时，就容易造成mysql的死锁（报错如下），所以最好在一个update中处理。

Deadlock found when trying to get lock; try restarting transaction

java测试程序

public class RunTest extends Thread{

    public static Map<String,String> testMap = new ConcurrentHashMap();

    ILoginUserService loginUserService;

    public RunTest(ILoginUserService loginUserService){
        this.loginUserService = loginUserService;
    }

    @Override
    public void run() {
        getSeq();
    }

    private void getSeq(){
        for(int i = 2; i > 0; i--) {
            int seq_approve_no = loginUserService.querySeq("seq_approve_no");
            String s = String.valueOf(seq_approve_no);
            testMap.put(s,s);
            System.out.println(this.getName()+":"+seq_approve_no);
        }
    }
}



private void testSeq(){
    RunTest rt1 = new RunTest(loginUserService);

    rt1.start();
}

@Override
public void run(String... args) throws Exception {
    for(int i = 100; i > 0; i--) {
        testSeq();
    }
}