How to generate unique grouped autoincrement document id's? [duplicate]

Question

I need to make some tables which are able to auto-increment unique document names for each month of the year based on the date of each documnet type. I think it will be a good idea to make it as a generated stored column in a MySql table. Thailand uses the Buddhist calendar where you normally add 543 years to the Christian calendar (2024 becomes 2567) in this case I add 43 years as I only need the last digits of the year. I am then missing the last serial number to number which I cannot find a solution to after intense searching on the internet. I would think it should be found under grouping. In this case, I am working with the invoices.

My table:

CREATE TABLE `tbl_0_invoices` (
  `invoice_date` date NOT NULL,
  `invoice_no` varchar(45) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci GENERATED ALWAYS AS (concat(_utf8mb4'INV',(date_format(`invoice_date`,_utf8mb4'%y') + 43),date_format(`invoice_date`,_utf8mb4'%m'))) STORED;
  
 ALTER TABLE `tbl_0_invoices`
  ADD PRIMARY KEY (`invoice_no`);

I have not been able to find any documentation on the issue, even this kind of document identification is quite normal in Thailand.

I expect this output.

invoice_date	invoice_no
21/8/2567	INV6708-1
13/9/2567	INV6709-1
13/9/2567	INV6709-2
13/9/2567	INV6709-3
13/9/2567	INV6709-4
18/9/2567	INV6709-5
25/10/2567	INV6710-1
25/10/2567	INV6710-2
25/10/2567	INV6710-3
25/10/2567	INV6710-4
24/1/2568	INV6801-1

"I need to make some tables which are able to auto-increment unique document names for each month of the year based on the date of each documnet type" You're fighting against the typical auto increment behavior that would give you a serially increasing value. Does the increment really need to reset every month? Could you live with INV-6708-1, INV-6708-2, INV-6709-3 where the serial incement is not reset every month? — erik258
– erik258, Commented Aug 14, 2024 at 16:36
MyISAM allowed you to create a multi-column primary key with grouped auto increment of the second column. This feature is not available in InnoDB. You may have to do what you want using an INSERT trigger. — Barmar
– Barmar, Commented Aug 14, 2024 at 17:12
You can't use an INSERT trigger either, because auto-increment values are not generated in a BEFORE trigger, but you can't modify columns in an AFTER trigger. Nor can you make a generated column expression that references an auto-increment value. — Bill Karwin
– Bill Karwin, Commented Aug 14, 2024 at 18:15
Beware, in a heavily parallel environment this requirement will cause bottlenecks. — Shadow
– Shadow, Commented Aug 14, 2024 at 22:28
@eric258 I cannot use the serial increment if it's not reset every month. A demand from the accounting company doing our reporting. — Lasse Staalung
– Lasse Staalung, Commented Aug 16, 2024 at 8:20

ArtBindu · Accepted Answer · 2024-08-14 18:42:54Z

1

Use DATE_FORMAT(date, "%y%m") to get the numeric year & month in format: YYMM.

Use row_number() OVER (PARTITION BY date) to get the increment value w.r.t YYMM value.

So, this is simple string joining query.
and MySQL query should be:

select 
    DATE_FORMAT(cast(invoice_date as date), '%d/%m/%Y') as invoice_date,
    CONCAT("INV", 
        DATE_FORMAT(cast(invoice_date as date), "%y%m"),
        "-",
        row_number() OVER (PARTITION BY DATE_FORMAT(cast(invoice_date as date), "%y%m"))
    ) as invoice_no
from mySQLTable;

Check Result: db<>fiddle

Result as follows:

edited Aug 14, 2024 at 18:42

answered Aug 14, 2024 at 18:37

ArtBindu

2,0381 gold badge12 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Lasse Staalung Over a year ago

Thanks, it works fine. I have one comment and one question: 1. you can't string the date because 29th february will give a problem every 4th year, thats why 43 is added to the year only, which I did. 2. I am not sure how I implement this into my table, but i can always make an invoice id, and join the table with a view, is that the idea?

ArtBindu Over a year ago

I don't understanding what you exactly want... Your question is resolved according to description and this is a new issue, then create a separate ticket with proper dbfiddle data table @LasseStaalung

Collectives™ on Stack Overflow

How to generate unique grouped autoincrement document id's? [duplicate]

1 Answer 1

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Linked

Related