Search code examples
mysqlprimary-keyauto-incrementcreate-table

Weird Mysql Increment

Mysql is auto incrementing by 6. I am not sure why or how to even address the issues.

 create table new_table(tinyint unsigned primary key auto_increment, 
                        display_name varchar(50));

I've also used 

 create table new_table(int unsigned primary key auto_increment, 
                        display_name varchar(50));

I don't think I am doing anything odd.

Solution

  • This is because you have set auto_increment_increment to 6, if I understand your comment correctly.

    • read more about it here

    Note though, that this might be on purpose. Usually this is set in a master-master replication. One master is configured to use even numbers as auto_increment values, the other master uses the odd values.

    To set it back to the usual behaviour of incrementing by 1, do the following:

    SET GLOBAL auto_increment_increment = 1; /*or the SESSION value, depending on your needs*/
SET GLOBAL auto_increment_offset = 1; /*or SESSION value...*/

    Also note, that having gaps in an auto_increment column is not a big deal. Should the column overflow, use bigint instead. Auto_increment columns can also have gaps because of rolled back transactions for example. When you want a strictly sequential numbering of your rows, don't rely on auto_increment!