List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:January 8 2002 2:33am
Subject:Re: Auto_Increment and the DELETE Statement
View as plain text  
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
>
>

Thread
Auto_Increment and the DELETE StatementRick Emery7 Jan
  • Query BehaviourAlex Shi8 Jan
Re: Auto_Increment and the DELETE StatementPaul DuBois8 Jan