Custom auto increment values

The auto_increment for MySQL fields are very useful but what about if I need a custom auto-increment like a custom format like 001-000033,

001-000034
001-000035
001-000036
...

To make it possible we have an option based on past article MySQL Sequences:

  1. Create the table to store the current sequence values:
    CREATE TABLE _sequence
    (
        seq_name VARCHAR(50) NOT NULL PRIMARY KEY,
        seq_group VARCHAR(10) NOT NULL,
        seq_val INT UNSIGNED NOT NULL
    );
  2. Create a function to get and increment the current value:
    delimiter //
    DROP FUNCTION IF EXISTS getNextCustomSeq//
    CREATE FUNCTION getNextCustomSeq
    (
        sSeqName VARCHAR(50),
        sSeqGroup VARCHAR(10)
    ) RETURNS VARCHAR(20)
    BEGIN
        DECLARE nLast_val INT; 
     
        SET nLast_val =  (SELECT seq_val
                              FROM _sequence
                              WHERE seq_name = sSeqName
                                    AND seq_group = sSeqGroup);
        IF nLast_val IS NULL THEN
            SET nLast_val = 1;
            INSERT INTO _sequence (seq_name,seq_group,seq_val)
            VALUES (sSeqName,sSeqGroup,nLast_Val);
        ELSE
            SET nLast_val = nLast_val + 1;
            UPDATE _sequence SET seq_val = nLast_val
            WHERE seq_name = sSeqName AND seq_group = sSeqGroup;
        END IF; 
     
        SET @ret = (SELECT concat(sSeqGroup,'-',lpad(nLast_val,6,'0')));
        RETURN @ret;
    END// 
     
    delimiter ;
  3. Create a stored procedure to modify a current sequence value:
    delimiter //
    DROP PROCEDURE IF EXISTS sp_setSeqCustomVal//
    CREATE PROCEDURE sp_setCustomVal(sSeqName VARCHAR(50),  
                  sSeqGroup VARCHAR(10), nVal INT UNSIGNED)
    BEGIN
        IF (SELECT COUNT(*) FROM _sequence  
                WHERE seq_name = sSeqName  
                    AND seq_group = sSeqGroup) = 0 THEN
            INSERT INTO _sequence (seq_name,seq_group,seq_val)
            VALUES (sSeqName,sSeqGroup,nVal);
        ELSE
            UPDATE _sequence SET seq_val = nVal
            WHERE seq_name = sSeqName AND seq_group = sSeqGroup;
        END IF;
    END//
    delimiter ;

Testing the new functions:

  1. Create a table:
    CREATE TABLE custom_autonums
    (
       id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
       seq_1 VARCHAR(20), -- custom sequence 1
       seq_2 VARCHAR(20), -- custom sequence 2
       UNIQUE(seq_1),
       UNIQUE(seq_2)
    );
  2. Create trigger:
    delimiter //
    DROP TRIGGER IF EXISTS custom_autonums_bi//
     
    CREATE TRIGGER custom_autonums_bi BEFORE INSERT ON custom_autonums
    FOR each ROW
    BEGIN
       SET NEW.seq_1 = getNextCustomSeq("seq_1","001");
       SET NEW.seq_2 = getNextCustomSeq("seq_2","DBA");
    END//
     
    delimiter ;
  3. Insert some values:
    INSERT INTO custom_autonums (id) VALUES (NULL),(NULL),(NULL);
    SELECT * FROM custom_autonums;
    +----+------------+------------+
    | id | seq_1      | seq_2      |
    +----+------------+------------+
    |  4 | 001-000001 | DBA-000001 |
    |  5 | 001-000002 | DBA-000002 |
    |  6 | 001-000003 | DBA-000003 |
    +----+------------+------------+
    3 ROWS IN SET (0.00 sec)
  4. Altering current values:
    CALL sp_setCustomVal('seq_1','001',675);
     
    INSERT INTO custom_autonums (id) VALUES (NULL),(NULL),(NULL);
    SELECT * FROM custom_autonums;
    +----+------------+------------+
    | id | seq_1      | seq_2      |
    +----+------------+------------+
    |  4 | 001-000001 | DBA-000001 |
    |  5 | 001-000002 | DBA-000002 |
    |  6 | 001-000003 | DBA-000003 |
    |  7 | 001-000676 | DBA-000004 |
    |  8 | 001-000677 | DBA-000005 |
    |  9 | 001-000678 | DBA-000006 |
    +----+------------+------------+
    6 ROWS IN SET (0.00 sec)

Enjoy!

2 Responses to Custom auto increment values

  1. Your procedure and most of the function could be simplified to a single INSERT…ON DUPLICATE KEY UPDATE statement.

Leave a Reply

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

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre lang="" line="" escaped="" cssfile="">

Go back to top