CODEWITHSUNDEEP
mysqlprimary-keyauto-incrementcreate-table
user3525290
user3525290

Reputation: 1619

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.

Upvotes: 2

Views: 322

Answers (3)

user3525290
user3525290

Reputation: 1619

The database admin is using replication and failed to tell me. I have never used replication so I was unaware of how incrementation worked.

Upvotes: 0

Rishikesh Chandra
Rishikesh Chandra

Reputation: 691

This should be the query

CREATE TABLE `new_table` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `display_name` varchar(50),
  PRIMARY KEY (`id`)
)

Also to set auto increment to 1 use

SET GLOBAL auto_increment_increment = 1;

Upvotes: 0

fancyPants
fancyPants

Reputation: 51928

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!

Upvotes: 5

Related Questions