Formatted Auto Increment Start from 1 Every Month

Question

I have table that its PK format is like this

YYMMXXXXXX

where YY is year in two digits, MM is month in two digits and XXXXXX is six digit auto increment number that start from 1 if the month change.

Can MySQL do this automatically?

Thanks before.

score 0 · Accepted Answer · edited May 23 '17 at 12:24

I searching solution that using triggers but it is use a temporary table.

https://stackoverflow.com/a/17894239/1525199

But then I find my own solution:

let say my table name my_table and has PK id (INT) and also a field time (datetime). I just need to get last (biggest) id in current month from my_table then add by 1.

SET @mos= (SELECT MAX(id) FROM my_table WHERE MONTH(`time`) = MONTH(NOW()) AND YEAR(`time`) = YEAR(NOW()));

SELECT IF(
    @mos IS NULL,
    CONCAT(YEAR(NOW()),MONTH(NOW()),'000001'),
    @mos + 1
    );

Formatted Auto Increment Start from 1 Every Month

1 Answers1