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);