mysql - How to customize data to be inserted? -

i have table items , while inserting it, id field(aside index id used) should generate combination of date , autoincrement value in pattern

"yy-mm-xxx" yy-last 2 digits of current year, mm-month, xxx-the autogenerated id.

apart fact better off store id in separate columns , present needed (e.g. view) words ...autoincrement value... maybe interpreted @ least in 2 ways:

1. you have auto_increment pk column id , want use values while producing compound secondary id
2. you have pk column id (whether it's auto_increment or not) want generate int values unique per combination of year , month.

---

if it's first case can solve using separate table sequencing , trigger this

table schema

create table items_seq (id int not null auto_increment primary key); create table items (id int not null default 0 primary key,                      item_id varchar(9) default '', ...); 

a trigger

delimiter $$ create trigger tg_bi_items before insert on items each row begin   insert items_seq (id) values(null);   set new.id = last_insert_id(),       new.item_id = concat(date_format(curdate(), '%y-%m-'),                            lpad(last_insert_id(), 3, '0')); end$$ delimiter ; 

now insert rows

insert items (item_id) values (null),(null); 

and you'll get

 | id |   item_id | ------------------ |  1 | 13-08-001 | |  2 | 13-08-002 | 

here sqlfiddle demo

---

if second case can trigger this

create trigger tg_bi_items before insert on items each row   set new.item_id = concat(     date_format(curdate(), '%y-%m-'),     lpad(coalesce(       (         select substring_index(max(item_id), '-', -1)           items          item_id date_format(curdate(), '%y-%') -- based on comments reset 1 every year       ), 0) + 1, 3, '0')); 

with approach have issue separate insert statements each row, otherwise end same generated number.

insert items (item_id) values (null); insert items (item_id) values (null); 

you'll

 | id |   item_id | ------------------ |  1 | 13-08-001 | |  2 | 13-08-002 | 

here sqlfiddle demo