Auto Increment including Year and Month in MySql

Question

I have a table called > Project with an auto increment field for project estimate bid number called Project_ID.

This field is auto incremented. I have created it as an 8 digit character field which carries the field rule.

I need it to auto increment as a two number year, two number month, include a hyphen, and then a number starting at 001 for first record in that time period.

An example for the month of April 2012 would be 1204-001 for the first record, 1204-002 for the 2nd and etc. then when the month of May rolls around the Project_ID would change to 1205-001.

What I’ve been trying to write is as follows, I kept it as a simple default expression with a default value of

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

How I have Achieve this?

Answer 1

Basically, you can use BEFORE INSERT TRIGGER on the table you want the column to be incremented.

Here are some steps to create a simple algorithm and put this code inside the trigger:

// get current YEAR
SET @cur_Year = CONCAT(DATE_FORMAT(CURDATE(), '%Y'));
// get current MONTH
SET @cur_MONTH = CONCAT(DATE_FORMAT(CURDATE(), '%m'));
// concatenate YEAR and MONTH
SET @Year_Month = CONCAT(@cur_Year, @cur_MONTH);
// get the last value for the current YEAR and MONTH
SET @max_ID = ( SELECT MAX(ID) 
                FROM    tableName 
                WHERE   ID LIKE CONCAT(@Year_Month, '-%'));
// get the last three characters from the id, convert in to
// integer and increment by 1
SET @last_ID = CAST(RIGHT(@max_ID, 3) AS SIGNED) + 1;
// pad zero on the left using LPAD and 
// concatenate it with YEAR and MONTH
SET @new_ID =   CONCAT(@Year_Month,'-',LPAD(CAST(@last_ID AS CHAR(3)), 3, '0'));