1. 一個序列對應(yīng)一張表
每次從數(shù)據(jù)庫取的序列號大小記為n 也可以理解為步長 適當增大可以防止頻繁訪問數(shù)據(jù)庫
因為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. 建表
-- 也可以在表里面加上步長這個列 然后在函數(shù)只傳一個參數(shù)即可 原理是一樣的
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.建函數(shù)
-- 如果increment在表里面設(shè)置 則不需要參數(shù) 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. 執(zhí)行sql
int n = 1;
String seqName = "your seq name";
String sql = "SELECT fun_next_seq(seqName, n)";
...
long id = jdbc.execute(sql)
...
