java - Auto Increment including Year and Month in MySql -

java - Auto Increment including Year and Month in MySql -

i have table called > project auto increment field project estimate bid number called project_id.

this field auto incremented. have created 8 digit character field carries field rule.

i need auto increment 2 number year, 2 number month, include hyphen, , number starting @ 001 first record in time period.

an illustration month of apr 2012 1204-001 first record, 1204-002 2nd , etc. when month of may rolls around project_id alter 1205-001.

what i’ve been trying write follows, kept simple default look default value of

cyear(date()) + (month()) + “-“ + “001” .

how have accomplish this?

basically, can utilize before insert trigger on table want column incremented.

here steps create simple algorithm , set code within trigger:

// current year set @cur_year = concat(date_format(curdate(), '%y')); // current month set @cur_month = concat(date_format(curdate(), '%m')); // concatenate year , month set @year_month = concat(@cur_year, @cur_month); // lastly value current year , month set @max_id = ( select max(id) tablename id concat(@year_month, '-%')); // lastly 3 characters id, convert in // integer , increment 1 set @last_id = cast(right(@max_id, 3) signed) + 1; // pad 0 on left using lpad , // concatenate year , month set @new_id = concat(@year_month,'-',lpad(cast(@last_id char(3)), 3, '0'));

java mysql