Hi. On Fri 2003-01-31 at 06:48:45 -0800, [EMAIL PROTECTED] wrote: > Hi, I have been using autoincrement fields for some > time but was wondering how does it work in some > "special" situations.
Most of this depends on which MySQL version you use and which table type, unfortunately. OTOH, for the common case all just work fine and relying too much on the edge cases is not a good idea anyhow. Some relevant manual pages are: 1: http://www.mysql.com/doc/en/example-AUTO_INCREMENT.html 2: http://www.mysql.com/doc/en/SEC471.html 3: http://www.mysql.com/doc/en/CREATE_TABLE.html 4: http://www.mysql.com/doc/en/ALTER_TABLE.html > Ex. suppose I have an autoincrement field called num > and the last one has value of 10. > > I delete the last on and insert a new one. Which > value will it have ? 10 or 11 With the ISAM and BDB table handler, you will get 10 [see 3]. With MyISAM 11 [3]. With InnoDB 11, except if you restart MySQL in-between, then you will get 10 [2,3]. The common part is: You will get a new number, which is at least greater than the current maximum value, but numbers may be reused. If you ignore older table handlers (ISAM,BDB) and ignore server restarts (my last was about 100 days before): You will get a new, never-used-before number that is greater than the current maximum value with MyISAM or InnoDB. > I read that if I issue a query delete * from table and > delete from table I have diferent results (in regards > to the auto increment field). Is this correct ? The difference is whether you specify a WHERE clause (delete * is not correct syntax, AFAIK), i.e. with DELETE FROM table_name (without a WHERE) the sequence starts over (for any table handler) if you are in auto-commit mode. [3] With a WHERE clause, nothing special happens. You can also set the value explicitly by using [see 4] ALTER TABLE table_name AUTO_INCREMENT = # HTH, Benjamin. -- [EMAIL PROTECTED] --------------------------------------------------------------------- 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
