SQL insert () values (),(),(); how to get auto_increments properly?
in [General]
|
Prev: Checking correct usage of fopen(), stream_set_timeout() and fread() [newbie]
Next: SQL insert () values (),(),(); how to get auto_increments properly?
From: Eric Lee on 14 Feb 2010 09:16 On Sat, Feb 13, 2010 at 7:41 PM, Jochem Maas <jochem(a)iamjochem.com> wrote: > Op 2/13/10 11:36 AM, Eric Lee schreef: > > > > > > On Sat, Feb 13, 2010 at 6:55 PM, Jochem Maas <jochem(a)iamjochem.com > > <mailto:jochem(a)iamjochem.com>> wrote: > > > > Op 2/13/10 10:08 AM, Lester Caine schreef: > > > Rene Veerman wrote: > > >> Hi. > > >> > > >> I'm looking for the most efficient way to insert several records > and > > >> retrieve the auto_increment values for the inserted rows, while > > >> avoiding crippling concurrency problems caused by multiple php > > threads > > >> doing this on the same table at potentially the same time. > > > > > >> Any clues are greatly appreciated.. > > >> I'm looking for the most sql server independent way to do this. > > > > > > Rene > > > The 'correct' way of doing this is to use a 'sequence' which is > > > something introduced in newer versions of the SQL standard. > > > Firebird(Interbase) has had 'generators' since the early days (20+ > > > years) and these provide a unique number which can then be > > inserted into > > > the table. > > > > > > ADOdb emulates sequences in MySQL by creating a separate table for > the > > > insert value, so you can get the next value and work with it, > without > > > any worries. The only 'problem' is in situations were an insert is > > > rolled back, a number is lost, but that is ACTUALLY the correct > > result, > > > since there is no way of knowing that a previous insert WILL > > commit when > > > several people are adding records in parallel. > > > > this is all true and correct ... > > > > but that doesn't answer the problem. how do you get the IDs of all > > the records > > that we're actually inserted in a multi-insert statement, even if > > you generate the > > IDs beforehand you have to check them to see if any one of the set > > INSERT VALUEs failed. > > > > @Rene: > > > > I don't think there is a really simple way of doing this in a RDBMS > > agnostic > > way, each RDBMS has it's own implementation - although many are > > alike ... and MySQL is > > pretty much the odd one out in that respect. > > > > it might require a reevaluation of the problem, to either determine > > that inserting > > several records at once is not actually important in terms of > > performance (this would depend > > on how critical the speed is to you and exactly how many records > > you're likely to be inserting > > in a given run) and whether you can rework the logic to do away with > > the requirement to > > get at the id's of the newly inserted records ... possibly by > > indentifying a unique > > indentifier in the data that you already have. > > > > one way to get round the issue might be to use a generated GUID and > > have an extra field which > > you populate with that value for all records inserted with a single > > query, as such it could > > function as kind of transaction indentifier which you could use to > > retrieve the newly > > inserted id's with one extra query: > > > > $sql = "SELECT id FROM foo WHERE insert_id = '{$insertGUID}'"; > > > > ... just an idea. > > > > > > > > > > > > > Hi > > > > I would like to learn more correct way from both of you. > > May I ask what is a sequences ? > > it an RDBMS feature that offers a race-condition free method of > retrieving a new unique identifier for a record you wish to enter, > the firebird RDBMS that Lester mentions refers to this as 'generators'. > > to learn more I would suggest STW: > > http://lmgtfy.com/?q=sql+sequence > > Jochem Thanks, Regards, Eric > > > > > > Thanks ! > > > > > > Regards, > > Eric > > > > -- > > PHP General Mailing List (http://www.php.net/) > > To unsubscribe, visit: http://www.php.net/unsub.php > > > > > > |