Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2004
    Posts
    2

    Unanswered: auto_increment problem!!!

    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!

  2. #2
    Join Date
    Jan 2004
    Location
    Europe, Finland, Helsinki
    Posts
    64

    auto_increment feature

    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

  3. #3
    Join Date
    Apr 2004
    Location
    Europe->Sweden->Stockholm
    Posts
    71
    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.

  4. #4
    Join Date
    Apr 2004
    Posts
    2

    thanks

    oops, i thought system would email me if i got reply. thanks men. i have been looking around to solve it... busy bee .
    Last edited by coder_sjj; 05-04-04 at 11:16.

  5. #5
    Join Date
    Apr 2004
    Location
    Europe->Sweden->Stockholm
    Posts
    71
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •