在MySQL/InnoDB中模拟自增功能

Question

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

条件：

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

是否可能仅使用程序逻辑来模拟，而无需表级锁定？

谢谢。

- Howard

Answer 1

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

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;

- Jon Black

---

这段代码已经过时了，因为它已经有12年的历史了，而MySQL也在不断发展。 - David

表的定义必须不同：CREATE TABLE IF NOT EXISTS users ( user_id int(10) UNSIGNED NOT NULL DEFAULT 0, username varchar(32) NOT NULL, PRIMARY KEY (user_id) ) ENGINE=InnoDB; - David

使用分隔符 # 会出现问题，可以改用分隔符 $$。 - David

经过这些调整，它可以正常工作并且是一个很棒的解决方案！ - David

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(); 是一个安全的会话值，用来检查你得到的ID。确保你的表支持事务，或者序列中的空洞不是问题。

- Wrikken

---

这个解决方案今天仍然有效，但是如果使用的系统无法更改与数据库相关的代码（或仅有限），则在MySql中没有触发器选项。 - David

Answer 3

创建另一个只有一行和一列的表格，用于存储下一个id值。然后在原始表上创建一个插入触发器，该触发器会增加第二个表中的值，获取它，并将其用于第一个表的ID列。您需要小心地执行选择和更新以确保它们是原子的。

实质上，您正在模拟MySQL中的Oracle序列。但这会导致序列表中的单个行锁定，因此可能不适合您所做的内容。

另一个类似但可能性能更好的选项是创建第二个“序列”表，该表只有一个自动递增的PK列，没有其他数据。让您的插入触发器向该表插入一行，并使用从那里生成的ID填充原始表中的ID。然后，要么让触发器，要么让另一个进程定期删除序列表中的所有行以进行清理。

- Eric Petroelje

---

@Jon Black在此页面上为该触发器创建了一个示例：https://dev59.com/tk_Sa4cB1Zd3GeqP-DP7#3324116，我的评论解释了如何更新代码以使其在较新的MySQL版本上运行。 - David

Answer 4

序列表需要将id设置为自增主键

- Ryan