{"id":15,"date":"2012-02-28T00:52:15","date_gmt":"2012-02-28T05:52:15","guid":{"rendered":"http:\/\/en.latindevelopers.com\/ivancp\/?p=15"},"modified":"2012-02-28T00:51:18","modified_gmt":"2012-02-28T05:51:18","slug":"custom-auto-increment-values","status":"publish","type":"post","link":"http:\/\/en.latindevelopers.com\/ivancp\/2012\/custom-auto-increment-values\/","title":{"rendered":"Custom auto increment values"},"content":{"rendered":"

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,<\/p>\n

001-000034\r\n001-000035\r\n001-000036\r\n...<\/pre>\n
To make it possible we have an option based on past article MySQL Sequences<\/a>:<\/p>\n
    \n
  1. Create the table to store the current sequence values:\n
    create table _sequence\r\n(\r\n    seq_name varchar(50) not null primary key,\r\n    seq_group varchar(10) not null,\r\n    seq_val int unsigned not null\r\n);<\/pre>\n<\/li>\n
  2. Create a function to get and increment the current value:\n
    delimiter \/\/\r\ndrop function if exists getNextCustomSeq\/\/\r\ncreate function getNextCustomSeq\r\n(\r\n\u00a0\u00a0\u00a0 sSeqName varchar(50),\r\n\u00a0\u00a0\u00a0 sSeqGroup varchar(10)\r\n) returns varchar(20)\r\nbegin\r\n\u00a0\u00a0\u00a0 declare nLast_val int; \r\n\r\n\u00a0\u00a0\u00a0 set nLast_val =\u00a0 (select seq_val\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 from _sequence\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 where seq_name = sSeqName\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 and seq_group = sSeqGroup);\r\n\u00a0\u00a0\u00a0 if nLast_val is null then\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 set nLast_val = 1;\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 insert into _sequence (seq_name,seq_group,seq_val)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 values (sSeqName,sSeqGroup,nLast_Val);\r\n\u00a0\u00a0\u00a0 else\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 set nLast_val = nLast_val + 1;\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 update _sequence set seq_val = nLast_val\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 where seq_name = sSeqName and seq_group = sSeqGroup;\r\n\u00a0\u00a0\u00a0 end if; \r\n\r\n\u00a0\u00a0\u00a0 set @ret = (select concat(sSeqGroup,'-',lpad(nLast_val,6,'0')));\r\n\u00a0\u00a0\u00a0 return @ret;\r\nend\/\/ \r\n\r\ndelimiter ;<\/pre>\n<\/li>\n
  3. Create a stored procedure to modify a current sequence value:\n
    delimiter \/\/\r\ndrop procedure if exists sp_setSeqCustomVal\/\/\r\ncreate procedure sp_setCustomVal(sSeqName varchar(50), \u00a0\r\n              sSeqGroup varchar(10), nVal int unsigned)\r\nbegin\r\n\u00a0\u00a0\u00a0 if (select count(*) from _sequence \u00a0\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 where seq_name = sSeqName \u00a0\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 and seq_group = sSeqGroup) = 0 then\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 insert into _sequence (seq_name,seq_group,seq_val)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 values (sSeqName,sSeqGroup,nVal);\r\n\u00a0\u00a0\u00a0 else\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 update _sequence set seq_val = nVal\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 where seq_name = sSeqName and seq_group = sSeqGroup;\r\n\u00a0\u00a0\u00a0 end if;\r\nend\/\/\r\ndelimiter ;<\/pre>\n<\/li>\n<\/ol>\n
    Testing the new functions:<\/strong><\/p>\n
      \n
    1. Create a table:\n
      create table custom_autonums\r\n(\r\n\u00a0\u00a0 id int not null primary key auto_increment,\r\n\u00a0\u00a0 seq_1 varchar(20), -- custom sequence 1\r\n\u00a0\u00a0 seq_2 varchar(20), -- custom sequence 2\r\n\u00a0\u00a0 unique(seq_1),\r\n\u00a0\u00a0 unique(seq_2)\r\n);<\/pre>\n<\/li>\n
    2. Create trigger:\n
      delimiter \/\/\r\ndrop trigger if exists custom_autonums_bi\/\/\r\n\r\ncreate trigger custom_autonums_bi before insert on custom_autonums\r\nfor each row\r\nbegin\r\n\u00a0\u00a0 set new.seq_1 = getNextCustomSeq(\"seq_1\",\"001\");\r\n\u00a0\u00a0 set new.seq_2 = getNextCustomSeq(\"seq_2\",\"DBA\");\r\nend\/\/\r\n\r\ndelimiter ;<\/pre>\n<\/li>\n
    3. Insert some values:\n
      insert into custom_autonums (id) values (null),(null),(null);\r\nselect * from custom_autonums;\r\n+----+------------+------------+\r\n| id | seq_1\u00a0\u00a0\u00a0\u00a0\u00a0 | seq_2\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n+----+------------+------------+\r\n|\u00a0 4 | 001-000001 | DBA-000001 |\r\n|\u00a0 5 | 001-000002 | DBA-000002 |\r\n|\u00a0 6 | 001-000003 | DBA-000003 |\r\n+----+------------+------------+\r\n3 rows in set (0.00 sec)<\/pre>\n<\/li>\n
    4. Altering current values:\n
      call sp_setCustomVal('seq_1','001',675);\r\n\r\ninsert into custom_autonums (id) values (null),(null),(null);\r\nselect * from custom_autonums;\r\n+----+------------+------------+\r\n| id | seq_1\u00a0\u00a0\u00a0\u00a0\u00a0 | seq_2\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n+----+------------+------------+\r\n|\u00a0 4 | 001-000001 | DBA-000001 |\r\n|\u00a0 5 | 001-000002 | DBA-000002 |\r\n|\u00a0 6 | 001-000003 | DBA-000003 |\r\n|\u00a0 7 | 001-000676 | DBA-000004 |\r\n|\u00a0 8 | 001-000677 | DBA-000005 |\r\n|\u00a0 9 | 001-000678 | DBA-000006 |\r\n+----+------------+------------+\r\n6 rows in set (0.00 sec)<\/pre>\n<\/li>\n<\/ol>\n
      Enjoy!<\/p>\n
      Share this:<\/h3>