-- Sequence management table
Drop table if exists sequence;
Create table sequence (
Name VARCHAR (50) not null,
Current_value int not null,
Increment int not null default 1,
Primary key (name)
) ENGINE = InnoDB;
-- Function for retrieving the current value
Drop function if exists currval;
DELIMITER $
Create function currval (seq_name VARCHAR (50 ))
RETURNS INTEGER
LANGUAGE SQL
DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT''
BEGIN
DECLARE value INTEGER;
SET value = 0;
SELECT current_value INTO value
FROM sequence
WHERE name = seq_name;
RETURN value;
END
$
DELIMITER;
-- Function for getting the next value
Drop function if exists nextval;
DELIMITER $
Create function nextval (seq_name VARCHAR (50 ))
RETURNS INTEGER
LANGUAGE SQL
DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT''
BEGIN
UPDATE sequence
SET current_value = current_value + increment
WHERE name = seq_name;
RETURN currval (seq_name );
END
$
DELIMITER;
-- Function for updating the current value
Drop function if exists setval;
DELIMITER $
Create function setval (seq_name VARCHAR (50), value INTEGER)
RETURNS INTEGER
LANGUAGE SQL
DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT''
BEGIN
UPDATE sequence
SET current_value = value
WHERE name = seq_name;
RETURN currval (seq_name );
END
$
DELIMITER;
/*
-- Test
Insert into sequence VALUES ('testseq ', 0, 1 );
Select setval ('testseq ', 10 );
Select currval ('testseq ');
Select nextval ('testseq ');
*/
Author's blog in json format