1. 一个序列对应一张表

每次从数据库取的序列号大小记为n 也可以理解为步长 适当增大可以防止频繁访问数据库

因为mysql的AUTO_INCREMENT步长是全局的，所以，不建议更改步长

CREATE TABLE `SEQ_DEMO` (
  `id` bigint(10) unsigned NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT SEQ_DEMO VALUES(0)

@Test
public void test() throws Exception {
    int n = 1; 
    Connection con = dataSourceWrite.getConnection();
    con.setAutoCommit(false);
    String sql = "update SEQ_DEMO set id = last_insert_id(id + n) ";
    Statement stmt = con.createStatement();
    stmt.executeUpdate(sql);

    String nextSql = "select last_insert_id()";
    ResultSet rs = stmt.executeQuery(nextSql);
    while (rs.next()) {
        Long id = rs.getLong(1);
        System.err.println("now id=" + id);
    }

    con.commit();
    con.close();
}

2.多个序列放在一张表上

1. 建表
-- 也可以在表里面加上步长这个列 然后在函数只传一个参数即可 原理是一样的
CREATE TABLE `REB_SEQ` 
    `seq_name` varchar(50) NOT NULL COMMENT '序列名',
    `curr_value` bigint(20) DEFAULT '0' COMMENT '当前值',
    -- `increment` int(10) DEFAULT '1' COMMENT '步长 默认1',
    PRIMARY KEY (`seq_name`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8

2.建函数
-- 如果increment在表里面设置 则不需要参数 param_cache_size 
DROP FUNCTION IF EXISTS `fun_next_seq`$$
CREATE DEFINER=`root`@`%` FUNCTION `fun_next_seq`(param_seq_name VARCHAR(50),param_cache_size INT(10)) RETURNS INT(11)
       DETERMINISTIC
  BEGIN  
    DECLARE ret BIGINT(20); 
    UPDATE REB_SEQ SET curr_value = curr_value + param_cache_size WHERE seq_name = param_seq_name;  
    SET ret = 0;
    SELECT curr_value INTO ret FROM REB_SEQ WHERE seq_name = param_seq_name; 
    RETURN ret; 
  END$$
  DELIMITER ;

3. 执行sql
int n = 1;
String seqName = "your seq name";
String sql = "SELECT fun_next_seq(seqName, n)";
...
long id = jdbc.execute(sql)
...