Hussain Fakhruddin, India Weblog

MySQL doesn’t doesn’t have sequences and Auto_Increment does not suffice the need for a text based sequence generator. Or say you want to reset AUTO_INCREMENT’s value?

So here’s a workaround for a generating a sequence in MySQL which can be reset later.

Step 1: Create a Table

CREATE TABLE IF NOT EXISTS `seqgen` (
`seqno` int(4) unsigned NOT NULL,
`application_id` varchar(20) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Step 2: Add your application in the sequence
INSERT INTO `seqgen` (`seqno`, `application_id`) VALUES (0, ‘myappname’);
Now when inserting I have set the seqno = 0 , because I want my sequence to start with 0;

Step 3:  You can create a Stored Proc or a Function to get the value and increment it by 1
<Remember to add this Proc/Function through command line with delimiter, else it will not work>

delimiter //
CREATE PROCEDURE seq_gen( OUT nextval INT , IN applicationid text)
BEGIN
select seqno into nextval from seqgen where application_id = applicationid;
update seqgen SET seqno = seqno + 1 where application_id = applicationid;
commit;
END;
//

—OR Function —- If you use function note that you’ll have to commit it by your program, MySQL doesn’t allow implicit or explicit commit in a Stored Function. So better use Procedure..

delimiter //
CREATE FUNCTION f_seq_gen(applicationid text)
RETURNS INT
BEGIN
DECLARE
nextval INT;
select seqno into nextval from seqgen where application_id = applicationid;
update seqgen SET seqno = seqno + 1 where application_id = applicationid;
RETURN nextva;
END;
//

Step 4: Access your sequence by

select f_seq_gen(‘myappname”);

Now you can use many such applications as Sequences in MySQL