SQL insert () values (),(),(); how to get auto_increments properly?
in [General]
|
Prev: SQL insert () values (),(),(); how to get auto_increments properly?
Next: SQL insert () values (),(),(); how to getauto_increments properly?
From: Jochem Maas on 13 Feb 2010 06:41 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 > > > Thanks ! > > > Regards, > Eric > > -- > PHP General Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > > |