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 INTO t_receiver (
receiver_uid,
seq
, send_dt
)
VALUES
#{item, jdbcType=VARCHAR},
(SELECT nextval('t_receiver_seq'))
, #{sendDt, jdbcType=TIMESTAMP}