Is it possible to get the id of inserted elements in mysql?

Question

MySQL

I am using mysqli in php.
When inserting a single record with the insert command, you can get the id of the inserted record.
If the insert command inserts several records, then I still get one id of the first inserted record.
Is it possible to get the id of all inserted records at once with the insert command in mysql?

Answer 1

For InnoDB (the engine guarantees sequential insertion for fields with AUTO_INCREMENT):
1. Set the setting innodb_autoinc_lock_mode=1 (default)
2. Make an INSERT.
3. Get LAST_INSERT_ID().
4. Get ROW_COUNT().
5. Generate record id based on the two previous points.
For all:
1. Add the "batch_id" field with indexing on it.
2. When preparing data, set the generated batch_id to all elements of the batch
3. After INSERT, do a SELECT by bacth_id

Answer 2

Without additional requests - no.

Answer 3

One of the reasons why I personally switched to Postgresql. There is a RETURNING which can return any field from the inserted row, not just the ID. If there are no specific cases to use RDBMS, then the transition is simple.
Regarding the context of the task. I will assume that it is necessary for the front/client to receive identifiers of interposed records. But you can solve this problem by going from the other side. Generate identifiers on the client (i.e. conditional record IDs are already known BEFORE inserting into the database). As such an identifier, it is better to use UUID , the implementation of which is available in absolutely all popular languages.