Direct quote from MySQL by Paul DuBois
"MySQL 3.23 introduced the new AUTO_INCREMENT behaviors of not reusing
sequence numbers and allowing you to specify an initial sequence number in
the CREATE TABLE statement.  These behaviors are undone if you delete all
records in the table using a DELETE statement of the following form:

        DELETE FROM tablename

In this case, the sequence starts over from 1 rather than continuing in
strictly increasing order.  The sequence starts over even if your CREATE
TABLE statement specifies an initial sequence number explicitly.  This
occurs due to the way MySQL optimizes DELETE statements that empty a table
entirely: It re-creates the data and index files from scratch rather than
deleting each record, and that causes all sequence number information to be
lost.  If you want to delete all records but preserve the sequence
information, you can suppress the optimization and force MySQL to perform a
row-by-row delete operation instead, like this:

        DELETE FROM tablename WHERE 1 > 0"

Things may have changed since this book was published.

"Claudiu" <[EMAIL PROTECTED]> wrote in message
> I have a mysql table which contains an id field which is set to
> auto_increment.
> I have a script which empties this database at regular intervals.
> My problem is that the auto_increment field won't reset to 0 after
> deleting all of tha data, but continue incrementing from where it left.
> How can i solve this problem..
> I want id to reset to 0 when i delete the contents of the table.
> Can someone help?

PHP General Mailing List (
To unsubscribe, visit:

Reply via email to