Emulate auto-increment in MySQL/InnoDB

Question

Assume I am going to emulate auto-increment in MySQL/InnoDB

Conditions

1. Using MySQL/InnoDB
2. The ID field don't have unique index, nor it is a PK

Is it possible to emulate only using program logics, without table level lock. Thanks.

Answer 1

Create another table with a single row and column that stores the next id value. Then create an insert trigger on the original table that increments the value in the second table, grabs it, and uses that for the ID column on the first table. You would need to be careful with the way you do the select and update to ensure they are atomic.

Essentially you are emulating an Oracle sequence in MySQL. It would cause a lock on single row in the sequence table though, so that may make it inappropriate for what you are doing.

ETA:

Another similar but maybe better performing option would be to create a second "sequence" table that just has a single auto-increment PK column and no other data. Have your insert trigger insert a row into that table and use the generated ID from there to populate the ID in the original table. Then either have the trigger or another process periodically delete all the rows from the sequence table to clean it up.

Answer 2

CREATE TABLE sequence (id INTEGER); -- possibbly add a name;
INSERT INTO sequence VALUES (1); -- starting value

SET AUTOCOMMIT=0;
START TRANSACTION;
UPDATE sequence SET id = LAST_INSERT_ID(id+1);
INSERT INTO actualtable (non_autoincrementing_key) VALUES (LAST_INSERT_ID());
COMMIT;

SELECT LAST_INSERT_ID(); Is even a session-safe value to check which ID you got. Be sure your table support transactions, or that holes in a sequence are no problem.

Answer 3

sequence table need to have id as the autoincrement PK

Answer 4

Use a sequence table and a trigger - something like this:

drop table if exists users_seq;

create table users_seq
(
next_seq_id int unsigned not null default 0
)engine = innodb;

drop table if exists users;

create table users
(
user_id int unsigned not null primary key,
username varchar(32) not null
)engine = innodb;

insert into users_seq values (0);

delimiter #

create trigger users_before_ins_trig before insert on users
for each row
begin

declare id int unsigned default 0;

  select next_seq_id + 1 into id from users_seq;

  set new.user_id = id;

  update users_seq set next_seq_id = id;

end#

delimiter ;

insert into users (username) values ('f00'),('bar'),('bish'),('bash'),('bosh');

select * from users;
select * from users_seq;

insert into users (username) values ('newbie');

select * from users;
select * from users_seq;