visitor (0 QPoints)
  • FR
  • EN
  • NL
  • DE
  • ES
315 experts, 1193 registered users, 1659 questions already answered
European Experts Exchange, the very best site for high-quality IT solutions

New Improved Search!

 


05/10/2011 1h30 : Steve Jobs is dead, the father of Apple ][ is gone, we are all orphaned.

Databases :: MySql :: Does auto_increment reset when deleting all records?
By: micamedia U.S.A.  Date: 10/09/2003 00:00:00  English  Points: 300 Status: Answered
Quality : Excellent
I have a tableA in which the primary key is an auto_increment in mysql. Lets say that the last value of the auto_increment field was 4556. If i run the sql command "delete from tableA" which deletes all teh records in the table, does my autho_increment reset back to 1, or will it continue from the last value (so the next record will have the auto_incrememt value of 4557).

If it does reset back to 1, how can i delete all the record and keep the auto_incrememt where it shoudl be?
By: alex_the_kidd Date: 10/09/2003 00:58:00 English  Type : Comment
I think that if you delete ALL records in that table the auto_increment field will be reset but if you only delete one record then that number will not be reused.
By: alex_the_kidd Date: 10/09/2003 00:59:00 English  Type : Comment
I don't think you can keep it if you delete all records.
By: alex_the_kidd Date: 10/09/2003 01:04:00 English  Type : Comment
I made a mistake:
Should be:

I think that if you delete ALL records in that table the auto_increment field will be reset and even if you only delete one record then that number will also be reused.
By: micamedia Date: 10/09/2003 01:12:00 English  Type : Comment
ok then the question is how do to NOT lose the auto_incremenet when i delete all fields.. i need to delete all fields.. and i need to keep the auto increment..

i'm using mysql and php.
By: Squibi Date: 10/09/2003 01:25:00 English  Type : Comment
Alex is wrong.

DELETE all rows from a table and auto_increment is preserved. TRUNCATE a table and it is reset. To preserve your auto_increment issue DELETE FROM mytable;

Example to prove:

mysql> create table autotest(id INT unsigned auto_increment primary key);
mysql> insert into autotest(id) VALUES(NULL);
mysql> insert into autotest(id) VALUES(NULL);
mysql> insert into autotest(id) VALUES(NULL);
mysql> select * from autotest;
+----+
| id |
+----+
| 1 |
| 2 |
| 3 |
+----+
3 rows in set (0.00 sec)

mysql> delete from autotest;
mysql> insert into autotest(id) VALUES(NULL);
mysql> insert into autotest(id) VALUES(NULL);
mysql> insert into autotest(id) VALUES(NULL);
mysql> select * from autotest;
+----+
| id |
+----+
| 4 |
| 5 |
| 6 |
+----+
3 rows in set (0.00 sec)

mysql> truncate table autotest;
mysql> insert into autotest(id) VALUES(NULL);
mysql> insert into autotest(id) VALUES(NULL);
mysql> insert into autotest(id) VALUES(NULL);
mysql> select * from autotest;
+----+
| id |
+----+
| 1 |
| 2 |
| 3 |
+----+
3 rows in set (0.00 sec)

Regards,
By: micamedia Date: 10/09/2003 02:21:00 English  Type : Comment
not true.. i just tried it myself. mysql version 3.26

inserted 4 records id was 1,2,3,4 did "delete from tbltest" .. then inserted 4 more records.. id was 1,2,3,4

So how do i preserve the id? I can get the max id by doing select max id .. but how do i issue a sql command to tell it to start from that max id once i delete all the records?
By: Squibi Date: 10/09/2003 02:25:00 English  Type : Answer
Aah, well then upgrade... ;)

If your version behaves differently, retrieve the max auto_increment, add one to it, delete all records, and do this:

ALTER TABLE tbl_name AUTO_INCREMENT = value
By: micamedia Date: 10/09/2003 03:00:00 English  Type : Comment
i believe that's the answer Squibi :) i'll give it a try and come back here and award you the points.
By: micamedia Date: 10/09/2003 04:24:00 English  Type : Comment
ok i need help.. this is not working.. i have my test table.. with the following field id, scratch, grandtotal .. id is the auto_increment field..

i put 3 records in.. the id is 1, 2, 3 ... then i "delete from tbltest" then i "alert table tbltest AUTO_INCREMENT = 4", then i insert 3 more records, then id is again 1, 2, 3 .. what's wrong.. it seem like the alert table statement isn't doing a thing.. can someone help?
By: VGR Date: 10/09/2003 04:32:00 English  Type : Comment
alter

not alert
By: VGR Date: 10/09/2003 04:34:00 English  Type : Assist
mysql> use test;
Database changed
mysql> create table totor(id integer unique auto_increment,data char(20));
Query OK, 0 rows affected (0.06 sec)

mysql> insert into totor values(0,'1');
Query OK, 1 row affected (0.00 sec)

mysql> insert into totor values(0,'2');
Query OK, 1 row affected (0.00 sec)

mysql> insert into totor values(0,'3');
Query OK, 1 row affected (0.00 sec)

mysql> select * from totor;
+----+------+
| id | data |
+----+------+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
+----+------+
3 rows in set (0.00 sec)

mysql> delete from totor;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into totor values(0,'4');
Query OK, 1 row affected (0.00 sec)

mysql> select * from totor;
+----+------+
| id | data |
+----+------+
| 1 | 4 |
+----+------+
1 row in set (0.01 sec)

mysql> delete from totor;
Query OK, 0 rows affected (0.01 sec)

mysql> alter table totor auto_increment=4;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> insert into totor values(0,'5');
Query OK, 1 row affected (0.01 sec)

mysql> select * from totor;
+----+------+
| id | data |
+----+------+
| 4 | 5 |
+----+------+
1 row in set (0.00 sec)
By: micamedia Date: 10/09/2003 04:38:00 English  Type : Comment
i tried all that.. my alter statement isn't working.. it just starts from 1 no matter what i do.. is there a bug with my version of mysql (3.23.56)
By: VGR Date: 10/09/2003 04:47:00 English  Type : Comment
nope, I've the same (it's the best until 4+ proved production-stable for me)

do a "describe tablename;"
By: Squibi Date: 10/09/2003 17:10:00 English  Type : Comment
Have you tried doing exactly what VGR did?
By: VGR Date: 10/09/2003 17:42:00 English  Type : Comment
if he has a table defined like me (thus the "describe tablename;") and follows the same instructions on the same server, I see no reason for him to have different results
By: VGR Date: 10/09/2003 17:42:00 English  Type : Comment
"my alter statement isn't working" is not acceptable
By: Squibi Date: 10/09/2003 17:50:00 English  Type : Comment
I would think so as well.
By: jpyeron Date: 13/09/2003 17:13:00 English  Type : Comment
solution:

use delete from table where truecond;

ie:
delete from AutoIncTest where 0=0;

proof:

mysql>

create table autoinc
(
id int not null auto_increment,
foobar varchar(63) null,
primary key (id)
);

insert into autoinc (foobar) values (now());
insert into autoinc (foobar) values (now());
insert into autoinc (foobar) values (now());
insert into autoinc (foobar) values (now());
insert into autoinc (foobar) values (now());
insert into autoinc (foobar) values (now());
insert into autoinc (foobar) values (now());
insert into autoinc (foobar) values (now());
insert into autoinc (foobar) values (now());
insert into autoinc (foobar) values (now());

mysql> select * from autoinc;
+----+---------------------+
| id | foobar |
+----+---------------------+
| 1 | 2003-09-13 15:04:24 |
| 2 | 2003-09-13 15:04:26 |
| 3 | 2003-09-13 15:04:26 |
| 4 | 2003-09-13 15:04:27 |
| 5 | 2003-09-13 15:04:28 |
| 6 | 2003-09-13 15:04:28 |
| 7 | 2003-09-13 15:04:28 |
| 8 | 2003-09-13 15:04:29 |
| 9 | 2003-09-13 15:04:29 |
| 10 | 2003-09-13 15:04:30 |
+----+---------------------+
10 rows in set (0.00 sec)

mysql> delete from autoinc where id<>0 or id=0;
Query OK, 10 rows affected (0.00 sec)

mysql> insert into autoinc (foobar) values (now());
Query OK, 1 row affected (0.00 sec)

mysql> select * from autoinc;
+----+---------------------+
| id | foobar |
+----+---------------------+
| 11 | 2003-09-13 15:05:13 |
+----+---------------------+
1 row in set (0.00 sec)

mysql> \s
--------------
C:\mysql\bin\mysql.exe Ver 11.16 Distrib 3.23.49, for Win95/Win98 (i32)

Connection id: 2
Current database: test
Current user: root@localhost
Server version: 3.23.49-nt
Protocol version: 10
Connection: mrlaptop via TCP/IP
Client characterset: latin1
Server characterset: latin1
TCP port: 3306
Uptime: 6 days 4 hours 59 min 30 sec

Threads: 1 Questions: 46463 Slow queries: 0 Opens: 11086 Flush tables: 1 Open tables: 1 Queries per second avg: 0.087
--------------

mysql> delete from autoinc;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from autoinc;
Empty set (0.00 sec)

mysql> insert into autoinc (foobar) values (now());
Query OK, 1 row affected (0.00 sec)

mysql> select * from autoinc;
+----+---------------------+
| id | foobar |
+----+---------------------+
| 1 | 2003-09-13 15:05:58 |
+----+---------------------+
1 row in set (0.00 sec)


from the manual: (there is another section which describes why the counter gets reset on delete w/o where clause [will post later])

7.6.11.5 How an auto-increment column works in InnoDB
After a database startup, when a user first does an insert to a table T where an auto-increment column has been defined, and the user does not provide an explicit value for the column, then InnoDB executes SELECT MAX(auto-inc-column) FROM T, and assigns that value incremented by one to the the column and the auto-increment counter of the table. We say that the auto-increment counter for table T has been initialized.

InnoDB follows the same procedure in initializing the auto-increment counter for a freshly created table.

Note that if the user specifies in an insert the value 0 to the auto-increment column, then InnoDB treats the row like the value would not have been specified.

After the auto-increment counter has been initialized, if a user inserts a row where he explicitly specifies the column value, and the value is bigger than the current counter value, then the counter is set to the specified column value. If the user does not explicitly specify a value, then InnoDB increments the counter by one and assigns its new value to the column.

The auto-increment mechanism, when assigning values from the counter, bypasses locking and transaction handling. Therefore you may also get gaps in the number sequence if you roll back transactions which have got numbers from the counter.

The behavior of auto-increment is not defined if a user gives a negative value to the column or if the value becomes bigger than the maximum integer that can be stored in the specified integer type.


By: jpyeron Date: 13/09/2003 17:37:00 English  Type : Comment
MySQL does not reset the auto_increment to 1

unless:

DELTE FROM table did not have a WHERE clause
the database server (or counter) was restarted, in which case the next val is MAX(auto_inc)+1

RTFM


this is from version 3.xx and 4.xx
STABLE branches, etc...

ALTER TABLE tbl_name AUTO_INCREMENT = value is not a correct solution. unless you have restarted the database server.


if micamedia would be so kinfd to post the database version I will test/post on his version.
By: Squibi Date: 13/09/2003 18:11:00 English  Type : Comment
"ALTER TABLE tbl_name AUTO_INCREMENT = value is not a correct solution. unless you have restarted the database server."

Oh really? Remember, InnoDB and MyISAM do not always behave the same, and the fact that you were able to quote a passage from the manual titled "How an auto-increment column works in InnoDB" Should make that obvious. In this situation asking micamedia which table handler he was using would probably have been a good idea.

And the ALTER TABLE approach does work with MyISAM, witness...

mysql> create table autotest(
-> rowID INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
-> value DATE);
Query OK, 0 rows affected (0.04 sec)

mysql> INSERT INTO autotest(value) VALUES(now());
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO autotest(value) VALUES(now());
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO autotest(value) VALUES(now());
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO autotest(value) VALUES(now());
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO autotest(value) VALUES(now());
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM autotest;
+-------+------------+
| rowID | value |
+-------+------------+
| 1 | 2003-09-13 |
| 2 | 2003-09-13 |
| 3 | 2003-09-13 |
| 4 | 2003-09-13 |
| 5 | 2003-09-13 |
+-------+------------+
5 rows in set (0.02 sec)

mysql> ALTER TABLE autotest AUTO_INCREMENT=10;
Query OK, 5 rows affected (0.04 sec)
Records: 5 Duplicates: 0 Warnings: 0

mysql> SELECT * FROM autotest;
+-------+------------+
| rowID | value |
+-------+------------+
| 1 | 2003-09-13 |
| 2 | 2003-09-13 |
| 3 | 2003-09-13 |
| 4 | 2003-09-13 |
| 5 | 2003-09-13 |
+-------+------------+
5 rows in set (0.01 sec)

mysql> INSERT INTO autotest(value) VALUES(now());
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO autotest(value) VALUES(now());
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO autotest(value) VALUES(now());
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO autotest(value) VALUES(now());
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO autotest(value) VALUES(now());
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM autotest;
+-------+------------+
| rowID | value |
+-------+------------+
| 1 | 2003-09-13 |
| 2 | 2003-09-13 |
| 3 | 2003-09-13 |
| 4 | 2003-09-13 |
| 5 | 2003-09-13 |
| 10 | 2003-09-13 |
| 11 | 2003-09-13 |
| 12 | 2003-09-13 |
| 13 | 2003-09-13 |
| 14 | 2003-09-13 |
+-------+------------+
10 rows in set (0.00 sec)
By: jpyeron Date: 13/09/2003 18:14:00 English  Type : Comment
Yes I did assume he was using the DEFAULT table type.

What table type are you using?


Here is whya I said ALTER TABLE solution was not a correct answer:

> does my autho_increment reset back to 1, or will it continue from the last value

NO, if you use a WHERE clause on the DELETE FROM table.

> If it does reset back to 1, how can i delete all the record and keep the auto_incrememt where it shoudl be?

To ** KEEP **, not reset the AUTO_INCREMENT, dont DROP the table, or use an unqulified DELETE FROM table.
also MySQL will reset the AUTO_INCREMENT on database startup to MAX(auto_inc)+1, then and only then wuold the ALTER TABLE play a role. (One can assume that a database server does not restart during code execution, otherwise one cannot assume transactions)


Jason Pyeron
Partner & Sr. Manager
Pyerotechnics Development, Inc.
By: Squibi Date: 13/09/2003 18:32:00 English  Type : Comment
Jason, please realise that I did post what I did with attached proof. A lot of auto_increment related work is handler and version dependant, so it is possible for both os us to be right.
By: jpyeron Date: 13/09/2003 18:45:00 English  Type : Comment
I am not saying that your codes do not work, I am just saying that an ALTER TABLE stattement requires the CLIENT to keep track of the AUTO_INCREMENT value. His first question was how can I delete ALL the records and KEEP the auto_increment; not how can I reset it after it has been lost, EXCEPT if it could not be kept (which it CAN)
By: Squibi Date: 13/09/2003 19:02:00 English  Type : Comment
And I also showed that it can be kept when all records are deleted, by performing a DELETE without a WHERE clause. In my version that works. In your version what you are doing works. We are both right in our own contexts. As this is a PAQ and our conversation is going nowhere fast, I am ubsubscribing from this thread.

Regards,
By: jpyeron Date: 13/09/2003 19:20:00 English  Type : Comment
micamedia, I dont know if you reviewed my post before closing this, but I would like you to answer if it did not work for you.

Do register to be able to answer

©2010 These pages are served without commercial sponsorship. (No popup ads, etc...). Bandwidth abuse increases hosting cost forcing sponsorship or shutdown. This server aggressively defends against automated copying for any reason including offline viewing, duplication, etc... Please respect this requirement and DO NOT RIP THIS SITE.
Please DO link to this page! Post to Twitter Post to Plurk Post to Yahoo Buzz Post to Delicious Post to Digg Post to Facebook Post to MySpace Post to Ping.fm Post to Reddit Post to StumbleUpon

EContact
browser fav
page generated in 508.863930 milliseconds

Why Google AdSense ads ?
compteur
 Ranking-Hits PageRank for this page