Paul, >>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. Oops. Sorry for calling the brand new MySQL Cookbook an "old book"! :/ >> TRUNCATE TABLE tbl >>This will in fact do a DROP/CREATE, thus resetting the AUTO_INCREMENT >>counter etc. >Not always! >Try this script: >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; You're right, and my 4.0.7 behaves the same way. Is TRUNCATE supposed to behave this way? (The manual doesn't say anything about TRUNCATE and AUTO_INCREMENT.) >>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: [snip] > Auto_increment: 1 Oh, well ... There's a slight contradiction in the manual, but you're right again, anyway: "When you insert a value of NULL (recommended) or 0 into an AUTO_INCREMENT column, the column is set to value+1, where value is the largest value for the column currently in the table. AUTO_INCREMENT sequences begin with 1." So, if initially value=1, then the first auto_increment value would be 1+1. Anyway, we know what the manual wants to tell us, so sorry for being precocious. 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: "Paul DuBois" <[EMAIL PROTECTED]> To: "Stefan Hinz, iConnect (Berlin)" <[EMAIL PROTECTED]>; "Octavian Rasnita" <[EMAIL PROTECTED]>; "MySQL" <[EMAIL PROTECTED]> Sent: Thursday, January 09, 2003 1:49 AM Subject: Re: Resetting the auto_increment to start from 1 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
