Can batchInsert() return all inserted ids ?

Yii 2.0 General Discussions
1

Hi all.

If you use batchInsert() like this, can you somehow figure out ids of inserted rows ?


Yii::$app->db->createCommand()->batchInsert('user', ['name', 'age'], [

    ['Tom', 30],

    ['Jane', 20],

    ['Linda', 25],

])->execute();

I am talking about auto incremet ID’s.

2

This should probably work for MySQL (I’m not sure about other DBs):


$count = Yii::$app->db->createCommand()->batchInsert('user', ['name', 'age'], [

    ['Tom', 30],

    ['Jane', 20],

    ['Linda', 25],

])->execute();


$firstID = Yii::$app->db->getMasterPdo()->lastInsertId();


$lastID = $firstID + $count - 1;

http://php.net/manual/en/pdo.lastinsertid.php

The execute() method returns the number of affected rows and the lastInsertId() returns the first auto-increment ID used.

3

at first lastInsertId() return the last insertd id and not the first, so $lastID = $firstID + $count - 1; is wrong

secondly concurrent batch insert (2 different user that call the same page that does the batch insert at same time) do not produce sequential id

batchinsert can’t return id by design since the format of the generated sql is (with some difference based on db)


INSERT INTO user ('name', 'age') values ('Tom', 30), ('Jane', 20) ,('Linda', 25);

So it is not possible to return the ids.

If you need the id create your own batch procedure that does single insert.

batchInsert is much faster but if you need REAL id is the only way (not completely true, by doing some change on the table and a couple lines of code you can still get them)

The application design depends also on usecase.

Even if batchInsert is the optimum on developer side, in some case can be insignificant on user side

10000 record as single insert will take 3 sec of execution

  • 3 second 2 time per day is an acceptable time for the user.
  • 3 seconds every 5 minute is still ok but maybe become annoying.
  • 3 second every minute of work probably start to be unacceptale

So mostly depends on how many time you need to run this batch insert and how (user or cronjob) and on how many data (=execution time).

4

It returns "Value of the AUTOINCREMENT column for the last INSERT". So if you insert multiple rows it returns the ID of the first inserted row (for the last INSERT).

It’s also shown in MySQL docs: http://dev.mysql.com/doc/refman/5.7/en/information-functions.html#function_last-insert-id

You are right about that. I haven’t taken this scenario into account. So your answer makes more sense.

5

Yes you’re right.

6

What if i am using InnoDb with Transaction block, Bulk insert is atomic according to this question stackoverflow.com/questions/21584207/…. Though atomicity doesn’t guarantee that ids will have successive values. But “When accessing the auto-increment counter, InnoDB uses a special table-level AUTO-INC lock that it keeps to the end of the current SQL statement” and “two transactions cannot have the AUTO-INC lock on the same table simultaneously”. So it seems guarantees successive ids values. dev.mysql.com/doc/refman/5.5/en/…

So dont you think if we are using innoDB along with transaction block to run the batch insert, wouldnt it work correctly.

7

I’m using the following approach on my projects. I works on PosgreSQL. Maybe it can be adapted to other DBMSs:


$sql = Yii::$app->db->createCommand()
    ->batchInsert(User::tableName(), ['name', 'age'], $rows)
    ->rawSql . ' RETURNING idanotacao'; // appends Postgresql RETURNING clause
$userIds = Yii::$app->db->createCommand($sql)->queryColumn();