Início / coding / Perguntas / 78320063
Accepted
david
Asked: 2024-04-13 16:56:18 +0800 CST

Valor de incremento automático para coluna de chave não primária

  • 772

No meu MariaDB eu tenho duas tabelas projecte issuea issuetabela contém todos os problemas criados para todos os projetos

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)
   );

O issue_numberé por projeto e começa sempre em 1, como incrementá-lo em 1 e resolver problemas de inserção simultânea?

Não posso usar select max(issue_number) + 1 from Issue where project_id=Xpara determinar o novo valor porque pode estar obsoleto

mariadb

3 respostas

  1. Best Answer

    Você pode criar uma tabela de "sequenciamento" para armazenar o próximo número de edição de cada projeto.

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

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

    Você pode usar o bloqueio otimista ou o bloqueio pessismista para resolver a condição de corrida. A sequência abaixo usa bloqueio pessimista. A solução Optimistic Locking é um pouco diferente (não mostrada aqui).

    Ao inserir um novo fascículo, você fará:

    1. Inicie uma transação de banco de dados.
    2. Recupere o próximo número de edição do projeto e bloqueie a linha.
    3. Aumente o número da próxima edição do projeto.
    4. Insira o problema com o número do problema recém-recuperado.
    5. Confirme a transação e libere o bloqueio.

    Em SQL será parecido com:

    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. Você poderia calcular o campo issue_number, não há necessidade de armazená-lo:

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

    ou você pode armazená-lo quando precisar do valor real com issue_numberfrequência....

    • 0

  3. Para garantir que a coluna issue_number aumente corretamente e evite problemas de inserção simultâneos, você pode usar uma combinação de gatilhos e sequências no MariaDB. Aqui está uma abordagem para conseguir isso:

    1. Crie uma sequência: primeiro, crie uma sequência para gerar números exclusivos para a coluna issue_number. As sequências são projetadas para lidar com acesso simultâneo e garantir valores exclusivos.

      CRIAR SEQUÊNCIA issue_number_sequence COMEÇAR COM 1;

    2. Use um gatilho: em seguida, crie um gatilho que defina automaticamente o issue_number ao inserir uma nova linha na tabela Issue. Este gatilho usará a sequência para gerar o próximo valor para issue_number com base no project_id.

      DELIMITOR //

      CRIAR TRIGGER before_issue_insert ANTES DE INSERT ON Issue PARA CADA LINHA COMEÇAR DEFINIR NOVO.issue_number = PRÓXIMO VALOR PARA issue_number_sequence; FIM; //

      DELIMITADOR;

    Com esta configuração, sempre que uma nova linha for inserida na tabela Issue, o gatilho before_issue_insert definirá automaticamente issue_number para o próximo valor de issue_number_sequence com base no project_id atual. Essa abordagem garante que issue_number aumente corretamente e evita problemas de inserção simultâneos.

    Observação : certifique-se de ajustar a sequência e os nomes dos gatilhos, bem como quaisquer outras especificações, com base no esquema e nos requisitos do seu banco de dados.

    • -3

relate perguntas