How to set an a second Auto increment column per user?

Question

I know this question has been asked before and most of the answers warn about doing so or suggest a solution for MyISAM but I'm using InnoDB. I'm trying to generate an Invoice or Bill for the user for him to give out to me (Business plan requirement) !

The thing is that in my country the reference of the ID of the bill for the same person should be ordered or he will be audited for the missing bills. Like for example he gave me one bill with 0001 and the second one is 0005. He will be interrogated for the missing 4.

So I need to have a custom auto-increment per UserID.

User 1 - idUser= 1 ,idBill = 1

User 1 - idUser= 1 ,idBill = 2

User 2 - idUsr = 2 , idBill = 1

Some threads suggested using triggers while others warned about table locks. I personally not familiar with triggers so I steer away from them since they require maintenance.

I am using Java and MySQL.

Answer 1

An example:

CREATE TABLE main (id INT AUTO_INCREMENT PRIMARY KEY,
                   primary_id CHAR(3), 
                   secondary_id INT) ENGINE = InnoDB;

CREATE TABLE auxiliary (primary_id CHAR(3), 
                        secondary_id INT AUTO_INCREMENT,
                        PRIMARY KEY (primary_id, secondary_id)) ENGINE = MyISAM;

CREATE TRIGGER generate_secondary_id
BEFORE INSERT 
ON main
FOR EACH ROW
BEGIN
    INSERT INTO auxiliary (primary_id) VALUES (NEW.primary_id);
    SET NEW.secondary_id = LAST_INSERT_ID();
END

INSERT INTO main (primary_id) VALUES
('A01'),
('A01'),
('B01'),
('C01'),
('A01'),
('B01'),
('A01'),
('B01');

SELECT * FROM main;

id | primary_id | secondary_id
-: | :--------- | -----------:
 1 | A01        |            1
 2 | A01        |            2
 3 | B01        |            1
 4 | C01        |            1
 5 | A01        |            3
 6 | B01        |            2
 7 | A01        |            4
 8 | B01        |            3

db<>fiddle here