Even truncate does not reset ids to one

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
raghavan20
DevNet Resident
Posts: 1451
Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:

Even truncate does not reset ids to one

Post by raghavan20 »

1. You can understand the problem from the following output. Even after truncation, the id does not reset to 1 instead it starts with 6.

Code: Select all

mysql> insert into onlyid values(NULL);
Query OK, 1 row affected (0.06 sec)

mysql> insert into onlyid values(NULL);
Query OK, 1 row affected (0.06 sec)

mysql> insert into onlyid values(NULL);
Query OK, 1 row affected (0.01 sec)

mysql> insert into onlyid values(NULL);
Query OK, 1 row affected (0.03 sec)

mysql> insert into onlyid values(NULL);
Query OK, 1 row affected (0.03 sec)

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

mysql> truncate table onlyid;
Query OK, 5 rows affected (0.03 sec)

mysql> select * from onlyid;
Empty set (0.02 sec)

mysql> insert into onlyid values(NULL);
Query OK, 1 row affected (0.01 sec)

mysql> insert into onlyid values(NULL);
Query OK, 1 row affected (0.02 sec)

mysql> select * from onlyid;
+----+
| id |
+----+
|  6 |
|  7 |
+----+
2 rows in set (0.01 sec)
2. Is it possible to say a table to increment auto generated ids by two?
Last edited by raghavan20 on Thu Jan 12, 2006 6:57 am, edited 1 time in total.
User avatar
JayBird
Admin
Posts: 4524
Joined: Wed Aug 13, 2003 7:02 am
Location: York, UK
Contact:

Post by JayBird »

If you want to reset the auto increment value, you can do this

Code: Select all

ALTER TABLE table_name AUTO_INCREMENT = 1;
..is that what you are asking?
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post by Weirdan »

Even after truncation, the id does not reset to 1 instead it starts with 6.
When used in conjunction with some table engines (specifically, InnoDB) TRUNCATE does not have the side-effect of resetting AUTO_INCREMENT counter.
User avatar
raghavan20
DevNet Resident
Posts: 1451
Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:

Post by raghavan20 »

pimptastic, that was the answer I was looking for for question 1, but I thought truncate would do that.

these guys, do they mean truncating tables would start autogenerating ids from the beginning only from mysql 5?
User avatar
JayBird
Admin
Posts: 4524
Joined: Wed Aug 13, 2003 7:02 am
Location: York, UK
Contact:

Post by JayBird »

raghavan20 wrote:pimptastic, that was the answer I was looking for for question 1, but I thought truncate would do that.

these guys, do they mean truncating tables would start autogenerating ids from the beginning only from mysql 5?
Not sure, but read Wierdan's reply
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Truncate is only supposed to empty the table. That's it. It can often be dangerous to renumber the autoincremented ids, that is likely why they do not do it.
Post Reply