-----mysql 创建sequence
-- 创建随机数表
drop table if exists test.sequence_test_hsg;
create table test.sequence_test_hsg (
seqname VARCHAR(50) NOT NULL, -- 序列名称
currentval INT NOT NULL, -- 当前值
incrementval INT NOT NULL DEFAULT 1, -- 步长(跨度)
PRIMARY KEY (seqname)) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- 插入随机数信息
INSERT INTO test.sequence_test_hsg VALUES ('sq_ryid', '1000000000', '1');
-- 查询
SELECT * FROM test.sequence_test_hsg;
-- 创建获取当前随机数函数
-- DROP FUNCTION currval;
DELIMITER $
CREATE FUNCTION currval (seq_name VARCHAR(50))
RETURNS INTEGER
BEGIN
DECLARE current INTEGER;
SET current = 0;
SELECT currentval INTO current
FROM test.sequence_test_hsg
WHERE seqname = seq_name;
RETURN current;
END$
DELIMITER ;
-- 取下一个值函数
-- DROP FUNCTION nextval;
DELIMITER $
CREATE FUNCTION nextval (seq_name VARCHAR(50))
RETURNS INTEGER
CONTAINS SQL
BEGIN
UPDATE test.sequence_test_hsg
SET currentval = currentval + incrementval
WHERE seqname = seq_name;
RETURN currval(seq_name);
END$
DELIMITER ;
-- 查询
select currval('sq_ryid');
select nextval('sq_ryid');
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/69990899/viewspace-2742908/ ，如需转载，请注明出处，否则将追究法律责任。
