Emulate auto-increment in MySQL / InnoDB

Question

Suppose I'm going to emulate auto-increment in MySQL / InnoDB

Conditions

• Using MySQL / InnoDB
• ID field does not have a unique index and it is not PK

Is it possible to emulate only the use of program logic, without locking at the table level. Thanks.

Answer 1

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; 

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(); It is even a session-safe value for checking which identifier you received. Make sure your tables support transactions, or that holes in the sequence are not a problem.

Answer 3

Create another table with one row and column in which the next id value will be stored. Then create an insert trigger in the source table that increments the value in the second table, captures it, and uses the ID column in the first table. You need to be careful how you make choices and updates to ensure their atomicity.

Essentially, you are emulating an Oracle sequence in MySQL. This will lock one row in the sequence table, so it may make it inappropriate for what you are doing.

ETA:

Another similar, but possibly more effective option is to create a second "sequence" table, in which there will be only one PK column with automatic increment and no other data. Ask the trigger to insert a row into this table and use the generated identifier from there to populate the identifier in the original table. Then, either a trigger or another process periodically removes all rows from the sequence table to clear it.

Answer 4

the sequence table must have an identifier as a PK autoincrement