List:General Discussion« Previous MessageNext Message »
From:x Date:January 9 2008 6:25pm
Subject:Re: why in procedure truncate table do not reset auto_increment?
View as plain text  
thanks

may you point out which chapter says?
 From manual I get the following answer agaist to my result(my server 
version 5.0.45), 

For |InnoDB| before version 5.0.3, |TRUNCATE TABLE| is mapped to 
|DELETE|, so there is no difference. Starting with MySQL 5.0.3, fast 
|TRUNCATE TABLE| is available. However, the operation is still mapped to 
|DELETE| if there are foreign key constraints that reference the table. 
(When fast truncate is used, it resets any |AUTO_INCREMENT| counter. 
 From MySQL 5.0.13 on, the |AUTO_INCREMENT| counter is reset by 
|TRUNCATE TABLE|, regardless of whether there is a foreign key constraint.)

The table handler does not remember the last used |AUTO_INCREMENT| 
value, but starts counting from the beginning. This is true even for 
|MyISAM| and |InnoDB|, which normally do not reuse sequence values.

See:
http://dev.mysql.com/doc/refman/5.0/en/truncate.html 
http://dev.mysql.com/doc/refman/5.1/en/truncate.html

Sebastian Mendel 写道:
> 过客 schrieb:
>   
>> [...] why in procedure TRUNCATE table
>> demo do not reset auto_increment?
>>     
>
> is clearly written in the documentation, just read ...
>
> in short:
>
> auto_increment is used for primary key, primary keys could be referenced
> from another table, setting auto_increment back to 0 could lead to using a
> primary key formerly used by another datarow and still referenced by another
> table - could lead to data inconsistency.
>
>   


Thread
why in procedure truncate table do not reset auto_increment?过客7 Jan
  • Re: why in procedure truncate table do not reset auto_increment?Sebastian Mendel9 Jan
    • Re: why in procedure truncate table do not reset auto_increment?x9 Jan
      • Re: why in procedure truncate table do not reset auto_increment?Sebastian Mendel10 Jan
  • Re: why in procedure truncate table do not reset auto_increment?Martijn Tonies9 Jan
    • Re: why in procedure truncate table do not reset auto_increment?Sebastian Mendel9 Jan
  • Re: why in procedure truncate table do not reset auto_increment?Martijn Tonies9 Jan
    • Re: why in procedure truncate table do not reset auto_increment?Sebastian Mendel9 Jan
  • Re: why in procedure truncate table do not reset auto_increment?Martijn Tonies9 Jan
    • Re: why in procedure truncate table do not reset auto_increment?Sebastian Mendel9 Jan