在MySQL/InnoDB中模拟自动增量 [英] Emulate auto-increment in MySQL/InnoDB

问题描述

假设我要在MySQL/InnoDB中模拟自动增量

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

条件

1. 使用MySQL/InnoDB
2. ID字段没有唯一索引，也不是PK

是否可以仅使用程序逻辑进行仿真，而无需进行表级锁定. 谢谢.

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

推荐答案

使用序列表和触发器-像这样:

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;

这篇关于在MySQL/InnoDB中模拟自动增量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！