Auto-increment value generation in composite primary key not on first field in innoDB table

Question

PHP

The essence of the problem is that there is such an opportunity for MyISAM tables, but not for innoDB.

There is a table How to make it so that when inserting a user_id value into a table, the value for message_id is generated automatically?

messages
user_id INT
message_id INT


It should look like this picture

user_id	message_id
one	one
one	2
one	3
2	one
2	2
2	3

I made a trigger that sets the message_id value as the maximum id + 1 before inserting,
everything works as it should, but after inserting, I don’t know which message_id the trigger recorded.

The option with message_id calculation and then insertion (in two requests) is not suitable.

Answer 1

I would say that it is not necessary to do so. message_id must be a unique auto-increment.
And if you want to display a beautiful numbering individual for each user, then make a separate `number` SAMLLINT field - and calculate it as a trigger.