List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:January 9 2003 12:49am
Subject:Re: Resetting the auto_increment to start from 1
View as plain text  
At 20:39 +0100 1/8/03, Stefan Hinz, iConnect (Berlin) wrote:
>Octavian,
>
>>  I've read the following in a MySQL book:
>
>It's a rather old book, which deals with MySQL 3.23, and not with MySQL
>4.x.

Actually, he's talking about MySQL Cookbook (p549).  Which is a new book,
which is why I said *may* reset the counter rather than *will*
reset the counter as was true in older versions of MySQL.


>
>>   DELETE FROM tbl_name WHERE 1 > 0;
>
>In MySQL 3.23, this was a workaround to force the server to delete a
>table row by row. By default, 3.23 would on DELETE FROM tbl just do a
>DROP TABLE + CREATE TABLE, because this was faster in most cases than
>deleting the rows. This behaviour wasn't ANSI SQL compliant, though.
>
>MySQL 4.x does a DELETE FROM tbl with or without WHERE clause ANSI
>compliant. This means, it will always delete the rows, not DROP/CREATE
>the table. To do the latter, use
>
>  TRUNCATE TABLE tbl
>
>This will in fact do a DROP/CREATE, thus resetting the AUTO_INCREMENT
>counter etc.

Not always!

Try this script:

DROP TABLE IF EXISTS t;
CREATE TABLE t (i INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY)
TYPE = INNODB;
INSERT INTO t SET i = NULL;
INSERT INTO t SET i = NULL;
INSERT INTO t SET i = NULL;
SELECT * FROM t;
TRUNCATE TABLE t;
INSERT INTO t SET i = NULL;
SELECT * FROM t;

See if you get the output I do (MySQL 4.0.8):

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

What's the solution?  Do this:

ALTER TABLE t AUTO_INCREMENT = 1;

>
>>  Well, I've tried that sql statement, but the auto_increment point of
>start
>>  was not reset to 1.
>
>Actually, the counter is reset to 0, not 1. The first inserted value
>then is auto-incremented, and thus becomes 1.

Sure about that?  Create a new table and try SHOW TABLE STATUS LIKE 't'
and you'll get:

mysql> show table status like 't'\G
*************************** 1. row ***************************
            Name: t
            Type: InnoDB
      Row_format: Fixed
            Rows: 0
  Avg_row_length: 0
     Data_length: 16384
Max_data_length: NULL
    Index_length: 0
       Data_free: 0
  Auto_increment: 1
     Create_time: NULL
     Update_time: NULL
      Check_time: NULL
  Create_options:
         Comment: InnoDB free: 14336 kB


>
>Regards,
>--
>   Stefan Hinz <hinz@stripped>
>   Geschäftsführer / CEO iConnect GmbH <http://iConnect.de>
>   Heesestr. 6, 12169 Berlin (Germany)
>   Tel: +49 30 7970948-0  Fax: +49 30 7970948-3
>
>----- Original Message -----
>From: "Octavian Rasnita" <orasnita@stripped>
>To: "MySQL" <mysql@stripped>
>Sent: Wednesday, January 08, 2003 8:33 AM
>Subject: Resetting the auto_increment to start from 1
>
>
>>  Hi all,
>>
>>  I've read the following in a MySQL book:
>>
>>    A special case of record deletion occurs when you clear out a table
>>  entirely using a DELETE with no WHERE clause:
>>   DELETE FROM tbl_name;
>>    In this case, the sequence counter may be reset to 1, even for table
>types
>>  for which values normally are not reused (MyISAM and InnoDB). For
>those
>>  types, if you wish to delete all the records while maintaining the
>current
>>  sequence value, tell MySQL to perform a record-at-a-time delete by
>including
>>  a WHERE clause that specifies some trivially true condition:
>>   DELETE FROM tbl_name WHERE 1 > 0;
>>
>>  ---
>>
>>  Well, I've tried that sql statement, but the auto_increment point of
>start
>>  was not reset to 1.
>>  I use MySQL 4.05 under Windows 2000.
>>
>>  Thanks.
>>
>>  Teddy,
>>  Teddy's Center: http://teddy.fcc.ro/
>  > Email: orasnita@stripped

Thread
Resetting the auto_increment to start from 1Octavian Rasnita8 Jan
  • Re: Resetting the auto_increment to start from 1Paul DuBois8 Jan
  • Re: Resetting the auto_increment to start from 1iConnect \(Berlin\) Stefan Hinz8 Jan
    • Re: Resetting the auto_increment to start from 1Paul DuBois9 Jan
      • What's returned from $sth->execute(); ?Jeff Snoxell10 Jan
        • Re: What's returned from $sth->execute(); ?Paul DuBois10 Jan
  • Re: Resetting the auto_increment to start from 1Octavian Rasnita9 Jan
  • Re: Resetting the auto_increment to start from 1iConnect \(Berlin\) Stefan Hinz9 Jan
Re: What's returned from $sth->execute(); ?Rich Allen11 Jan