MySQL e sequenze

Premessa: MySQL non gestisce le sequenze. O meglio, MySQL ha un campo auto_increment, ma è possibile specificarne uno per tabella. Di fatto, MySQL non implementa una struttura di creazione, gestione e cancellazione delle sequenze indipendenti dalla tabella (quello che su altri database si ottiene con CREATE SEQUENCE e affini, per capirci). Per ovviare al problema, bisogna quindi emulare la gestione delle sequenze mediante l'uso di costrutti autodefiniti. Di seguito un esempio di come creare, gestire e cancellare le sequenze:

drop table sequences;
drop function create_seq;
drop function drop_seq;
drop function next_value;
drop function cur_value;

create table sequences(seq_name varchar(30),
                       seq_value integer,
                       seq_increment integer
)
engine=MyISAM;

delimiter //
create function create_seq(name varchar(30), inc integer)
returns integer
begin
  declare exist integer;

  select count(*) into exist from sequences where seq_name = name;
  
  if exist = 0 then
    insert into sequences(seq_name, seq_value, seq_increment)
        values(name, 0, inc);
    return 0;
  else
    return -1;
  end if;
end
//

create function drop_seq(name varchar(30))
returns integer
begin
  declare exist integer;

  select count(*) into exist from sequences where seq_name = name;
  
  if exist = 0 then
    return -1;
  else
    delete from sequences where seq_name = name;
    return 0;
  end if;
end
//

create function next_value(name varchar(30))
returns integer
begin
  declare exist integer;
  declare cur_value integer;
  declare new_value integer;
  declare inc_value integer;

  select count(*) into exist from sequences where seq_name = name;
  
  if exist = 0 then
    return -1;
  else
    select seq_value,
           seq_increment
      into cur_value,
           inc_value
      from sequences
     where seq_name = name;

    set new_value = cur_value + inc_value;

    update sequences
       set seq_value = new_value
     where seq_name = name;

    return new_value;
  end if;
end
//

create function cur_value(name varchar(30))
returns integer
begin
  declare exist integer;
  declare cur_value integer;

  select count(*) into exist from sequences where seq_name = name;

  if exist = 0 then
    return -1;
  else
    select seq_value
      into cur_value
      from sequences
     where seq_name = name;

    return cur_value;
  end if;
end
//

delimiter ;

Il codice in precedenza, sfruttando il fatto che le transazioni su l'engine MyISAM non sono supportate (e, di conseguenza, tutte le operazioni sono, per usare un gergo tecnico, non atomiche), permette di ottenere un incremento sempre univoco, indipendentemente dalla sessione in cui ci si trova. Di seguito, un esempio di utilizzo:

-- Viene creata la sequenza TEST con un incremento di un valore per volta
select create_seq('test', 1);

-- Viene incrementato e riportato il nuovo valore della sequenza
select next_value('test');

-- Viene mostrato il valore corrente della sequenza
select cur_value('test');

-- Viene cancellata la sequenza
select drop_seq('test');