在mysql中实现sequence nextval

1、定义一个表sequence

#字段seq_name表示序列名,

#字段current_val 表示对应序列的当前值,

#字段increment_val表示对应序列的每次增加的值,

CREATE TABLE `sequence` (`seq_name` VARCHAR(50) NOT NULL,`current_val` INTEGER NOT NULL,`increment_val` INTEGER NOT NULL DEFAULT 1,PRIMARY KEY USING BTREE (`seq_name`))

2、定义一个函数next_val

入参sequence_name,序列名

CREATE DEFINER = 'root'@'localhost' FUNCTION `next_val`(        `sequence_name` VARCHAR(64)    )    RETURNS INTEGER    DETERMINISTIC    CONTAINS SQL    SQL SECURITY DEFINER    COMMENT ''BEGINUPDATE sequence t, (SELECT @current_val := current_val, @increment_val := increment_val FROM sequence t2 WHERE t2.seq_name=sequence_name) t3SET t.current_val = t.current_val + t.increment_val WHERE t.seq_name=sequence_name AND @current_val=t.current_val;RETURN @current_val + @increment_val;END;


3、举例获取序列A00001的下个值

首先在表sequence里增加初始化数据如:

seq_name: A00001

current_val: 1

increment_val: 1

命令行调用函数:


jdbc调用函数代码段:

    String sql = "{? = call next_val(?)}";    Connection conn = getDataSource().getConnection();;    CallableStatement pstat = conn.prepareCall(sql);    pstat.registerOutParameter(1, Types.BIGINT);    pstat.setString(2, "A00001");    pstat.execute();    long nextVal = pstat.getLong(1);
发表评论
留言与评论(共有 0 条评论) “”
   
验证码:

相关文章

推荐文章