At 16:52 -0600 1/7/02, Rick Emery wrote: >I don't know if this is a bug or a feature. Just an observation concerning >creating MYISAM tables with auto_increment. > >CREATE TABLE mytable (myval int auto_increment unique ) AUTO_INCREMENT=9000; >INSERT INTO mytable VALUES(NULL); >INSERT INTO mytable VALUES(NULL); > >these above INSERTS create entries as expected: myval=9000, myval=9001 > >DELETE FROM mytable; >INSERT INTO mytable VALUES(NULL); > >this INSERT creates myval=1, instead of the 9000 or 9002 that I would >expect.
This is how it's documented to work. DELETE without a WHERE resets the AUTO_INCREMENT value. > >However: >CREATE TABLE mytable (myval int auto_increment unique ) AUTO_INCREMENT=9000; >INSERT INTO mytable VALUES(NULL); >INSERT INTO mytable VALUES(NULL); >DELETE FROM mytable WHERE myval>0; >INSERT INTO mytable VALUES(NULL); > >this INSERT creates an entry in the table: myval=9001, which is preferred. > >Question: upon deleting all entries from a table with "DELETE FROM mytable", >should the next auto_incremented value be 1 or the initial auto_increment >value specified in the CREATE statement (in this case 9000)? 1. To retain the current counter, empty the table using a DELETE statement that includes a WHERE clause. > >rick > > --------------------------------------------------------------------- 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
