SitePoint Sponsor

User Tag List

Results 1 to 16 of 16

Thread: auto_increment

  1. #1
    SitePoint Evangelist jkh1978's Avatar
    Join Date
    May 2001
    Location
    Northern Virginia
    Posts
    445
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    auto_increment

    I am throwing test data into my database with phpmyadmin to see how my webpage reacts.

    Now I know the id number doesnt really matter, but I have gone back and deleted the test data, and I would like to put the auto_increment number back down too. Is there a way to "reset" it?

  2. #2
    Making a better wheel silver trophy DR_LaRRY_PEpPeR's Avatar
    Join Date
    Jul 2001
    Location
    Missouri
    Posts
    3,428
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    DELETE FROM table will delete everything from the table, if there's anything, and reset the AUTO_INCREMENT number.
    - Matt ** Ignore old signature for now... **
    Dr.BB - Highly optimized to be 2-3x faster than the "Big 3."
    "Do not enclose numeric values in quotes -- that is very non-standard and will only work on MySQL." - MattR

  3. #3
    SitePoint Member
    Join Date
    Oct 2001
    Location
    Toronto, Canada
    Posts
    18
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    As far as I know, theres no real way around that. (If you think about it, its much better this way)

  4. #4
    SitePoint Wizard Defender1's Avatar
    Join Date
    Apr 2001
    Location
    My Computer
    Posts
    2,808
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    i believe if you manually insert and id number, it'll start counting from there.
    Defender's Designs
    I'm Getting Married!

    Not-so-patiently awaiting Harry Potter Book 7 *sigh*

  5. #5
    Making a better wheel silver trophy DR_LaRRY_PEpPeR's Avatar
    Join Date
    Jul 2001
    Location
    Missouri
    Posts
    3,428
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by hyfen
    As far as I know, theres no real way around that. (If you think about it, its much better this way)
    yeah there is - the way i just said. try it.


    Originally posted by Defender1
    i believe if you manually insert and id number, it'll start counting from there.
    nope. in MySQL 3.23 the next number will be one more than the highest that was *EVER* in the table, not one more than *IS* in the table.

  6. #6
    SitePoint Wizard Defender1's Avatar
    Join Date
    Apr 2001
    Location
    My Computer
    Posts
    2,808
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    oops, guess i wasn't clear.
    i meant when you insert your first value
    Defender's Designs
    I'm Getting Married!

    Not-so-patiently awaiting Harry Potter Book 7 *sigh*

  7. #7
    SitePoint Evangelist jkh1978's Avatar
    Join Date
    May 2001
    Location
    Northern Virginia
    Posts
    445
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The Doctor understood what I want... I want to reset after I already have data in it.

    Thanks for the replies.

  8. #8
    SitePoint Addict rwar's Avatar
    Join Date
    Sep 2001
    Location
    PF / RS / BR
    Posts
    207
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Is there any way to "edit" the mysql fields (to stop the id auto incrementing, and continue from, say, the last id)?

    I'm asking this 'cause the situation of delete a field and I want edit it, in the future, with the id deleted...
    php? mysql? apache? That's it.

  9. #9
    SitePoint Evangelist jkh1978's Avatar
    Join Date
    May 2001
    Location
    Northern Virginia
    Posts
    445
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thats what I am looking for too

  10. #10
    SitePoint Addict rwar's Avatar
    Join Date
    Sep 2001
    Location
    PF / RS / BR
    Posts
    207
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sorry. I didn't understand before...
    php? mysql? apache? That's it.

  11. #11
    SitePoint Wizard Defender1's Avatar
    Join Date
    Apr 2001
    Location
    My Computer
    Posts
    2,808
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    i tend to just delete the column and reinsert it when i want to reset an auto-increment.
    Defender's Designs
    I'm Getting Married!

    Not-so-patiently awaiting Harry Potter Book 7 *sigh*

  12. #12
    SitePoint Addict rwar's Avatar
    Join Date
    Sep 2001
    Location
    PF / RS / BR
    Posts
    207
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Won't appear some errors, warnings...?
    php? mysql? apache? That's it.

  13. #13
    SitePoint Wizard Defender1's Avatar
    Join Date
    Apr 2001
    Location
    My Computer
    Posts
    2,808
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    um... only if you have data in it.
    in which case why would you be resesting the id as you'd probably start duplicating data.

    backup your data and structure, then delete it, then restore it with the backup file.
    Defender's Designs
    I'm Getting Married!

    Not-so-patiently awaiting Harry Potter Book 7 *sigh*

  14. #14
    SitePoint Member
    Join Date
    Oct 2001
    Location
    Toronto, Canada
    Posts
    18
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    If you want to turn off the autoincrement, heres the sql syntax: ALTER TABLE [tablename] CHANGE [oldfieldname] [newfieldname] TINYINT (2) DEFAULT '0' not null

    Then insert the info. After that you can turn the field's autoincrement on again with: ALTER TABLE style CHANGE [oldfieldname] [newfieldname] TINYINT (2) DEFAULT '0' not null AUTO_INCREMENT

    Make sure to pass on an id number in when the autoincrement is off. (Get the field with the highest index number, increment it by one, and use it for the new index number)

  15. #15
    SitePoint Enthusiast Jujubee's Avatar
    Join Date
    Mar 2001
    Location
    Canada
    Posts
    98
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I just wasted half hour on this so here's what I did.

    My Primary Key was a TINYINT and I was only at 50 when by mistake I inserted a row at id 255. That's the limit of TINYINT and even if I removed that row, all future INSERTs would start at 255.

    So to get it to go back to 51, this is what I did via PHPMyAdmin:

    1) this may be obvious, but delete rows that are higher than where you want to start (e.g. if you want the INSERTS to start up at 51, make sure 50 is the highest ID in your table)

    2) Remove AUTO_INCREMENT from the field that you're having trouble with (usually the PRIMARY KEY)

    3) DROP any index that's on the field

    4) Use the "COPY TABLE TO" function to copy the table to another table (give it a temp name (TABLE_NEW))

    5) rename the original table to a temp name (TABLE_OLD)

    6) rename the new table (TABLE_NEW) to whatever your OLD table was called

    7) DROP TABLE_OLD

    8) add the PRIMARY KEY & AUTO-INCREMENT back to the field that was giving you trouble

    Now when you insert, it will be just continue where it should. Phew.

    -----
    So:
    - remove AUTO_INCREMENT & INDEX/PRIMARY KEY
    - copy the TABLE
    - DROP old table
    - rename new table
    - add AUTO_INCREMENT & INDEX/PRIMARY KEY back
    ------

    Hope that made sense!


  16. #16
    SitePoint Wizard Defender1's Avatar
    Join Date
    Apr 2001
    Location
    My Computer
    Posts
    2,808
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    or you could have:

    1.) View a dump of the table, data and structure, save/copy it.
    2.) Drop that table
    3.) Use the file you created from the dump to recreate the table

    it'll start counting from the highest id in the data you insert.
    Defender's Designs
    I'm Getting Married!

    Not-so-patiently awaiting Harry Potter Book 7 *sigh*


Bookmarks

Posting Permissions

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