Fatal Exception

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!