表名 sequence
获取当前值(并根据 increment 字段的值自增一次)
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
获取自增的下一个值 (并根据 increment 字段的值自增一次)
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
设置值
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
调用函数
获取当前值 SELECT nextval('xxx')
获取下一个值 SELECT nextval('xxx') (字段'name'具体的值)
设置值 select setval('xxx',1)