Creating Sequential and Padded Invoice IDs with SQL

Padded Invoice IDs

In one of my recent client projects there is a requirement for creating invoice_id as it should be readable from the user and easily shareable. It should contain some prefixes. Also, they choose the database for this is Mysql.

Example:
inv00001
inv00002
inv00003

I solved this from the application level. But I was curious and I needed more convincing about this solution.

So I researched Is there any way to solve this by SQL.

Approaches I tried

• Virtual column ❌
• Event trigger ❌
• View table ✅

The table structure

Dummy orders table

Virtual column ❌

I tried to make a virtual column like this.
Note:

ALTER TABLE orders
ADD COLUMN invoice_id VARCHAR(255) AS (CONCAT('INV', LPAD(id, 4, '0'))) VIRTUAL,
ADD INDEX idx_invoice_id (invoice_id_virtual);

Error: Generated column 'invoice_id' cannot refer to auto-increment column.

Event trigger ❌

ALTER TABLE orders
ADD COLUMN invoice_id INT AFTER id;



DELIMITER //
CREATE TRIGGER trg_generate_padded_id_insert
AFTER INSERT ON orders
FOR EACH ROW
BEGIN
    -- Update the invoice_id for the newly inserted row
    UPDATE orders
    SET invoice_id = CONCAT('INV', LPAD(id, 4, '0'))
    WHERE id = NEW.id and invoice_id_virtual is null;
END //

DELIMITER ;

Basically, I tried to do this trigger function that if any new row inserts It should auto-update the invoice_id column if it's null.

This was also a big failure. I think I need to research more about this event trigger feature. My subconscious mind said it should work.

View table ✅

CREATE OR REPLACE VIEW your_view_name AS
select 
  CONCAT("INV", LPAD(id, 6, '0')) as invoice_id, 
  orders.*  
from orders; 

What it will do, It will generate a custom view table on the fly that I told him to do it by the “select” statement.

View table results from the dummy table

So every time any new order is inserted in the orders table. The view table will auto-update. And also if we need to find any invoice id we can do this search from this view table.

Conclusion

I don't know, is it a better approach or not. But I tried my level best. If you found any other approach feel free to share it in the comments section.