At 14:48 +0300 4/30/04, Egor Egorov wrote:
Leandro Melo <[EMAIL PROTECTED]> wrote:
If it`s a InnoDB table :-? my case
If you want to start auto_increment sequence from the beginning, you
must recreate InnoDB table.
To add to this, I'll mention something else that is relevant in the
context of InnoDB tables. It's not just record deletes that can
cause a sequence number to become "unused". If you generate an
AUTO_INCREMENT value within a transaction but roll back that transaction,
the AUTO_INCREMENT value also becomes "unused". For example, if you have
transactions A, B, and C that each generate an AUTO_INCREMENT value of
1, 2, and 3 in turn, but B rolls back, the values stored in the table
are 1 and 3. There is now a gap in the sequence.
I'm going to re-emphasize that there is almost never any reason to
resequence an AUTO_INCREMENT column. I know that people like to
keep sequence numbering nice and neat, but having gaps and unused
numbers isn't going to make applications work any differently,
and MySQL doesn't care one way or the other.
In those rare cases when you *must* have an unbroken sequence,
AUTO_INCREMENT may be the wrong approach anyway.
--- Paul DuBois <[EMAIL PROTECTED]> escreveu: > At 12:27
-0300 4/29/04, Leandro Melo wrote:
>Hi,
>i got a table wich its pk is an auto_increment
field.
>I have 10 elements in this table, wich makes the
pk_id
>field = 10.
>I inserted incorrectly anoter row in this table
(the
>11th) and imediately deleted it. Although, i'd like
>that the next time i insert a row in this table,
it's
>index be still 11 (not 12 as mysql would do by
>default).
>Is there any fast command i can make to correct the
>table situation?
If it's a MyISAM table, yes, although you might ask
yourself
whether there's really any need to do so. (Answer:
nearly always
not.)
This statement will cause the next AUTO_INCREMENT
value generated
to be one more than the current maximum value in the
column:
>> ALTER TABLE tbl_name AUTO_INCREMENT = 1;
--
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
