主页 / coding / 问题 / 78320063
Accepted
david
Asked: 2024-04-13 16:56:18 +0800 CST

非主键列的自动递增值

  • 772

在我的 MariaDB 中，我有两个表project，issue该表包含为所有项目issue创建的所有问题

create table if not exists Project(
  id integer PRIMARY KEY auto_increment,
  `name` varchar(200) NOT NULL
 );

 create table if not exists Issue(
  id integer PRIMARY KEY AUTO_INCREMENT,
  project_id integer,
   issue_number integer,
   content text,
    FOREIGN KEY (project_id)
        REFERENCES Project(id)
   );

每个issue_number项目都从 1 开始，如何将其加 1 并解决并发插入问题？

我无法用来select max(issue_number) + 1 from Issue where project_id=X确定新值，因为可能已经过时

mariadb

3 个回答

  1. Best Answer

    您可以创建一个“排序”表来存储每个项目的下一个问题编号。

    create table sequencing (
  project_id int primary key not null,
  next_number int
);

insert into sequencing (project_id, next_number) values (1234, 1);

    您可以使用乐观锁定或悲观锁定来解决竞争条件。下面的序列使用悲观锁定。乐观锁解决方案略有不同（此处未显示）。

    插入新问题时，您将执行以下操作：

    1. 启动数据库事务。
    2. 检索项目的下一个问题编号并锁定该行。
    3. 增加项目的下一个问题编号。
    4. 插入带有新检索到的问题编号的问题。
    5. 提交事务并释放锁。

    在 SQL 中，这将如下所示：

    start transaction;

select next_number -- save this number in app as "n"
from sequencing 
where project_id = 1234
for update;

update sequencing
set next_number = next_number + 1
where project_id = 1234;

insert into issue (..., issue_number) values (..., n); -- use "n" here

commit;
    • 1

  2. 您可以计算字段issue_number，无需存储它：

    SELECT 
   id, 
   project_id, 
   row_number() over (partition by project_id order by id) as issue_number,
   content 
FROM Issue;

    issue_number或者当您需要经常的实际值时可以存储它......

    • 0

  3. 为了确保 issues_number 列正确递增并避免并发插入问题，您可以在 MariaDB 中使用触发器和序列的组合。这是实现此目的的方法：

    1. 创建序列： 首先，创建一个序列来为 issues_number 列生成唯一编号。序列旨在处理并发访问并保证唯一值。

      创建序列 issues_number_sequence 从 1 开始；

    2. 使用触发器： 接下来，创建一个触发器，在将新行插入问题表时自动设置问题编号。该触发器将使用序列根据project_id 生成issue_number 的下一个值。

      分隔符 //

      在插入问题前为每行开始设置 NEW.issue_number = Issue_number_sequence 的下一个值；在_issue_insert 之前创建触发器；结尾; //

      分隔符；

    通过此设置，每当将新行插入问题表时，触发器 before_issue_insert 都会根据当前的project_id自动将issue_number设置为issue_number_sequence中的下一个值。此方法可确保 issues_number 正确递增并避免并发插入问题。

    注意：确保根据您的数据库架构和要求调整序列和触发器名称以及任何其他细节。

    • -3

相关问题