MySQL do not have sequence, for people coming from Oracle this sounds a little bit odd but as I will show you the solution is so simple that you will forgive MySQL very soon.

First step create a table for storing the values. You can do it a local db level or also at mysql. db is up to you.

My choise is to do at single db level.

CREATE TABLE sequence (id INT NOT NULL, name varchar(10), PRIMARY KEY (name), INDEX (id));

then we will implement the four methods normally used for handling sequences:

• createSeq(<sequence_name>);

• getSeqVa(<sequence_name>);

• getSeqNextVal(<sequence_name>);

• deleteSeq(<sequence_name>);

For each method we will create a function:

Methods definition:

DELIMITER $$

DROP FUNCTION IF EXISTS `hss`.`createSeq`$$

CREATE FUNCTION `createSeq`(in_seq CHAR(10)) RETURNS int(11)

    READS SQL DATA

    BEGIN

        DECLARE seqVal INT;

        INSERT INTO sequence (id,name) VALUES (0,in_seq);

        RETURN(0);

    END$$

DELIMITER ;

**************************************************************************************
DELIMITER $$
DROP FUNCTION IF EXISTS `hss`.`getSeqNextVal`$$
CREATE FUNCTION `getSeqNextVal`(in_seq CHAR(10)) RETURNS int(11)
    READS SQL DATA
    BEGIN
        DECLARE seqVal INT;
        select id INTO seqVal from sequence where name = in_seq for update;
        UPDATE sequence SET id=LAST_INSERT_ID(id+1) where name = in_seq;
        Select id INTO seqVal from sequence where name = in_seq;
    
        RETURN(seqVal);
    END$$

DELIMITER ; 

*********************************************************************************************

 

DELIMITER $$

DROP FUNCTION IF EXISTS `hss`.`getSeqVal`$$
CREATE FUNCTION `getSeqVal`(in_seq CHAR(10)) RETURNS int(11)
    READS SQL DATA
    BEGIN
        DECLARE seqVal INT;
    
        Select id INTO seqVal from sequence where name = in_seq;
    
        RETURN(seqVal);
    END$$

DELIMITER ;

DELIMITER $$

************************************************************************************
CREATE FUNCTION `deleteSeq`(in_seq CHAR(10)) RETURNS int(11)
    READS SQL DATA
    BEGIN
        DECLARE seqVal INT;
    
        DELETE from sequence where name=in_seq;
        RETURN(0);
    END$$

DELIMITER ;

How to use them:

START TRANSACTION; select createSeq('a'); COMMIT;
select getSeqVal('a');
START TRANSACTION; select getSeqNextVal('a'); COMMIT;
START TRANSACTION; select deleteSeq('a'); COMMIT;

Easy as drinking a glass of beer ;-) .....

See u for next tip....