Get all inserted IDs when inserting multiple rows

Question

I've already looked at other answers and I still feel that my question is relevant and deserves a separate entry.

I have a table named settings(which stores user settings) and I have to insert multiple settings for each user. Initially, I had executed a separate insert statement for each setting, but having felt this wasn't a particularly good way to do it, I thought of inserting multiple rows by the same insert statement. My only problem is that I want the auto_incremented IDs of each of the newly inserted rows.

I've read answers that say this isn't possible/scalable etc, but I feel that I have hit upon the solution. I want feedback whether my way is correct or not and hence this question.

What I've done is simple. After inserting the multiple rows, I call last_insert_id() to get the ID of the first row of the simultaneously inserted rows. I already have the count of the number of rows inserted, so I simply create a new array and populate it with IDs starting at last_insert_id() and ending at last_insert_id()+n-1 (where n is the number of rows inserted).

I feel this will work because of the following reasons:

1.) MYSQL documentation states that last_insert_id() is connection dependent and if another client/connection inserts new records, then that won't affect other client's last_insert_id().

2.) I feel that as the insert is being done by a single SQL statement, the whole insertion should be treated as a single transaction. If that is true, then ACID rules should apply and the auto_incremented values should be sequential. I'm not sure about this one.

Those are my reasons why I feel the logic should work. So my question is, will the above logic work for ALL conditions? Can I rely on it to work correctly in all situations? I know it is working for me currently.

Answer 1

I already doing this is my app. I insert records in loop and when each record is inserted i will store the Auto increment id in array by call last_insert_id(). So i can use the array of inserted id's where ever i need.

Answer 2

This behaviour shouldn't be relied upon; besides the obvious locking issues, let's say you want to set up master<->master replication; all of a sudden, the id's increment by 2 every time.

Besides that, instead of actually writing multiple insert statements, it might be worth using prepared statements:

$db = new PDO(...);
$db->beginTransaction();
$stmt = $db->prepare('INSERT INTO `mytable` (a, b) VALUES (?, ?)');

foreach ($entries as $entry) {
    $stmt->execute(array($entry['a'], $entry['b']));
    $id = $db->lastInsertId();
}

$db->commit();

Answer 3

How about:

$id = array();

$sql = "INSERT INTO `table` VALUES ('', 'foo', 'bar');";
$sql .= "INSERT INTO `table` VALUES ('', 'foo', 'bar');";
$sql .= "INSERT INTO `table` VALUES ('', 'foo', 'bar');";
$sql .= "INSERT INTO `table` VALUES ('', 'foo', 'bar');";
$sql .= "INSERT INTO `table` VALUES ('', 'foo', 'bar');";
$sql .= "INSERT INTO `table` VALUES ('', 'foo', 'bar');";

if ($db=new mysqli('host', 'user', 'pass', 'dbase'))
{
    $db->multi_query($sql);

    if ( isset($db->insert_id) ) $id[] = $db->insert_id;

    while ($db->more_results())
    {
        if ($db->next_result()) $id[] = $db->insert_id;
        else trigger_error($db->error);
    }

    $db->close();
}
else trigger_error($db->error);

if (count($id) == 0) trigger_error('Uh oh! No inserts succeeded!');
else print_r($id);

Perhaps this would return your insert ids like you are wanting. I haven't tested it, but perhaps you could adapt it for your purpose and who knows it might actually work.

Answer 4

I did this for a bit though did not find the data useful in the end so stopped.

I track the datetime and user_who_altered of each entry into the table so retrieving the list becomes simple.

It is important to set a variable with the time however, rather than relying on NOW():

  INSERT INTO table (username,privelege,whenadded,whoadded) VALUES ('1','2','$thetime','$theinserter'),('1','5','$thetime','$theinserter'),etc...;

will insert the multiple rows nicely.

to retrieve the array you seek:

SELECT idrow FROM table WHERE username='1' AND whenadded='$thetime' AND whoadded='$theinserter';

This is good practice as you are able to track which user modified the record and it does not depend on locks or chance.

As for your own solution, it will work and saves a query. I would worry about how it might respond to prepared statements on a busy table though and perhaps the method used ought to consider that. The method I use is immune to such problems.

Answer 5

I agree with @anigel. You cant be sure that some transactions wouldnt get muddled up. You can split the inserts into separate queries, call last_insert_id() for each individual query and populate an array with the results.

Granted, this may increase processing time but at least you can be sure of avoiding conflicts. Plus, since its a settings table, its highly unlikely that you'll have to run a ton of transactions per client request

Answer 6

If you like to gamble - then do this :)

To be 99% sure you would have to lock the table for writing. You are not sure that the (in future) two transactions  will not be able to intertwine.

To be 100% sure you read these values. (Or analyze the source MySQL) The best solution would be to add the date to tablei edit settings and read the latest. If you do not want to change the structure, you can use triggers http://dev.mysql.com/doc/refman/5.0/en/create-trigger.html.

Good solution will be refresh all your settings or only pairs: key - setting name