CODEWITHSUNDEEP
mysqlinsertauto-incrementpartitioning
Griff
Griff

Reputation: 1747

Mysql Auto Increment increasing by 2 and 1?

If I create a table with the following syntax,

CREATE TABLE IF NOT EXISTS `hashes` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`hash` binary(20) NOT NULL,
PRIMARY KEY (`id`,`hash`),
UNIQUE KEY (`hash`)
) ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE = 4 AUTO_INCREMENT=1
PARTITION BY KEY(`hash`)
PARTITIONS 10;

And insert queries with the following syntax

INSERT INTO hashes (hash) VALUES ($value) ON DUPLICATE KEY UPDATE hash = hash

Then the auto increment column works as expected both if the row is inserted or updated.

Although creating the table without the partition like below and inserting with the query above the auto increment value will increase by 1 on every update or insert causing the A_I column to be all over place as the query could do 10 updates and then 1 insert causing the column value to jump 10 places.

CREATE TABLE IF NOT EXISTS `hashes` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`hash` binary(20) NOT NULL,
PRIMARY KEY (`id`,`hash`),
UNIQUE KEY (`hash`)
) ENGINE=InnoDB AUTO_INCREMENT=1;

I understand why the value increases on an update with INNO_DB but I do not understand why it doesn't when the table is partitioned?

Upvotes: 0

Views: 1057

Answers (1)

jcho360
jcho360

Reputation: 3759

you cannot change that, but you can try something like this:

mysql> set @a:= (select max(id) + 2 from hashes); 

mysql> insert into hashes (id) values ($value) on duplicate key update id=@a;

NOTE: the partitions change a little bit after mysql 5.6, which version do you have?

Upvotes: 0

Related Questions