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
