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 <[EMAIL PROTECTED]>
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" <[EMAIL PROTECTED]>
To: "MySQL" <[EMAIL PROTECTED]>
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: [EMAIL PROTECTED]
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
