Simulando secuencias en MySQL

Quienes hemos usado PostgresSQL solemos fastidiarnos con el código que hay que escribir para tener un campo auto-numérico, pero también hemos llegado a extrañar esa característica cuando usamos MySQL.

Como sabemos MySQL, usa la propiedad AUTO_INCREMENT para campos numéricos que usamos en claves primarias.

Pero en que situaciones podemos necesitar una secuencia? pues les doy algunas ideas:

  • Cuando queremos tener mas de un campo auto-numérico en una tabla.
  • Cuando necesitamos un contador general que podemos utilizar en mas de una tabla.
  • No se me ocurren mas, pero estoy seguro que en algún momento podemos darle un uso.

Manos a la obra:

Necesitaremos una tabla para guardar el valor actual de la secuencia:

create table _sequence
(
	seq_name varchar(50) not null primary key,
	seq_val int unsigned not null
);

Vamos a necesitar también una función para obtener el valor siguiente de la secuencia, incluyendo casos como:

  • Si no existe la secuencia, crearla (para qué complicarnos).
  • Actualizar el nuevo valor para la siguiente llamada
  •  

    He aquí la función getNextSeq que recibe como parámetro, el nombre de la secuencia:

    /*Funcion que devuelve el siguiente valor de una secuencia*/
    delimiter //
    drop function if exists getNextSeq//
     
    create function getNextSeq(sSeqName varchar(50)) returns int unsigned
    begin
        declare nLast_val int;
    
        set nLast_val =  (select seq_val 
                              from _sequence 
                              where seq_name = sSeqName);
        if nLast_val is null then
            set nLast_val = 1;
            insert into _sequence (seq_name,seq_val) 
            values (sSeqName,nLast_Val);
        else
            set nLast_val = nLast_val + 1;
            update _sequence set seq_val = nLast_val 
            where seq_name = sSeqName;
        end if;
    
        return nLast_val;
    end//
     
    delimiter ;

     

    Ahora necesitamos (a manera de mantenimiento) un procedimiento para modificar el valor de una secuencia:

    /*Procedimiento que crea/establece el valor de una secuencia*/
    delimiter //
    drop procedure if exists sp_setSeqVal//
    
    create procedure sp_setSeqVal(sSeqName varchar(50), nVal int unsigned)
    begin
        if (select count(*) from _sequence where seq_name = sSeqName) = 0 then
            insert into _sequence (seq_name,seq_val) 
            values (sSeqName,nVal);
        else
            update _sequence set seq_val = nVal 
            where seq_name = sSeqName;
        end if;
    end//
    
    delimiter ;
    

     

    Es todo!, ahora sólo necesitamos hacer algunas pruebas, para ello vamos a crear una tabla en la cual vamos a tener dos campos auto-generados con nuestra secuencia.

    create table multiple_autonums
    (
       id int not null primary key auto_increment,
       seq_1 int, -- Secuencia 1
       seq_2 int, -- Secuencia 2
       unique(seq_1),
       unique(seq_2)
    );

    Para que la magia ocurra debemos crear también un trigger antes de insertar:

    /*trigger ejecutado antes de insertar que obtiene
      los siguientes valores de las secuencias*/
    
    delimiter //
    drop trigger if exists multiple_autonums_ai//
    
    create trigger multiple_autonums_bi before insert on multiple_autonums
    for each row
    begin
       set new.seq_1 = getNextSeq("seq_1");
       set new.seq_2 = getNextSeq("seq_2");
    end//
    
    delimiter ;
    

     

    Hagamos algunas pruebas

    -- insertando valores para ver que pasa.
    insert into multiple_autonums (id) values (null),(null),(null);
    
    -- Resultado de la primera inserción
    select * from multiple_autonums;
    +----+-------+-------+
    | id | seq_1 | seq_2 |
    +----+-------+-------+
    |  1 |     1 |     1 |
    |  2 |     2 |     2 |
    |  3 |     3 |     3 |
    +----+-------+-------+
    3 rows in set (0.00 sec)
    
    -- Modificando las secuencias
    call sp_setSeqVal("seq_1",47);
    call sp_setSeqVal("seq_2",9786);
    
    -- Insertando nuevamente los valores
    insert into multiple_autonums (id) values (null),(null),(null);
    
    -- Viendo que ha pasado
    select * from multiple_autonums;
    +----+-------+-------+
    | id | seq_1 | seq_2 |
    +----+-------+-------+
    |  1 |     1 |     1 |
    |  2 |     2 |     2 |
    |  3 |     3 |     3 |
    |  4 |    48 |  9787 |
    |  5 |    49 |  9788 |
    |  6 |    50 |  9789 |
    +----+-------+-------+
    

     

    Que les pareció?

    Me acabo de dar cuenta que hay algunas condiciones que no he tomado en cuenta, pero de todas formas sirve. Incluso pueden modificar las funciones para hacer mas complejo el calculo del valor siguiente, se me ocurre por ejemplo que podemos generar numeros de facturas al estilo: [serie]-[numero] ejem: 001-00485

    Espero les sirva.

6 pensamientos en “Simulando secuencias en MySQL

  1. Gracias por el artículo Ivan.

    ¿Qué opinas de un esquema de una (1) tabla por secuencia? La tabla (por secuencia) tendría una (1) columna AUTO_INCREMENT que manejaría la secuencia.
    ¿Sería esto más fiable en un entorno multi-hilos?

    Muchas gracias y un saludo.

    • William,

      No sería muy practico tener una tabla por secuencia, imagínate tener 200 secuencias, tendrías 200 tablas? se convertiría en algo inmanejable.

      He realizado algunas pruebas y no he tenido problemas con la generación, y creo que no habrán problemas mientras las función getNextSeq sea llamada desde un trigger, en otro caso pueden presentarse duplicados debido a la concurrencia.

      Saludos.

      • Hola Ivan,

        Ciertamente mientras aumente el número de secuencias se volverá algo inmagenable el manejo de tablas así como yo lo planteo.

        ¿Cómo se puede automatizar el esquema que planteas? ya que al momento de adicionar una nueva secuencias tendría que alterar la tabla «multiple_autonums» y modficar el trigger «multiple_autonums_ai»?

        Muchas gracias.

        Un saludo.

  2. Pingback: Valores auto_increment personalizados » Fatal exception

Deja un comentario

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *