php - Generate a unique string with integer as postfix for column in MySQL -

i want generate unique incremental string value 1 column in mysql table. format of key {string}-{int}, example, foo-1, foo-2, , on.

below table structure:

create table if not exists `items` (   `id` bigint(20) not null auto_increment,   `item_key` varchar(100) not null,   `title` tinytext not null,   `created` datetime not null,   `modified` datetime not null,   primary key (`id`),   unique key `item_key` (`item_key`) ) engine=innodb  default charset=utf8 auto_increment=1; 

the requirement here is, when new record inserted, it's value of 'item_key' should calculated as:

step-1: find last record

step-1.1

if there is, value of 'item_key' , increment it's integer part , set new record

step-1.2

else set foo-1 item_key

there 2 options achieve per knowledge: 1. mysql triggers 2. using php-mysql manually query last record , set new value in insert query

i prefer first option - mysql triggers - before insert. have created trigger this.

delimiter $$  create trigger generate_item_key before insert     on items each row  begin      declare itemkey varchar(10);     declare lastitemkey varchar(100) default "";     declare lastitemnum varchar(20) default "";      set itemkey = "foo";      -- find last item     set lastitemkey = (select item_key items item_key != "" order created desc limit 1);      if (lastitemkey <> '')         set lastitemnum = convert(replace(lastitemkey, concat(itemkey, "-"), ""), unsigned);     else         set lastitemnum = 0;     end if;      -- set new issue key     set new.item_key = concat(itemkey, "-", lastitemnum + 1);  end; $$  delimiter ; 

this works fine single record insert query. example,

insert `items` (`id`, `item_key`, `title`, `created`, `modified`) values (null, 'key', 'first item', '2015-07-14 00:00:00', '2015-07-14 00:00:00'); 

it generates foo-1 item_key column.

but, question is, not working multiple insert query. reason there unique key applied on column, , trigger generates same key records. example,

insert `items` (`id`, `item_key`, `title`, `created`, `modified`) values (null, 'key', 'first item', '2015-07-14 00:00:00', '2015-07-14 00:00:00'),(null, 'key2', 'second item', '2015-07-14 00:00:00', '2015-07-14 00:00:00'),(null, 'key3', 'third item', '2015-07-14 00:00:00', '2015-07-14 00:00:00'),(null, 'key', 'first item', '2015-07-14 00:00:00', '2015-07-14 00:00:00'); 

can please suggest here? can best , fast solution achieve this?

if has better solution other above two, please suggest also.

thank you

if gap between item key number being generated not cared, can make use of newly generated primary key value part of item_key name.

example:

delimiter $$  create trigger bi_items  --  generate_item_key         before insert on items         each row begin      declare itemkey varchar(10);     declare _key_id int default 0;      set itemkey = "foo";      -- find new id being assigned pk column     select auto_increment _key_id       information_schema.tables      table_name = 'items'        , table_schema = database();      -- set new issue key     set new.item_key = concat( itemkey, "-", _key_id );  end; $$  delimiter ; 

note: auto increment field values not rolled on errors. , hence on next insertion requests after on insert error, generated auto incremented , discarded values not used.