William, > > What should be understood is that MySQL's implementation of AUTO_INCREMENT >requires a particular/different > > philosophical view: that first the 'primary row' is to be stored, then the >AUTO_INCREMENT data captured, and > > finally the dependent row is stored (in the second table) - a reversal of the >earlier-mentioned approach. As > > soon as one understands how the whole job is to be done, the logic of the >individual components becomes > > 'obvious'. (and the old dog has to remember his new tricks!) > ... > I've run into one case where knowing the "next" ID would be useful was > where file names, being stored in the record contained the ID (for > performance reasons or maybe my bad design) since I was storing graphics > separately (for web work it makes great sense since the name needs to go > on the page not the data). I had to do an insert, get the > LAST-INSERTED-ID, and then update the record. I suspect that this is > really faster if there are multiple updated going on than locking the > table. Also, it enables me to use fixed length records.
=one of the previous correspondents in this (the original) thread described how any anticipatory calculation based upon LAST_INSERTED to arrive at the next AUTO_INCREMENT value was essentially flawed. It will work in a single-user environment, but without locking, risks corruption if the scenario allows for multiple concurrent-updates to the table. =in this case, you avoided the 'look-ahead' but at the cost of an extra db update call! (two for the price of one?) =do you mean that the graphic file name was the same as the AUTO_INCREMENT value in the corresponding tbl row? Do you further mean that the design featured only the one column, or was there an ID column and a graphic name column? =if the ID was the name, then to avoid the multi-user problem, the row could have been saved and then LAST_INSERTED used to (re-)name the graphic file. This sequence being congruent to the philosophy of MySQL's operation (as mentioned earlier/above) - why did you feel the need to name the file before storing its data in the table? (why not sequence them the other way around?) =if the ID and the filename were separate columns, then there was definitely no need to use ancitipation/look-ahead, any name would do as long as it was unique - you don't care and the machine knows no reason! In other words for row-x you could use the LAST_INSERTED value - the fact that it was a copy of the ID from the previous row of the table would be neither here-nor-there (there's no implicit, nor need there be an explicit, relationship between the two columns - and relational rules suggest that there should not be in any case). This presumes that you will name the first file/data in the first row yourself - filename 0000.filetype for example! =it's always a risk if you use a single field to mean two different things and yet fulfill objectives in two different contexts (trouble is, we all try to get away with it...) =BTW: yes I note that this is ancient history and not a current problem to be solved - just watching out for the risk-factors that you discuss below, and the fact that someone else might want to 'learn' from your post. > Didn't even think of locking the tables. =too much hassle for this "bear of very little brain" too! > BTW: an earlier poster mentioned DB2. You have to have a separate Key > table and use an Update with a Sub Select and lock that table to get the > next key since DB2 still (as of the version I was using) have > auto-increment fields. =there you go, I don't remember using AUTO_INCREMENT since I first used DB2 (that was when the paint was still wet, and the performance about as slow pouring!) I think I've only ever 'patched' DB2 calls - or translated them into ORACLE for the last 15 years... > Don't know if its true, but a DBA I worked with insisted that MicroSoft's > SQL's auto-increment was flawed so we had to do the same thing there, > even though it did have auto-increment. Perhaps a hold over from the > "doing it by hand" days.. =I've never been a great fan of MS SQL-Server, but I seem to recall that there was a 'discovered fault' of this nature in one (?early) release. It was fairly arcane IIRC. It was reasonably quickly patched though. Meantime I do confess to using the incident as part of my 'proof' that the product wasn't 'up to snuff' for corporate/enterprise systems... =These days I would always use the RDBMS' AUTO_INCREMENT (or equivalent) feature. I can think of no good reason (in any current 'mainstream' RDBMS) to manufacture a tbl ID/sequence - and if you have to manufacture some accumulator in the dependent part of a row, then there is no way out (in a multi-user environment) but to use locking and MAX() etc. [see also this topic discussed somewhere in the MySQL manual] =Thanks for the memories! =dn --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
