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