List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:July 5 1999 5:57pm
Subject:Re: MySQL 3.23 & autoincrement
View as plain text  
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/
Thread
SQL join questionWillem Bison11 Mar
  • Re: SQL join questionChristian Mack11 Mar
  • Re: MySQL 3.23 alpha released (problems/fixes)Paul DuBois5 Jul
    • MySQL 3.23 & autoincrementBarry5 Jul
      • Re: MySQL 3.23 & autoincrementPaul DuBois5 Jul
        • Re: MySQL 3.23 & autoincrementMartin Ramsch5 Jul
          • Re: MySQL 3.23 & autoincrementPaul DuBois5 Jul
            • Re: MySQL 3.23 & autoincrementMichael Widenius7 Jul
        • Re: MySQL 3.23 & autoincrementDick Griffin5 Jul
        • Re: MySQL 3.23 & autoincrementMichael Widenius7 Jul
      • MySQL 3.23 & autoincrementMichael Widenius7 Jul