第一步:创建管理表序列
CREATE TABLE `sequence` (
`name` varchar(50) NOT NULL,
`current_value` int(11) NOT NULL,
`increment` int(11) NOT NULL DEFAULT '1',
PRIMARY KEY (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
第二步:创建操作的函数
currval:获取当前序列的数值
CREATE DEFINER=`root`@`%` FUNCTION `currval`(seq_name VARCHAR(50)) RETURNS int
DETERMINISTIC
BEGIN
DECLARE value INTEGER;
SET value = 0;
SELECT current_value INTO value
FROM sequence
WHERE name = seq_name;
RETURN value;
END
nextval 获取下一个序列的数值
CREATE DEFINER=`root`@`%` FUNCTION `nextval`(seq_name VARCHAR(50)) RETURNS int
DETERMINISTIC
BEGIN
UPDATE sequence
SET current_value = current_value + increment
WHERE name = seq_name;
RETURN currval(seq_name);
END
setval 设置序列的开始数值
CREATE DEFINER=`root`@`%` FUNCTION `setval`(seq_name VARCHAR(50), value INTEGER) RETURNS int
DETERMINISTIC
BEGIN
UPDATE sequence
SET current_value = value
WHERE name = seq_name;
RETURN currval(seq_name);
END
最后修改于 2021-09-18 15:11:27
如果觉得我的文章对你有用,请随意赞赏
扫一扫支付

