List:General Discussion« Previous MessageNext Message »
From:mos Date:October 4 2011 3:39am
Subject:Re: How MyISAM handle auto_increment
View as plain text  
At 06:21 PM 10/3/2011, Angela liu wrote:
>Thanks, what about if mysqld restart, does auto_increment gets reset  ?

No. The next auto increment value stays with the table. As another 
person already stated, you should never manually change the auto 
increment value on a table that already has rows in it.  MySQL always 
handles the incrementing of an AutoInc field.


>I saw this happened to Innodb, if table is empty and server restart, 
>auto_incremnet gets reset to 0

This shouldn't happen unless MySQL crashes.  Perhaps you are 
confusing the next Auto Increment value with the Last_Insert_Id() 
(stored in server memory) which has a value only AFTER the user has 
inserted a row. There can be a hundred connections (each with their 
own session) adding rows to the same table at the same time. 
Last_Insert_Id() will retrieve the autoinc value of the record that 
was just inserted for YOUR session.  You won't get someone else's 
AutoInc value, only the autoinc value for the record that YOU just 
inserted.  So if you insert a record, wait 5 minutes, then execute a 
"Select Last_Insert_Id()", you will get the correct autoinc value 
that was used when YOUR record was inserted, even though another 
hundred records were added while you waited to execute the Select 
statement. You can never really reliably know what the autoinc value 
will be for the record you are about to insert until AFTER the record 
has been inserted and you execute Last_Insert_Id() to retrieve this 
autoinc value.

  I hope this clears it up.

Mike


>
>From: mos <mos99@stripped>
>To: mysql@stripped
>Sent: Monday, October 3, 2011 3:01 PM
>Subject: Re: How MyISAM handle auto_increment
>
>At 04:46 PM 10/3/2011, you wrote:
> > Hi, Folks: I have questions regarding how MyISAM handles 
> auto_increment clolumn? 1. is there a auto_increment counter for 
> MyISAM to assign a new value to auto_increment columns?
>
>Yes
>
> >  2. if MyISAM has the counter, is the counter stored in memory or 
> disk? Thnaks
>
>It is stored with the table definition.  It is only reset to 0 when 
>the table is (re)created. You can get the last AutoInc for the 
>record that was just added by "Select Last_Insert_Id()". See 
>http://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html
>
>Mike
>
>
>
>-- MySQL General Mailing List
>For list archives: http://lists.mysql.com/mysql
>To unsubscribe:    http://lists.mysql.com/mysql?unsub=1
>
>

Thread
How MyISAM handle auto_incrementAngela liu3 Oct
  • Re: How MyISAM handle auto_incrementReindl Harald3 Oct
    • Re: How MyISAM handle auto_incrementLucio Chiappetti5 Oct
  • Re: How MyISAM handle auto_incrementmos4 Oct
  • Re: How MyISAM handle auto_incrementAngela liu4 Oct
    • Re: How MyISAM handle auto_incrementmos4 Oct
RE: How MyISAM handle auto_incrementLucio Chiappetti6 Oct
  • RE: How MyISAM handle auto_incrementJerry Schwartz7 Oct