Example code for implementing auto-increment sequence in mysql

CREATE TABLE `sequence` (
 `name` varchar(50) COLLATE utf8_bin NOT NULL COMMENT 'Sequence name',
 `current_value` int(11) NOT NULL COMMENT 'Current value of the sequence',
 `increment` int(11) NOT NULL DEFAULT '1' COMMENT 'Sequence auto-increment',
 PRIMARY KEY (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

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 ; 

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 ; 

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 ; 

INSERT INTO sequence VALUES ('testSeq', 0, 1);-- Add a sequence name and initial value, as well as the auto-increment rateSELECT SETVAL('testSeq', 10);-- Set the initial value of the specified sequenceSELECT CURRVAL('testSeq');-- Query the current value of the specified sequenceSELECT NEXTVAL('testSeq');-- Query the next value of the specified sequence