Creating sequences in MySQL an alternative to AUTO_INCREMENT

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

4 thoughts on “Creating sequences in MySQL an alternative to AUTO_INCREMENT

  1. anon

    what exactly is ‘myappname’ in your function.. other than that, your code looks very useful. thanks for uploading it here.

  2. Pingback: Make MySQL auto-increment id (re) start from 1 - PHP Solutions - Developers Q & A

  3. Larry Irwin

    I needed to import a bunch of data and keep a running sequence number for any entry for an “account” so the unique key would be (account,seq). The solution was to import all the data with seq = NULL. After the import run:
    update ignore tblname set seq=1;
    update ignore tblname set seq=2;
    update ignore tblname set seq=3;

    until none of the rows had seq = null

  4. Larry Irwin

    Also – Using a function:
    CREATE TABLE IF NOT EXISTS seqtest (
    id int(10) unsigned NOT NULL AUTO_INCREMENT,
    account int(10) unsigned NOT NULL,
    seq int(4) unsigned NOT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY dir1 (account,seq)
    ) ENGINE=MyISAM DEFAULT CHARSET=latin1;

    delimiter //
    CREATE FUNCTION f_acctseq(inacct INT) RETURNS INT
    READS SQL DATA
    BEGIN
    DECLARE outseq INT(4) DEFAULT ‘1’;
    select ifnull(max(seq),0)+1 into outseq from seqtest where account = inacct;
    RETURN outseq;
    END;
    //
    delimiter ;

    INSERT INTO seqtest VALUES (NULL,12345,f_acctseq(12345));
    INSERT INTO seqtest VALUES (NULL,12345,f_acctseq(12345));
    INSERT INTO seqtest VALUES (NULL,12345,f_acctseq(12345));
    INSERT INTO seqtest VALUES (NULL,12345,f_acctseq(12345));

    select * from seqtest;
    +—-+———+—–+
    | id | account | seq |
    +—-+———+—–+
    | 1 | 12345 | 1 |
    | 2 | 12345 | 2 |
    | 3 | 12345 | 3 |
    | 4 | 12345 | 4 |
    +—-+———+—–+
    4 rows in set (0.00 sec)

Leave a Reply

Your email address will not be published. Required fields are marked *