hi,
i set up a table, and set "id" column as auto_increment. if there's been 8 records inside. if i delete the last one, the following coming row's id will be 9, not 8. can any one explain why and how to solve this problem?
thank you!

Hi!

Ain't an error, is a feature. There are no way how a deletion could (or should!)affect the Auto_increment counter. So some caps are probable!
Not MySQL specific feature, other databases has the same.

Cheers, Bill

The AUTO_INCREMENT column always increases. However, you can specify an AUTO_INCREMENT column as secondary column in a multiple-column index to "divide" the AUTO_INCREMENT into several groups.

Example:

NOTE THAT id2 IS THE AUTO_INCREMENT COLUMN here, and not id!

CREATE TABLE `testAI` (
`id` int(11) NOT NULL default '0',
`id2` int(11) NOT NULL auto_increment,
PRIMARY KEY (`id`,`id2`)
);

mysql> INSERT INTO testAI(id) VALUES(1),(1),(2),(2),(3),(1);
Query OK, 6 rows affected (0.00 sec)
Records: 6 Duplicates: 0 Warnings: 0

mysql> SELECT * FROM testAI;
+----+-----+
| id | id2 |
+----+-----+
| 1 | 1 |
| 1 | 2 |
| 1 | 3 |
| 2 | 1 |
| 2 | 2 |
| 3 | 1 |
+----+-----+
6 rows in set (0.00 sec)

There are now three groups of auto_increment values.

mysql> DELETE FROM testAI WHERE id2>1;
Query OK, 3 rows affected (0.00 sec)

mysql> SELECT * FROM testAI;
+----+-----+
| id | id2 |
+----+-----+
| 1 | 1 |
| 2 | 1 |
| 3 | 1 |
+----+-----+
3 rows in set (0.00 sec)

Still normal.

mysql> INSERT INTO testAI(id) VALUES(1),(1),(2);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0

mysql> SELECT * FROM testAI;
+----+-----+
| id | id2 |
+----+-----+
| 1 | 1 |
| 1 | 2 |
| 1 | 3 |
| 2 | 1 |
| 2 | 2 |
| 3 | 1 |
+----+-----+
6 rows in set (0.00 sec)

There, that's how you want it! Maybe you can reorganize your data in some way (insert a dummy BOOL column maybe?) so that you can use this method.

oops, i thought system would email me if i got reply. thanks men. i have been looking around to solve it... busy bee .

Hope it helps. Just watch out for slow inserts... I have no idea how MySQL optimizes this behaviour. I would guess a separate index on the second column (the auto_increment column) in the (dummy, autoincr) column pair is needed.