From: Date: July 5 1999 7:57pm
Subject: Re: MySQL 3.23 & autoincrement
List-Archive: http://lists.mysql.org/mysql/6577
Message-Id: <v04020a2db3a6a1bb3c3a@[192.168.0.6]>
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"

At 7:33 PM +0200 7/5/1999, Martin Ramsch wrote:
>On Mo, 1999-07-05 12:13:35 -0500, Paul DuBois wrote:
>> By the way, the no-reuse property doesn't seem to be implemented yet.
>> Try this:
>>
>> USE test
>> DROP TABLE IF EXISTS t;
>> CREATE TABLE t (i INT AUTO_INCREMENT PRIMARY KEY);
>> INSERT t VALUES(NULL),(NULL);
>> SELECT * FROM t;
>> DELETE FROM t;
>> INSERT t VALUES(NULL),(NULL);
>> SELECT * FROM t;
>>
>> I get the same result from both SELECT statements:
>
>Please, would you try "DELETE from t where i>0" instead of "DELETE
>from t"?  If I remember correctly the latter actually drops and
>recreates the whole table instead of just deleting the records.

Ah, that makes a difference:

+---+
| i |
+---+
| 1 |
| 2 |
+---+
+---+
| i |
+---+
| 3 |
| 4 |
+---+

However, DELETE FROM t doesn't actually drop and recreate the
whole table; it recreates the data and index files, but leaves the
*.frm file alone.  I'm not sure where the max AUTO_INCREMENT value
is being stored, but from a logical standpoint, I would expect
the value to keep on incrementing whether or not I used WHERE 1>0 to
delete records.

Monty, any comment?

--
Paul DuBois, paul@stripped
Northern League Chronicles: http://www.snake.net/nl/