mysql自定义sequence_单机版 mysql 序列 sequence 生成策略

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)

...