Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.
Moderator: General Moderators
raghavan20
DevNet Resident
Posts: 1451 Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:
Post
by raghavan20 » Thu Jan 12, 2006 6:56 am
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.
JayBird
Admin
Posts: 4524 Joined: Wed Aug 13, 2003 7:02 am
Location: York, UK
Contact:
Post
by JayBird » Thu Jan 12, 2006 6:57 am
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?
Weirdan
Moderator
Posts: 5978 Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine
Post
by Weirdan » Thu Jan 12, 2006 7:03 am
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.
raghavan20
DevNet Resident
Posts: 1451 Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:
Post
by raghavan20 » Thu Jan 12, 2006 7:05 am
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?
JayBird
Admin
Posts: 4524 Joined: Wed Aug 13, 2003 7:02 am
Location: York, UK
Contact:
Post
by JayBird » Thu Jan 12, 2006 8:14 am
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
feyd
Neighborhood Spidermoddy
Posts: 31559 Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA
Post
by feyd » Thu Jan 12, 2006 8:27 am
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.