[support] Auto increment fields

[Usamah M. Ali]

On Tue, Apr 19, 2011 at 8:43 PM, Austin Einter <austin.einter at gmail.com> wrote:
>
> Anybody observed, the auto-increment fileds in a table is NOT always incremented by 1 with each row insert.
> But as per SQL manual it should be increased by 1.
>
> Is this behavior Ok? Or I am doing something wrong.
>
> Note: I do  not write any value to this coulmn in my INSERT statement, this coulmn values are set by system automatically.
>
> Values I have seen for auto-incrment field -> 1,2,3,4,8,9,10,18,19,20 etc...
> So in between the values incremented by more than 1.
>
>
>
> Thanks
> Austin
> --
> [ Drupal support list | http://lists.drupal.org/ ]

MySQL sets the default offset value of AUTO_INCREMENT to 1, but you
can specify any _numeric_ value between 1 and 65535[1].

The gaps in values you are getting are most probably deleted rows,
because neither there is a mechanism in MySQL to re-assign auto
incremented values nor is it necessarily to begin with. Auto
incrementing is all about generating *unique* values (usually primary
keys) not "incremented by one" values.[2]

[1]http://dev.mysql.com/doc/refman/5.5/en/replication-options-master.html#sysvar_auto_increment_offset
[2]http://dev.mysql.com/doc/refman/5.5/en/example-auto-increment.html

Regards,
Usamah