How to mirror auto increment value and combine with value in another column in mysql

I have an auto increment column id in a table, this works fine but I am trying to create an order_id column that combines the account_id of a user with the auto increment value of id.

So the values inside order_id look for example like this:

33.1000
52.1001
69.1002
Etc

Where the number before the dot is the account_id and the number after is the auto incremented value of id.

How can I do this?

Similar answers I found are for another database language or are questions with dead links.

I’ve tried immediatly updating the database after an insert using PHP with the last inserted id function insert_id but I’m sure this can be done in SQL.

Answer

You can construct the value as:

concat(account_id, '.', lpad(id, 4, '0'))

Unfortunately, you cannot use this as a generated value or default value for the column. That gives you two options.

The first is to use a view:

create table v_t
    select t.*, concat(account_id, '.', lpad(id, 4, '0')) as order_id
    from t;

Or to set the value using a trigger.