From: Michael Widenius
Date: July 7 1999 9:20am
Subject: Re: MySQL 3.23 & autoincrement
List-Archive: http://lists.mysql.com/mysql/6728
Message-Id: <14211.6976.788021.527042@monty.pp.sci.fi>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

>>>>> "Paul" == Paul DuBois <paul@stripped> writes:

Paul> At 12:41 PM -0400 7/5/1999, Barry wrote:
>> Hello All,
>> 
>> I just downloaded the WinNT 3.22.24 and was looking through the new manual
>> at the Changes in 3.22.24 section. I came upon the following statement
>> about the MyISAM tables planned for 3.23.x.
>> 
>> "Internal handling of one AUTO_INCREMENT column. MyISAM will automatically
>> update this on INSERT/UPDATE. The AUTO_INCREMENT value can be reset with
>> myisamchk. This will make AUTO_INCREMENT columns faster and old numbers
>> will not be reused."
>> 
>> I am happy to see that old numbers will not be re-used, but am I
>> miss-reading this? Will an Update on an existing row modify the
>> Auto_Increment value? Using an Auto_Increment value as a static db record
>> identifier, I would not want this value to be modified once the row is
>> Inserted.

Paul> You're probably thinking of TIMESTAMP.  AUTO_INCREMENT isn't like that.

Paul> By the way, the no-reuse property doesn't seem to be implemented yet.
Paul> Try this:

Paul> USE test
Paul> DROP TABLE IF EXISTS t;
Paul> CREATE TABLE t (i INT AUTO_INCREMENT PRIMARY KEY);
Paul> INSERT t VALUES(NULL),(NULL);
Paul> SELECT * FROM t;
Paul> DELETE FROM t;
Paul> INSERT t VALUES(NULL),(NULL);
Paul> SELECT * FROM t;

Paul> I get the same result from both SELECT statements:

Paul> +---+
Paul> | i |
Paul> +---+
Paul> | 1 |
Paul> | 2 |
Paul> +---+

Hi!

Notice that 

DELETE FROM t;

Is implemented as a full drop of the table.  In this case MySQL will
forget the last used value.

Check this out:


mysql> USE test
Database changed
mysql> DROP TABLE IF EXISTS t;
Query OK, 0 rows affected (0.05 sec)

mysql> CREATE TABLE t (i INT AUTO_INCREMENT PRIMARY KEY);
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT t VALUES(NULL),(NULL);
Query OK, 2 rows affected (0.03 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM t;
+---+
| i |
+---+
| 1 |
| 2 |
+---+
2 rows in set (0.01 sec)

mysql> delete from t where t > 0;
Query OK, 2 rows affected (0.00 sec)

mysql> INSERT t VALUES(NULL),(NULL);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM t;
+---+
| i |
+---+
| 3 |
| 4 |
+---+
2 rows in set (0.00 sec)

Regards,
Monty