last insert ID's

[The Little Guy]

Lets say I want to insert into a table using one query, like this:

 

insert into test (name) values ('monkey'), ('baseball');

 

what can I do to get both of the insert ids?

[floridaflatlander]

a table but both id's?

[The Little Guy]

I know there is last_insert_id() but it only gets one of them, I would like a table that returns all of them (I believe what your saying).

[Illusion]

from mysql documentation

In the case of a multiple-row INSERT statement, mysql_insert_id() returns the first automatically generated AUTO_INCREMENT value; if no such value is generated, it returns the last explicit value inserted into the AUTO_INCREMENT column.

 

in that case the id range might be mysql_insert_id()  to mysql_insert_id() + number of effected rows -1.

[The Little Guy]

is it possible for two members to run this, and they are inserted every other?

 

for example we both insert 6 records (total of 12) and it looks like this for the insert:

 

me

you

me

you

me

you

..

..

and so on

 

If that happens, then this won't really work will it?

[Mahngiel]

wouldn't it though? i mean, in the example situation you provided, you're suggesting that you'd each be running six queries, in which case last_id would be viable on it's own.  But in your OP, you are inserting X number rows in one insert.  Therefore it'd be valid that last_id - X  would return your IDs if there were so incremented.

[The Little Guy]

with a single insert query, the next insert waits, till the previous one is done before it can go, I would assume that this is the same thing that way you wouldn't have the alternating inserts. I am not sure though.

[Mahngiel]

That is something I do not know, how mysql queues those things.

 

If you had one insert statement of 500,000 rows, and I another statement with equal rows, and at the same time executed - which order they would be inserted?

[Barand]

is it possible for two members to run this, and they are inserted every other?

 

for example we both insert 6 records (total of 12) and it looks like this for the insert:

 

me

you

me

you

me

you

..

..

and so on

 

If that happens, then this won't really work will it?

 

mysql_insert_id is per connection so "you" would get your insert_id and "me" would get mine.

[requinix]

Multiple simultaneous INSERT queries will be queued up, not executed in parallel. I believe all engines will write-lock tables during INSERTs, but won't necessarily read-lock. Thus if you run one INSERT with multiple values, all the values will be inserted in one (pseudo-)atomic operation.

In other words a mysql_insert_id() - mysql_affected_rows() should be safe.

[The Little Guy]

Going off Illusion:

insert into table_a (col1) values ('one'), ('two'), ('three');
set @first_iid = (select last_insert_id());
set @last_iid = (select (row_count() + @first_iid) - 1);

 

That should get us our first and last insert id's, and assuming the table increments by one, you can calculate all the rows inserted by basic math.

 

I keep hearing that doing this is bad though, because someone could do an insert while my insert is only half way complete. But it seems like some of you are disagreeing with that.

[fenway]

I keep hearing that doing this is bad though, because someone could do an insert while my insert is only half way complete. But it seems like some of you are disagreeing with that.

 

You can retrieve the most recent AUTO_INCREMENT value with the LAST_INSERT_ID() SQL function or the mysql_insert_id() C API function. These functions are connection-specific, so their return values are not affected by another connection which is also performing inserts.

 

But Barand already told you this.

[The Little Guy]

I keep hearing that doing this is bad though, because someone could do an insert while my insert is only half way complete. But it seems like some of you are disagreeing with that.

 

You can retrieve the most recent AUTO_INCREMENT value with the LAST_INSERT_ID() SQL function or the mysql_insert_id() C API function. These functions are connection-specific, so their return values are not affected by another connection which is also performing inserts.

 

But Barand already told you this.

 

Right but if I get auto_id 10 on one connection, and another connection gets auto_id 11 and we each insert 5 rows, I am not going to get all 5 of my inserts, I will get mine and someone esle's. since supposedly the table doesn't get locked on inserts, but maybe that is Storage Engine Specific...

[cpd]

In answer to your original question.

 

The only real way - that I know of - you can get all your IDs is using a stored procedure; all the new IDs generated can be returned as a result set.

 

Moreover, you can add further control to the system by creating a table called "InsertIDs", or similar, which houses all your table names along with the "next ID" to be used upon insert. You should note not ever table may required manual tracking of the ID.

 

This would eliminate an auto increment field in any table you then manage and you can easily track the IDs being inserted with multi insert statements. This would however then require you to handle the procedures arguments which would need to be XML or something as your inserting multiple rows etc... you get the gist, hopefully.

 

Quite a bit of work to do though.

[kicken]

I can't find anywhere in the manual the confirms it, but I would trust that if you do use multi-row insert syntax that it will be performed all at once and you can count on the generated id's being in the range of last_insert_id() through last_insert_id+row_count().

 

I doubt mysql would let some other client sneak a row in during a mass-insert.

 

[Mahngiel]

@Little Guy:  Are you trying to resolve a problem you know exists? or are you just trying to cover all your bases?

 

8.7.1. Internal Locking Methods

 

This section discusses internal locking; that is, locking performed within the MySQL server itself to manage contention for table contents by multiple sessions. This type of locking is internal because it is performed entirely by the server and involves no other programs

http://dev.mysql.com/doc/refman/5.0/en/internal-locking.html

[The Little Guy]

@Little Guy:  Are you trying to resolve a problem you know exists? or are you just trying to cover all your bases?

 

One of our tables has about 225,000 (~2/sec) inserts per day, and every so often we need to insert 2+ records, so instead of doing one at a time to get the insert ID's it would be nice to do it all at once.

 

So with about 2 addition inserts per second, you can almost guarantee that while I am doing my mass insert someone else will be wanting to insert 1 or more rows as well. So if the other person doesn't get locked out, he could insert halfway through my insert, and I will then have bad historical data.

 

Does that answer your question?

[fenway]

You're not listening -- it's per connection -- no matter how many you insert.

[gizmola]

You're not listening -- it's per connection -- no matter how many you insert.

 

To elaborate on what fenway stated, in the context of apache, each script is running in its own thread, and thus its own connection.  If it didn't work this way, mysql_insert_id would be useless.

 

One other thing about a mult-insert is that it is by its definition, an atomic operation.  This is important in mysql with myisam for example, which does not have transactions at all, and only does table locking.

 

So in summary, no there is no way on a multi-row insert could end up with a non-contiguous range of id's. 

[fenway]

So in summary, no there is no way on a multi-row insert could end up with a non-contiguous range of id's. 

Actually, just to be annoying, there is -- auto_increment_increment ;-)

[gizmola]

Haha, well not what I was talking about, but sure, that oddball setting still wouldn't allow for what he was orginally concerned with -- having 2 different multirow inserts running near simultaneously, resulting in the interleaving of key values.  One would complete and get its range, and the other would get the next range. 

 

Still, we count on you to come up with this mysql minutiae fenway.  Don't ever change man!

[fenway]

Still, we count on you to come up with this mysql minutiae fenway.  Don't ever change man!

That's what I'm here for ;-)