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!

8 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.

  2. How do you get over this error in step 2:

    “This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)”

    • Hello Joe,

      Try with
      CREATE FUNCTION getNextCustomSeq
      (
      sSeqName VARCHAR(50),
      sSeqGroup VARCHAR(10)
      ) RETURNS VARCHAR(20)
      NOT DETERMINISTIC
      MODIFIES SQL DATA

      BEGIN
      ...

      And the same for the other functions and stored procedures.

  3. Takip ediyorum teşekkür ederim

  4. Thank you for post… excellent guidelines as step by step… Great !

  5. Hello, can i ask if i want to add alphabets after DBA-000001 like DBA-000001-A. If DBA-0000001-A reached to DBA-999999-A, it will auto increment to B like DBA-0000000-B. Can you teach me how to do that? You can email your answer to me. My email is sen.player2@hotmail.com

Leave a Reply

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

Go back to top