MySQL AUTO INCREMENT with Prefix (YYMM) that reset every month

Question

I saw another post explaining the use of sequence table to create prefixed AUTO INCREMENT ID.

SQL Fiddle
http://sqlfiddle.com/#!2/0ed88/1

I need the generated auto increment format to be: YYMM + AUTO INCREMENT VALUE that reset every month. For example now is January, 2015. The generated id should be: 15011, 15012, 15013, etc. Next month February 2015, the generated id should be: 15021, 15022, 15023, etc. I can use the above method to generate the prefix, however how do I reset the AUTO INCREMENT value each month? Database is InnoDB. Any help will be greatly appreciated :)

MODIFIED CODE

CREATE TABLE table1_seq
(
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
)|

CREATE TABLE Table1
(
  id VARCHAR(7) NOT NULL PRIMARY KEY DEFAULT '0', name VARCHAR(30)
)|


CREATE TRIGGER tg_table1_insert
BEFORE INSERT ON table1
FOR EACH ROW
BEGIN
  INSERT INTO table1_seq VALUES (NULL);
  SET NEW.id = CONCAT(DATE_FORMAT(NOW(),'%y%m'), LAST_INSERT_ID());
END |


INSERT INTO Table1 (name) VALUES ('Jhon'), ('Mark')|

There is really no need to have special codings for primary keys. Just use auto-incremented values. If you need to enumerate things within a month, you can do it when querying. — Gordon Linoff, Jan 04 '15 at 04:20

Tikas Mamed · Answer 1 · 2015-08-06T06:14:31.890

If you have a field DATE or DATETIME in your table then this solution maybe can help you.

Let say your table name my_table and has PK id (INT) and also a field bila (DATE). 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(`bila`) = MONTH(NOW()) AND YEAR(`bila`) = YEAR(NOW()));

SET @mos = IF(
    @mos IS NULL,
    CONCAT(YEAR(NOW()),MONTH(NOW()),'001'),
    @mos + 1
    );

Then u can use @mos in your query next

score 0 · Answer 2 · answered Jan 04 '15 at 05:34

0

To reset the AUTO_INCREMENT value, drop and recreate the table1_seq table. Given your example, a TRUNCATE statement would be sufficient (given that there aren't any foreign keys, and we assume the table is using either the MyISAM or InnoDB engine).

TRUNCATE TABLE table1_seq ;

(MySQL behavior for this statement is to create a new, empty table, with the AUTO_INCREMENT set back to the value when the table was created.)

This effectively achieves the same result as a DROP table followed by CREATE table.

That answers the question you asked. As a side note, generating a key value this way usually turns out to be a bad idea.

answered Jan 04 '15 at 05:34

spencer7593

106,611
15
112
140

Thank you for your reply. However that means i have to implement the logic of checking it externally (says via php). Is there any way to implement it within MySQL? Therefore, external application just need to do the INSERT statement and the ID generation handled automatically by MySQL. – Albert Tobing Jan 04 '15 at 09:34
I mean the process of monitoring change of month need to be done externally before executing the TRUNCATE command. Is there any way so the whole process can be done by mysql internally? Therefore, external application only need to issue the INSERT command for adding record. Thanks. – Albert Tobing Jan 04 '15 at 11:11

score 0 · Answer 3 · answered Jan 10 '16 at 20:28

i've relied on the last answer above, i've used the orderDate field from my table and compared it with the current date during insertion , to decide finally if i want to reset or increment the purchaseOrderRef field: The aim is to insert custom auto increment order id (field name: "purchaseOrderRef" ) with the format DD-MM-XXX in table [Products_SumOrders]. such that it resets the XXX to 0 automatically every month:

    USE [Mydatabase] -- here you need to use your own database
    GO

    /****** Object:  Trigger [dbo].[customAutoIncrement]    Script Date: 10/1/2016 10:07:41 PM ******/

        SET ANSI_NULLS ON
        GO

        SET QUOTED_IDENTIFIER ON
        GO

         CREATE TRIGGER [dbo].[customAutoIncrement]
         ON [dbo].[Products_SumOrders]
        AFTER INSERT
        AS 
        BEGIN
            SET NOCOUNT ON;
            declare @maxOrderID int;
            declare @maxOrderRef nvarchar(max);
            declare @relativeID varchar(max);
            declare @orderId int;
            select @orderId =orderid from inserted;
            print(@orderId);--allow me to display the inserted order id
            SET @maxOrderID = (SELECT Max(orderid) FROM [Products_SumOrders] WHERE MONTH([OrderDate]) = (MONTH(GETDATE())) AND YEAR([OrderDate]) = YEAR(GETDATE()) and orderid < @orderId );
                print(@maxOrderID);--last added order
    --custom month format (always MM):
                declare @mon int;
                declare @stringMon nvarchar(10);
                set @mon= MONTH(GETDATE());
                IF @mon <10
                set @stringMon ='0' +  CONVERT(VARCHAR(1),@mon) ;
                ELSE
                set @stringMon = CONVERT(VARCHAR(2),@mon) ;
            IF @maxOrderID is null --no orders has been added in this month:
            begin
                set @maxOrderRef =  Substring(CONVERT(VARCHAR(4),YEAR(GETDATE())),3,4)+ '-'+  CONVERT(VARCHAR(2),@stringMon)+'-001';
            end
            ELSE
--custom order id format (always XXX):
            begin
            set @relativeID =(SELECT [purchaseOrderRef] FROM [Products_SumOrders] WHERE orderid=@maxOrderID);
            set @relativeID = Substring(@relativeID,LEN(@relativeID)-(Charindex('-', REVERSE(@relativeID))-2),LEN(@relativeID));
            print(CONVERT(int,@relativeID));
            IF CONVERT(int,@relativeID) < 9
                set @relativeID ='00' +  CONVERT(VARCHAR(2),@relativeID+1) ;
                ELSE
                begin
                if CONVERT(int,@relativeID) < 99
                set @relativeID ='0' +  CONVERT(VARCHAR(3),@relativeID+1) ;
                else
                set @relativeID = CONVERT(VARCHAR(3),@relativeID+1) ;
                end
            set @maxOrderRef =  Substring(CONVERT(VARCHAR(4),YEAR(GETDATE())),3,4)+ '-'+  CONVERT(VARCHAR(2),@stringMon)+'-'+ CONVERT(VARCHAR(3),@relativeID);
            end
            print(@maxOrderRef);
            UPDATE Products_SumOrders
            SET purchaseOrderRef =  @maxOrderRef
            FROM inserted  INNER JOIN [Products_SumOrders]  On inserted.orderid = [Products_SumOrders].orderid
        END 

        GO

MySQL AUTO INCREMENT with Prefix (YYMM) that reset every month

3 Answers3