CODEWITHSUNDEEP
mysql
Olivier
Olivier

Reputation: 5688

AUTO_INCREMENT current value influenced by manual inserts

I just figured an auto_increment behavior which makes total sense, but that I would like to avoid:

A good example is worth a thousand words: 

create table test(id int not null auto_increment,somecrap text) ENGINE = INNODB;
insert into test(somecrap) values ('a');
insert into test(id,somecrap) values (100000,'b');
insert into test(somecrap) values ('c');

Here is the result :

╔════════╦══════════╗
║   id   ║ somecrap ║
╠════════╬══════════╣
║      1 ║ a        ║
║ 100000 ║ b        ║
║ 100001 ║ c        ║
╚════════╩══════════╝

Here is what I would like to see:

╔════════╦══════════╗
║   id   ║ somecrap ║
╠════════╬══════════╣
║      1 ║ a        ║
║ 100000 ║ b        ║
║      2 ║ c        ║
╚════════╩══════════╝

Once again, I totally understand why they did this that way...but my question is: Is there a way to ... kind of "deactivate" this auto increment value update when manually inserting an id ?

If no, is there a good solution to simulate it ?

An asumption you could make: I KNOW that my auto increments wont reach 100000 (100k is for the example... the actual use case is a much bigger figure)

Must I run two separate queries like:

select max(id) from test where id<100000

and then insert with max(id)+1 ?

Since my transaction isolation level is "read committed", such a behavior will introduce potential concurrency issues => I would like to avoid it if possible !

Upvotes: 1

Views: 103

Answers (3)

Olivier
Olivier

Reputation: 5688

Workaround, which mimics Oracle sequences:

Just create a 'referential' table which is basically a key-value table, with 'value' being an int.

The equivalent to mysequence.nextval would be:

UPDATE referential SET value=@newval:=(value+1) 
     WHERE key=/*put your key (class name for me) here*/;
SELECT @newval;

Then, the @newval can safely be used as an id.

Warning If executed in a transaction, this prevents any parallel insert. Thus, this request can be executed outside any transaction, or the 'referential' table may be created as MyISAM

Upvotes: 0

Leeft
Leeft

Reputation: 3837

Even if you got this to work reliably, it would always be a hack. And indeed, an ALTER TABLE (or any other DDL statements) would commit any existing transaction. You should really consider an alternative and cleaner approach instead.

It would be much cleaner to simply add another column (or perhaps two, one for each 32 bits) and populate these with the 64 bit id that you can then set to the value you want. If you need them always populated, then copy the auto increment PK over after the insert. This leaves the auto increment key at rest, doesn't introduce transactional problems and it is not a hack ... at the cost of a slightly larger data storage.

Upvotes: 1

Alexis_user
Alexis_user

Reputation: 425

If you need the ID after the insert, it's better to find it with a SELECT query and WHERE clauses.

It's a really bad idea !!! but :

You can use the ALTER TABLE. EDIT : This works only with MySQL 5.6 !

ALTER TABLE table_name AUTO_INCREMENT = ...;

Read the mysql manual

Upvotes: 1

Related Questions