What is the best way to generate a unique number in the database, in addition to the auto-increment key?

Question

Database design

There is a database like:

группа
id name code ...
1  1b2    111

студент
id group_id name contract_number
1  1   Иванов  ?

and now, when creating a student, we need to form a unique contract number contract_number for him.
at the same time, the following parts are present in the contract number:
- there may be some kind of prefix, not even a number, so numerical operations disappear
- there must be a code from the group
- the date can be in the form 090621

How would it be better to organize this so that unique contract numbers are obtained.
During the day, keep a suffix, such as
111 090621 001
111 090621 002
, somehow it’s not very interesting to look for all such numbers that have
contract_number like '__090621%' inside
, this is in php you have to sort through everything, not in Mysql.

It is also impossible to keep a separate table and take these generated numbers from there, because The numbering still depends on the group.

Can eat what else successful approaches to the decision of such task?

ps Plus, there are still no guarantees that this part will be a strictly defined number of digits before the date, they may require some kind of id to be inserted there, i.e. even this like will not work, and changing to % is not quite, I think, mathematically correct.

Answer 1

You are aware that a unique index can be hung on a set of fields, and not on one field? Keep all these fields in different columns of the table, and combine them into one unique index for integrity control.

Answer 2

do not mix technical information with applied
unique key - technical, unique contract number - applied,
in short, natural keys - evil, abstract - good,
if the customer certainly wants to stuff something into the contract number
(and guaranteed to want more in a year)
then
1) store this something as separate search fields
2) do not use the contract number for links in any case
3) do not store the number but collect it with a request only for output (if you prohibit editing the original fields)
4) uniqueness to the contract number will be added by a timestamp

Answer 3

Make the contract a separate entity and store it in a separate table. One day you will inevitably have students with multiple contracts.
To select a unique number, fix the full prefix and look for the maximum number with this prefix
contract_number like '111090621%'