mysql auto increment from 00001 - 99999 and reset the value every year

Question

My first aim is to generate customer reference code automaticaly everytime when I insert a new customer

so when it shown in my nodejs it should be : "MS2200001"

So my idea is set id from customer table (mysql) with auto increment and zerofill (int) length = 5

So I can get id 00001 and insert to another column named as "customer reference" with

("MS" + (2022)+ "00001")

And I am trying to reset the counter to 00001 again if become 2023,2024,2025 etc.

How can I archive this in phpmyadmin or I should chnage my idea?

Answer 1

Use trigger-generating technique and additional MyISAM table with secondary AUTO_INCREMENT column in PRIMARY KEY.

An example:

-- base table for complete identifier generation
CREATE TABLE base_for_complete_id (
    `year` YEAR, 
    id INT AUTO_INCREMENT, 
    PRIMARY KEY (`year`, id)
) ENGINE = MyISAM;

-- create trigger which will generate complete identifier
CREATE TRIGGER generate_complete_id
BEFORE INSERT ON maintable
FOR EACH ROW
BEGIN
    DECLARE tmp INT;
    -- insert row into base table
    INSERT INTO base_for_complete_id (`year`) VALUES (YEAR(NEW.created_at));
    -- store id generated for current year
    SET tmp = LAST_INSERT_ID();
    -- save generated complete identifier into main table
    SET NEW.complete_id = CONCAT('prefix_', YEAR(NEW.created_at), '_', tmp);
    -- clear excess rows from base table
    DELETE FROM base_for_complete_id WHERE `year` = YEAR(NEW.created_at) AND id < tmp;
END

DEMO fiddle

If you need to format id part of generated value with leading zeros then use LPAD() function, for example SET tmp = LPAD(LAST_INSERT_ID(), 5, 0);.

Caution! If the value for generated number exceeds 99999 then it will be truncated, and only 5 leading digits will be stored.