MySQL how to create a custom id column formatted with character, date and number

Issue

CREATE TABLE customers (
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT...

I need the sequence to go like so:

KR-21-001
KR-21-002
KR-21-003

Explanation

KR – Is Stable Character

21 – Todays year date format (yy)

001 – Increments by +1 when a new value is added.

Next year (2022)..

the sequence needs to be reset back to 001 but the 20 changes to 21 because of the year being 2022 :

KR-22-001
KR-22-002
KR-22-003

Question

How can I create this custom ID column BEFORE INSERT?

Solution

What you are requesting is actually somewhat more complicated.

1. You can’t use auto genrated columns and for A BEFORE Trigger NEW.id is empty

As you complicate more and more you need to add a column to grab the change i year

But you could do something like this

CREATE TABLE customers (
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT
,custom_id VARCHAR(100)
,test varchar(10) 
)

CREATE TABLE customers (
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT
,Newid INT
,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
,custom_id VARCHAR(100)
,test varchar(10) 
)

CREATE TRIGGER after_customers_insert
BEFORE INSERT
ON customers FOR EACH ROW
BEGIN
    SET @created := (SELECT IFNULL(created_at, NOW()) FROM customers ORDER BY created_at DESC LIMIT 1);
    
    IF YEAR(@created) <> YEAR(NEw.created_at) THEN
       SET NEW.Newid :=  1;
    ELSE
        SET NEW.Newid := (SELECT IFNULL(MAX(Newid),0) + 1 FROM customers);
    END IF;
    SET NEW.custom_id = CONCAT('KR_',DATE_FORMAT(now(), '%y'),'_',LPAD(NEW.Newid,3,'0'));
END

INSERT INTO customers (test) VALUEs ('test')

INSERT INTO customers (test) VALUEs ('test')

UPDATE customers SET created_at = created_at - INTERVAL 1 YEAR 

INSERT INTO customers (test) VALUEs ('test')

SELECT * FROM customers

id | Newid | created_at          | custom_id | test
-: | ----: | :------------------ | :-------- | :---
 1 |     1 | 2020-07-30 22:10:30 | KR_21_001 | test
 2 |     2 | 2020-07-30 22:10:30 | KR_21_002 | test
 3 |     1 | 2021-07-30 22:10:30 | KR_21_001 | test

db<>fiddle here

Answered By – nbk

This Answer collected from stackoverflow, is licensed under cc by-sa 2.5 , cc by-sa 3.0 and cc by-sa 4.0