List:General Discussion« Previous MessageNext Message »
From:Michael Widenius Date:July 7 1999 9:20am
Subject:Re: MySQL 3.23 & autoincrement
View as plain text  
>>>>> "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
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