Get insert_id for all rows inserted in single mysq

问题:

Working in PHP and using MYSQLI. Trying to get the insert_id for all rows inserted from a single insert query with multiple values. Keeping it in a single call for efficiency. My actual code has hundreds of values in one insert query. However, here is some sample code for an insert with just 4 values:

$sql = "INSERT INTO link_tags ( string_names_id, urls_id ) 
    VALUES ( '2', '17' ), ( '8', '31' ), ( '8', '1' ), ( '8', '4' )";

$mysqli->query($sql);

echo "id's: " .$mysqli->insert_id;

The above code achieves the insert, but only gives me the id of the first insert from the call. How can I retrieve all of the ID's for this single query?

回答1:

It should be safe to assume that your ids are mysql_insert_id + the next 3 (whatever) in succession, as your statement is done in one transaction

回答2:

No, this isn't possible with MySQL.

Its not even really save to use LAST_INSERT_ID() as it will return a BIGINT (64-bit) value representing the first automatically generated value successfully inserted for an AUTO_INCREMENT column.

You could guess that this value + length of your data list will be the ID's but there isn't any absolute guarantee this will always be true.

回答3:

As @thomas pointed, I used this solution and suppose it's ok to detect last insert id;

$id = $link->insert_id;
if ($id && $link->affected_rows > 1) {
    $id = ($id + $link->affected_rows) - 1; // sub first id num
}

In your situation (for all ids);

$id = $link->insert_id; $ids = [$id];
if ($id && $link->affected_rows > 1) {
    for ($i = 0; $i < $link->affected_rows - 1; $i++) {
        $ids[] = $id + 1;
    }
}

// or more dirty way
$id = $link->insert_id; $ids = [$id];
if ($id && $link->affected_rows > 1) {
    $ids = range($id, ($id + $link->affected_rows) - 1);
}

Meanwhile, I used multi_query and handled result in while loop but could not achieve expected result.

while ($link->more_results() && $link->next_result()) $ids[] = $link->insert_id;