mysql 创建序列以及使用方法

DROP TABLE IF EXISTS t_sequence; 
  
-- 建t_sequence表，指定seq列为无符号大整型，可支持无符号值：0(default)到18446744073709551615（0到2^64–1）。
CREATE TABLE t_sequence (
   name       VARCHAR(50) NOT NULL, 
     current_value   BIGINT UNSIGNED NOT NULL DEFAULT 0, 
     increment     INT NOT NULL DEFAULT 1, 
     PRIMARY KEY (name)  -- 不允许重复seq的存在。
) ENGINE=InnoDB; 
  
  
DELIMITER / 
  
DROP FUNCTION IF EXISTS currval /
  
CREATE FUNCTION currval(seq_name VARCHAR(50)) 
RETURNS BIGINT
BEGIN
     DECLARE value BIGINT;
     SELECT current_value INTO value
     FROM t_sequence
     WHERE upper(name) = upper(seq_name); -- 大小写不区分.
     RETURN value;
END;
/
  
DELIMITER ; 
  
  
DELIMITER /
  
DROP FUNCTION IF EXISTS nextval /
  
CREATE FUNCTION nextval (seq_name VARCHAR(50)) 
RETURNS BIGINT
BEGIN
     DECLARE value BIGINT;
     UPDATE t_sequence
     SET current_value = current_value + increment 
     WHERE upper(name) = upper(seq_name);
     RETURN currval(seq_name); 
END;
/
  
DELIMITER ; 
  
DELIMITER /
  
DROP FUNCTION IF EXISTS setval / 
  
CREATE FUNCTION setval (seq_name VARCHAR(50), value BIGINT) 
RETURNS BIGINT
BEGIN
     UPDATE t_sequence
     SET current_value = value 
     WHERE upper(name) = upper(seq_name); 
     RETURN currval(seq_name); 
END;
/
  
DELIMITER ;

执行上述sql创建序列函数


使用方法：
1、执行：insert into t_sequence  set name='序列名称';
例如：
insert into t_sequence  set name='t_receiver_seq';

2、xml中如下使用：SELECT nextval('序列名称')
例如：
<insert id="insertReceivers" parameterType="java.util.List">
        INSERT INTO t_receiver (
        receiver_uid,
        seq   
        <if test="sendDt != null">
            , send_dt
        </if>
        )
        VALUES
        <foreach collection="list" item="item" index="index" open="(" separator="),(" close=")">
            #{item, jdbcType=VARCHAR},
            (SELECT nextval('t_receiver_seq'))
            <if test="sendDt != null">
                , #{sendDt, jdbcType=TIMESTAMP}
            </if>
        </foreach>
    </insert>