Re: next insert id
I'm not going to address these comments to anyone - in an attempt to avoid inter-personal conflict... Herewith some psychology and philosophy: - some people can't be bothered to read the manual - writing a (good/complete) manual is even harder than reading it - fully appreciating each fine detail as you read it amidst a myriad of ifs, buts, and maybes, in some language feature/function's technical description can be challenging - some people find it easier to ask others than to read/work it out for themselves - some people think others are paid to be on the list to answer questions - some people seem to think they will 'break' the computer/MySQL if they 'experiment' - some people think that if they don't know/can't read something, asking others is preferable to/quicker than experimentation - some people think they know the answer to list questions and dash off a response without checking the manual/really understanding the question/really knowing the answer - the person who has provided the most consistently correct information in this thread has been most criticised - the people who provided incorrect/out-of-date information/advice have yet to be castigated - would you put incorrect advice on a par with Microsoft marketing blurb? (and you know how techies like to rage against that!) - technical manuals tend to lay out the facts this is how it works, and any rules/exceptions - technical manuals do not tend to get into the design philosophy behind the code that implements the commands/makes it all work - many users find discussion of such philosophy 'incidental' and ask for the 'short version' Why did the original post-er ask the question? Almost certainly because (s)he has read the manual where it says that there is no native support for referential integrity in MySQL, and needs to set up a relationship between two rows in different tables using a column that has its value set by AUTO_INCREMENT. I first learned SQL as pure theory - before the standard was implemented. The earliest versions/implementations, eg early DB2 and ORACLE, did not include native referential integrity, it was something we were taught to code for ourselves. I'm fairly sure that the prevailing wisdom (now we're straying into the area of transaction-processing) was that you worked out what the 'primary record' would be, and then coded up the dependent record (including its foreign key) and stored it, and finally stored the 'primary' record. IIRC and if there are other (non-MySQL) implementations that follow that same approach, then this explains why so many people ask the question what will be the NEXT key to be used by AUTO_INCREMENT. Unfortunately respondents trying to tackle that question literally, disappear quickly into transactional processing. However MySQL offers a range of functions to 'display' AUTO_INCREMENT values, and report back on recent activity. More so when you include interfacing languages' functions, eg PHP or Perl; and many of these occur automatically as a result/by-product of an earlier call to MySQL (rather than requiring a second/separate/additional call to be made across the interface). NB this feature/characteristic nullifies some of the other comments made during this thread. 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!) Did I miss anything/get any part wrong? Regards, =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
Re: next insert id (slightly OT)
On Thu, 31 Jan 2002, DL Neil wrote: ... 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. Didn't even think of locking the tables. 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. 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.. Sincerely, William Mussatto, Senior Systems Engineer CyberStrategies, Inc ph. 909-920-9154 ext. 27 - 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
Re: next insert id (slightly OT)
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 .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:
Re: next insert id (slightly OT)
On Thu, 31 Jan 2002, DL Neil wrote: Date: Thu, 31 Jan 2002 21:28:04 - From: DL Neil [EMAIL PROTECTED] To: William R. Mussatto [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: Re: next insert id (slightly OT) 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. That is why I did an add, got the LAST-INSERTED-ID. =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? The name was derived from the ID. This was so the display program could just grab it and go. =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?) Ah, but the name had to go into the database at some point. =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 .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...) Caught me, I use a configuration table to tell the use of the various fields and if the field is of type file the contents is read and subsitituted. This was mainly a reaction to a version of mysql (since fixed) that required the server be halted to do table reorgs. (Yes yes I know its no longer true). Also I'm a fan of fixed lenght records and putting large things on the file system and things that need to be searched in the database. =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
next insert id
database,sql,query,table I need to find out what the next value will be in an auto_increment field will be. could someone help me out with the select statement? - 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
Re: next insert id
SELECT MAX(id) FROM table; will get you the highest value for 'id', that +1 will be the 'next' value, until someone inserts a new row into that table. james montebello On Tue, 29 Jan 2002, Joel Wickard wrote: database,sql,query,table I need to find out what the next value will be in an auto_increment field will be. could someone help me out with the select statement? - 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 - 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
RE: next insert id
INSERT into the table then SELECT last_insert_id() or when using php query(INSERT QUERY) $last_id=mysql_insert_id() OR INSERT SELECT MAX(id) FROM tablename assuming id is the auto incremnt field. - Johnny Withers [EMAIL PROTECTED] p. 601.853.0211 c. 601.209.4985 -Original Message- From: Joel Wickard [mailto:[EMAIL PROTECTED]] Sent: Tuesday, January 29, 2002 7:04 PM To: [EMAIL PROTECTED] Subject: next insert id database,sql,query,table I need to find out what the next value will be in an auto_increment field will be. could someone help me out with the select statement? - 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 - 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
Re: next insert id
At 17:03 -0800 1/29/02, Joel Wickard wrote: database,sql,query,table I need to find out what the next value will be in an auto_increment field will be. could someone help me out with the select statement? You can't find out what it *will be* until you actually create the record. Then you get the like this: SELECT LAST_INSERT_ID(); Check the discussion for LAST_INSERT_ID() in the MySQL manual. It will tell you the answer to your next question. :-) - 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
Re: next insert id
Not _necessarily_ true, surely, if there's another transaction active at the moment (assuming you are using a table that supports transactions)...? At 03:16 PM 1/29/2002 -0800, James Montebello wrote: SELECT MAX(id) FROM table; will get you the highest value for 'id', that +1 will be the 'next' value, until someone inserts a new row into that table. james montebello On Tue, 29 Jan 2002, Joel Wickard wrote: database,sql,query,table I need to find out what the next value will be in an auto_increment field will be. could someone help me out with the select statement? - 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 - 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 - 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
Re: next insert id
At 15:16 -0800 1/29/02, James Montebello wrote: SELECT MAX(id) FROM table; will get you the highest value for 'id', that +1 will be the 'next' value, until someone inserts a new row into that table. Which may already have happened between the time you created your record and the time you issue the SELECT shown above. In which case, you'll get the wrong result. Use LAST_INSERT_ID() instead. james montebello On Tue, 29 Jan 2002, Joel Wickard wrote: database,sql,query,table I need to find out what the next value will be in an auto_increment field will be. could someone help me out with the select statement? - 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
Re: next insert id
And then you hope that between 'insert' and 'select' nobody else inserted another record? laszlo Johnny Withers wrote: INSERT into the table then SELECT last_insert_id() or when using php query(INSERT QUERY) $last_id=mysql_insert_id() OR INSERT SELECT MAX(id) FROM tablename assuming id is the auto incremnt field. - Johnny Withers [EMAIL PROTECTED] p. 601.853.0211 c. 601.209.4985 -Original Message- From: Joel Wickard [mailto:[EMAIL PROTECTED]] Sent: Tuesday, January 29, 2002 7:04 PM To: [EMAIL PROTECTED] Subject: next insert id database,sql,query,table I need to find out what the next value will be in an auto_increment field will be. could someone help me out with the select statement? - 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 - 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 - 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
Re: next insert id
This result will be invalid when the a new row is written to the table, whether it's part of a transaction or not, no matter what the table type. I said as much, but perhaps I was a bit too cryptic. Generally speaking, getting this value reliably while other work is going on is only possible if you lock the table, do the select, do whatever you're going do to with that value, and unlock the table. As soon as you do the unlock, the value is useless. james montebello On Tue, 29 Jan 2002, Christopher Thompson wrote: Not _necessarily_ true, surely, if there's another transaction active at the moment (assuming you are using a table that supports transactions)...? At 03:16 PM 1/29/2002 -0800, James Montebello wrote: SELECT MAX(id) FROM table; will get you the highest value for 'id', that +1 will be the 'next' value, until someone inserts a new row into that table. james montebello On Tue, 29 Jan 2002, Joel Wickard wrote: database,sql,query,table I need to find out what the next value will be in an auto_increment field will be. could someone help me out with the select statement? - 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 - 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 - 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
Re: next insert id
Of course, if you do LAST_INSERT_ID, then try to use the value while someone else is doing an insert, you're in the same boat. Neither method is reliable without a lock. james montebello On Tue, 29 Jan 2002, Paul DuBois wrote: At 15:16 -0800 1/29/02, James Montebello wrote: SELECT MAX(id) FROM table; will get you the highest value for 'id', that +1 will be the 'next' value, until someone inserts a new row into that table. Which may already have happened between the time you created your record and the time you issue the SELECT shown above. In which case, you'll get the wrong result. Use LAST_INSERT_ID() instead. james montebello On Tue, 29 Jan 2002, Joel Wickard wrote: database,sql,query,table I need to find out what the next value will be in an auto_increment field will be. could someone help me out with the select statement? - 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
Re: next insert id
Nathan: I suspect you are trying to solve the wrong problem. Why don't you tell us _why_ you think you need this value, what you are trying to accomplish. At 04:23 PM 1/29/2002 -0700, Nathan wrote: But if this is the MyISAM table type, and you performed an insert, then a delete, your MAX + 1 would get the next value of the series, but the auto_increment will actually use MAX + 2. Correct? Is there a system call to find out what MySQL will use next rather than the highest + 1? # Nathan - Original Message - From: James Montebello [EMAIL PROTECTED] To: Joel Wickard [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Tuesday, January 29, 2002 4:16 PM Subject: Re: next insert id SELECT MAX(id) FROM table; will get you the highest value for 'id', that +1 will be the 'next' value, until someone inserts a new row into that table. james montebello On Tue, 29 Jan 2002, Joel Wickard wrote: database,sql,query,table I need to find out what the next value will be in an auto_increment field will be. could someone help me out with the select statement? - 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 - 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 - 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 - 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
RE: next insert id
* James Montebello Of course, if you do LAST_INSERT_ID, then try to use the value while someone else is doing an insert, you're in the same boat. Neither method is reliable without a lock. This is not correct. The LAST_INSERT_ID() function return the last inserted auto increment key for _this_ connection... this is fairly easy to test: just open two different connections to the same mysql db, insert some records into the same table in both consoles, and check last_insert_id() in both... repeat until satisfied :o) -- Roger - 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
Re: next insert id
At 15:35 -0800 1/29/02, James Montebello wrote: Of course, if you do LAST_INSERT_ID, then try to use the value while someone else is doing an insert, you're in the same boat. Neither method is reliable without a lock. That's incorrect. LAST_INSERT_ID() is client-specific, other clients can't mess you up. james montebello On Tue, 29 Jan 2002, Paul DuBois wrote: At 15:16 -0800 1/29/02, James Montebello wrote: SELECT MAX(id) FROM table; will get you the highest value for 'id', that +1 will be the 'next' value, until someone inserts a new row into that table. Which may already have happened between the time you created your record and the time you issue the SELECT shown above. In which case, you'll get the wrong result. Use LAST_INSERT_ID() instead. james montebello On Tue, 29 Jan 2002, Joel Wickard wrote: database,sql,query,table I need to find out what the next value will be in an auto_increment field will be. could someone help me out with the select statement? - 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
Re: next insert id
At 15:30 -0800 1/29/02, laszlo wrote: And then you hope that between 'insert' and 'select' nobody else inserted another record? Only for the last method. The first two return client-specific values. laszlo Johnny Withers wrote: INSERT into the table then SELECT last_insert_id() or when using php query(INSERT QUERY) $last_id=mysql_insert_id() OR INSERT SELECT MAX(id) FROM tablename assuming id is the auto incremnt field. - Johnny Withers [EMAIL PROTECTED] p. 601.853.0211 c. 601.209.4985 -Original Message- From: Joel Wickard [mailto:[EMAIL PROTECTED]] Sent: Tuesday, January 29, 2002 7:04 PM To: [EMAIL PROTECTED] Subject: next insert id database,sql,query,table I need to find out what the next value will be in an auto_increment field will be. could someone help me out with the select statement? - 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 - 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 - 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 - 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
Re: next insert id
At 15:33 -0800 1/29/02, James Montebello wrote: This result will be invalid when the a new row is written to the table, whether it's part of a transaction or not, no matter what the table type. I said as much, but perhaps I was a bit too cryptic. Generally speaking, getting this value reliably while other work is going on is only possible if you lock the table, do the select, do whatever you're going do to with that value, and unlock the table. As soon as you do the unlock, the value is useless. Getting the value reliably is *exactly* what LAST_INSERT_ID() is for. No transaction necessary. james montebello On Tue, 29 Jan 2002, Christopher Thompson wrote: Not _necessarily_ true, surely, if there's another transaction active at the moment (assuming you are using a table that supports transactions)...? At 03:16 PM 1/29/2002 -0800, James Montebello wrote: SELECT MAX(id) FROM table; will get you the highest value for 'id', that +1 will be the 'next' value, until someone inserts a new row into that table. james montebello On Tue, 29 Jan 2002, Joel Wickard wrote: database,sql,query,table I need to find out what the next value will be in an auto_increment field will be. could someone help me out with the select statement? - 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
Re: next insert id
The original question that started this thread was: I need to find out what the next value will be in an auto_increment field will be. could someone help me out with the select statement? To which I replied: You can't find out what it *will be* until you actually create the record. Then you get the like this: SELECT LAST_INSERT_ID(); Check the discussion for LAST_INSERT_ID() in the MySQL manual. It will tell you the answer to your next question. :-) When I wrote that last sentence, I was anticipating that the original poster would ask, but what happens if other clients insert records and generate new AUTO_INCREMENT values? Won't that cause the value returned by LAST_INSERT_ID() to become invalid? The answer, of course, is no. LAST_INSERT_ID() is designed specifically *not* to be affected by activity performed by other clients. It's all spelled out in the manual. If you don't believe it (and from the amount of traffic that this thread has generated, apparently many people do not), here's the reference: http://www.mysql.com/doc/M/i/Miscellaneous_functions.html LAST_INSERT_ID() is your friend. Get to know it, it won't let you down. :-) - 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
Re: next insert id
Other people do know how to read manuals, Paul. If the original poster made any assumptions about the NEXT value in the auto increment field based on the value of LAST_INSERT_ID, that assumed value will be invalid the second another row is written to the table. If I did this: INSERT INTO foo SET bar='bar'; SELECT LAST_INSERT_ID() - 1, so next_value = 2 ...someone else writes one row... INSERT INTO foo SET bar='baz'; SELECT LAST_INSERT_ID() - 3, hey! is not the same as next_value! Same as if I used MAX()... LAST_INSERT_ID is simply the last value YOU inserted. It has no reliable relationship with any values in the table itself, and you can make no valid assumptions about values in the table unless you hold the lock for that table. And you CAN find out what it WILL be (contrary to your statement below), if you lock the table first, and use MAX(). Now, as for whether or not it's USEFUL to know the next value, that's another matter. If what you want is to do something like this: INSERT some row w/o setting the auto_increment column SELECT the auto_increment ID for the row I just wrote Then LAST_INSERT_ID is, indeed, the right way to do that. No locks required. james montebello On Tue, 29 Jan 2002, Paul DuBois wrote: The original question that started this thread was: I need to find out what the next value will be in an auto_increment field will be. could someone help me out with the select statement? To which I replied: You can't find out what it *will be* until you actually create the record. Then you get the like this: SELECT LAST_INSERT_ID(); Check the discussion for LAST_INSERT_ID() in the MySQL manual. It will tell you the answer to your next question. :-) When I wrote that last sentence, I was anticipating that the original poster would ask, but what happens if other clients insert records and generate new AUTO_INCREMENT values? Won't that cause the value returned by LAST_INSERT_ID() to become invalid? The answer, of course, is no. LAST_INSERT_ID() is designed specifically *not* to be affected by activity performed by other clients. It's all spelled out in the manual. If you don't believe it (and from the amount of traffic that this thread has generated, apparently many people do not), here's the reference: http://www.mysql.com/doc/M/i/Miscellaneous_functions.html LAST_INSERT_ID() is your friend. Get to know it, it won't let you down. :-) - 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 - 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
Re: next insert id
At 17:38 -0800 1/29/02, James Montebello wrote: Other people do know how to read manuals, Paul. If the original poster made any assumptions about the NEXT value in the auto increment field based on the value of LAST_INSERT_ID, that assumed value will be invalid the second another row is written to the table. Which is why I said that you can't find out the value until you create it. Trying to get it ahead of time is a waste of time. And as you point out below, whether that is even useful is open to debate. If I did this: INSERT INTO foo SET bar='bar'; SELECT LAST_INSERT_ID() - 1, so next_value = 2 ...someone else writes one row... INSERT INTO foo SET bar='baz'; SELECT LAST_INSERT_ID() - 3, hey! is not the same as next_value! Same as if I used MAX()... LAST_INSERT_ID is simply the last value YOU inserted. I don't think that I claimed otherwise. It has no reliable relationship with any values in the table itself, and you can make no valid assumptions about values in the table unless you hold the lock for that table. And you CAN find out what it WILL be (contrary to your statement below), if you lock the table first, and use MAX(). Actually ... That's not true, unless you can guarantee that the next AUTO_INCREMENT value will be the maximum value currently in the table + 1. Which is not, in general, true. It will be true for ISAM tables, but can easily be false for MyISAM tables, for example. (Specifically, it's true for table types that reuse deleted values at the top of the sequence. For table types that guarantee a strictly monotonic sequence, MAX() bears no relationship to the next AUTO_INCREMENT value.) Now, as for whether or not it's USEFUL to know the next value, that's another matter. If what you want is to do something like this: INSERT some row w/o setting the auto_increment column SELECT the auto_increment ID for the row I just wrote Then LAST_INSERT_ID is, indeed, the right way to do that. No locks required. Right. My (apparently quite evident) despair at the course of this thread is not over the finer points of how LAST_INSERT_ID() behaves, but over the extent to which its basic essential nature seems to go unnoticed. That's too bad, because it saves many headaches and lots of messing around that would otherwise be necessary. - 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
