List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:October 25 2002 4:50pm
Subject:Re: InnoDB and auto_increment fields
View as plain text  
At 11:26 -0500 10/25/02, Chris Boget wrote:
>sql, query (*sigh*, I hate this filter)
>
>I have an auto_increment key set up on my InnoDB table.
>Whenever I delete all the records, the number isn't reset.
>However, for my ISAM tables, whenever I delete all the
>records, the auto_increment number is reset.

That's how ISAM tables work.

>Is there a reason for this in InnoDB?

Yes: That's how InnoDB tables work.  (So do MyISAM tables.)

The handler for each table type handles AUTO_INCREMENT columns
slightly differently.  You're running across one of those differences.

>   Is there a way that I
>can reset the auto_increment number when all the records
>are deleted?

ALTER TABLE t AUTO_INCREMENT = 1;

>   I have a temporary table that has records
>added and deleted all the time.  I wouldn't want the record
>number to eventually reach the ceiling for the datatype of
>the field.
>
>Any insight would be greatly appreciated.
>
>Chris

Thread
ConnectionArthur25 Oct
  • Re: ConnectionJocelyn Fournier25 Oct
  • phpmyadminMitchell Wright25 Oct
    • RE: phpmyadminEd Carp25 Oct
      • Re: phpmyadmin (OT)Thomas Seifert25 Oct
        • Re: phpmyadmin (OT)Mitchell Wright25 Oct
      • RE: phpmyadminJohn Chang25 Oct
        • RE: phpmyadminJeroen Geusebroek26 Oct
        • RE: phpmyadminEd Carp26 Oct
    • Re: phpmyadmin (OT)Mark Stringham25 Oct
    • InnoDB and auto_increment fieldsChris Boget25 Oct
      • Re: InnoDB and auto_increment fieldsDyego Souza do Carmo25 Oct
      • Re: InnoDB and auto_increment fieldsPaul DuBois26 Oct
    • Re: InnoDB and auto_increment fieldsChris Boget25 Oct
      • Re: InnoDB and auto_increment fieldsPete Harlan28 Oct
    • Re: InnoDB and auto_increment fieldsChris Boget28 Oct
Re: phpmyadminJan Steinman27 Oct