Search code examples
mysqlinnodbauto-increment

MySQL: Insert multiple rows with same AI value

Let's say this is my table:

CREATE TABLE tab (
    id INT AUTO_INCREMENT NOT NULL,
    val VARCHAR(9),
    KEY(id),
    PRIMARY KEY (xx)
);

Would it possible to insert multiple rows at the same time in a way that they would all get the same auto-increment value?

The following works, but increments each new row, regardless of the fact that we are doing a single query.

INSERT INTO tab (id,val) VALUES (LAST_INSERT_ID(),'a'), (LAST_INSERT_ID(),'b');

How could I make sure they all receive the same auto-incremented ID in a single query?

Solution

  • You will need to keep the first AI value in a variable and pass it in the INSERT query for different pairs