Page 1 of 1

Even truncate does not reset ids to one

Posted: Thu Jan 12, 2006 6:56 am
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?

Posted: Thu Jan 12, 2006 6:57 am
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?

Posted: Thu Jan 12, 2006 7:03 am
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.

Posted: Thu Jan 12, 2006 7:05 am
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?

Posted: Thu Jan 12, 2006 8:14 am
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

Posted: Thu Jan 12, 2006 8:27 am
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.