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?

Stored procedure, or do the insert in a progamming language like Perl, Java or C#. At a minimum, you'll need to 1) select max(Project_id), 2) determine if it's the same or a different month/year, 3) get the year and month code, 4) compute the index (-001, -002, etc) and 5) insert the resulting Project_id value (along with the rest of the fields that go with this record). IMHO... — paulsm4, Feb 12 '13 at 09:02
Yes there is no in-built mechanism in MySql to allow this. Follow @paulsm4 — Minesh, Feb 12 '13 at 09:06

John Woo · Answer 1 · 2013-02-12T09:37:37.313

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'));

score 1 · Answer 2 · answered Feb 12 '13 at 09:25

1

INSERT INTO (Project_ID, col1, col2, col3)
SELECT DATE_FORMAT(NOW(), CONCAT('%y%m-', 
    ((  SELECT RIGHT(CONCAT('000', (RIGHT(Project_ID, 3) + 1)), 3) AS number
        FROM table_name
        WHERE LEFT(Project_ID, 5) = DATE_FORMAT(NOW(), '%y%m-'))
        ORDER BY Project_ID DESC
        UNION
    (   SELECT '001')
        LIMIT 1))),
'Col1 data', 'Col2 data', 'Col3 data'

This might look a bit odd, so I'll just explain the flow:

I use INSERT INTO ... SELECT so that I can check existing data from table_name to see if there are any existing cases already. The WHERE will find existing cases, and thanks to both RIGHT and LEFT it isn't too hard to carve out relevant data needed. If no rows are found, '001' is used instead, then you simply assign the existing columns as shown.

answered Feb 12 '13 at 09:25

Robin Castlin

10,956
1
28
44

Using your answer looks easier than learning a new subject, triggers. But it is beyond human comprehension. Can you parse it so, we noobs can understand. I am looking an answer for my problem YYMM#### on every increment. For instance for the date 30.11.2022, i wish increments will be like 22110001, 22110002 ... etc. And in case the threshold id (22119999) does exists, i wish it will set the id to next month: 22120001 – Tugalsan Karabacak Nov 30 '22 at 20:42
I'm gonna be honest with you; this answer is almost 10 years old and I don't remember myself what this is about. Sounds like you want to do `DATE_FORMAT(date, '%y%m0000')` and just plus incrementally with `ROW_NUMBER()` – Robin Castlin Dec 01 '22 at 10:02
I found how to fetch new id here. If I update it later on as a second step, I could able bypass this complicated stuff. https://stackoverflow.com/questions/1915166/how-to-get-the-insert-id-in-jdbc – Tugalsan Karabacak Jan 17 '23 at 05:25

Abhik Dey · Answer 3 · 2014-05-09T12:49:06.737

I have absolutely solved it,just take a look..

At first you have to take a sample table where the columns will be same to the columns of your original table except the column project_id.

then first insert a row in the original table where the value of column project_id=0 and the other columns are null,just insert the first row manually like this.

Then create a trigger on the sample table like the following...

/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

delimiter ;;

create trigger t after insert on try

for each row

begin

declare v int;

declare c int;

set v=(select max(project_id) from original);

if(v=0)then

insert into original set

project_id=concat((select concat(right(substring_index((select * from(select curdate() from try limit 1) as a),'-','1'),2),right(substring_index((select * from(select curdate() from try limit 1) as a),'-','2'),2)) from try limit 1),'-001'),

project=new.project;

else

set c=(select right((select max(project_id) from original)as x,3) from original limit 1);

insert into original set

project_id=concat((select concat(right(substring_index((select * from(select curdate() from try limit 1) as a),'-','1'),2),right(substring_index((select * from(select curdate() from try limit 1) as a),'-','2'),2)) from try limit 1),concat('-00',c+1)),

project=new.project;

delete from original limit 1;

end if;

end;;

/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

In the above trigger my sample table was try(project text) and the original table was original(project_id text,project text).

After creating a trigger like this on the sample table,start inserting rows in the sample table,the rows will automatically inserted in the original table with auto_increment values in the project_id column like..1405-001,1405-002,1405-003.... where 14 is 2014 and 05 is May and the rest are auto_incremented values which is being incremented using the trigger.

Just follow the above steps, your problem will be surely solved.

Auto Increment including Year and Month in MySql

3 Answers3

Linked