Emular secuencias en MySQL

Emular secuencias en MySQL

Ayer, debido a una necesidad para un proyecto en que estoy trabajando, me encontré nuevamente con la falta de sequences en MySQL. Generalmente este problema se resuelve utilizando campos con la característica autoincrement, pero aún así hay ocaciones en que eso no resuelve la problemática. Como me ocurrió a mi.

Buscando un poco en google me encontré con un enlace muy interesante en donde resuelven el problema de forma más que convincente. La probé y me funcionó a la perfección. En enlace en cuestión es :

https://www.convert-in.com/mysql-sequence.htm

La solución, muy distinta a lo acostumbrado que es tener un tabla con un único campo al que se le obtiene el Max Value y luego se incremente en “1”, consiste en (como el título de este post lo indica) emular una secuencia tal cual funciona en Oracle o en PostgreSQL.

Primero que todo, se debe crear una tabla para guardar todas nuestras sequences, de forma similar a como lo hace Oracle o Postgres (tras bambalinas).

CREATE TABLE `sequence` (
`name` varchar(100) NOT NULL,
`increment` int(11) NOT NULL DEFAULT 1,
`min_value` int(11) NOT NULL DEFAULT 1,
`max_value` bigint(20) NOT NULL DEFAULT 9223372036854775807,
`cur_value` bigint(20) DEFAULT 1,
`cycle` boolean NOT NULL DEFAULT FALSE,
PRIMARY KEY (`name`)
) ENGINE=MyISAM;

Puedes cambiar el ENGINE a InnoDB dependiendo de la versión de MySQL o MariaDB que estés utilizando.

Cada campo representa lo siguiente:

  • name: Nombre de la sequence que se está configurando.
  • increment: Valor incremental que se utilizará cada vez que use nuestra sequence.
  • min_value: Valor mínimo de la sequence.
  • max_value: Máximo valor que puede tomar la sequence.
  • cur_value: Valor inicial con que se configura la secuencia (start value ¿les suena?).
  • cycle: Permite determinar si llegado al max_value, la sequence comieza en su min_value nuevamente o no.

Luego se procede a crear nuestra primera sequence, que para este ejemplo será ejemplo_seq.

INSERT INTO sequence
( name, increment, min_value, max_value, cur_value)
VALUES
('ejemplo_seq', 1, 1, 100, 1);

Ahora toca generar la función nextval. Sí, nextval. Dijimos que emularíamos
una secuencia y esa es la forma correcta de trabajar con ellas.

Para ello se crea una function en MySQL que se encargará de consultar el valor a utilizar y de actualizar el valor. Tal cual funcionan las sequences.

DELIMITER $$
CREATE FUNCTION `nextval` (`seq_name` varchar(100))
RETURNS bigint NOT DETERMINISTIC
BEGIN
DECLARE cur_val bigint;

SELECT
cur_value INTO cur_val
FROM
sequence
WHERE
name = seq_name;

IF cur_val IS NOT NULL THEN
UPDATE
sequence
SET
cur_value = IF (
(cur_value + increment) > max_value OR (cur_value + increment) < min_value, IF ( cycle = TRUE, IF ( (cur_value + increment) > max_value,
min_value,
max_value
),
NULL
),
cur_value + increment
)
WHERE
name = seq_name;
END IF;
RETURN cur_val;
END;
$$

En este paso ya tenemos nuestra sequence creada y lista para funcionar por medio de la función nextval. Sólo queda probarla. Para esto, tal cual lo haríamos en un motor de base de datos que cuenta con esta característica, utilizaremos simplemente:

select nextval('ejemplo_seq');

Espero les sirva y lo encuentren tan útil como yo.

Saludos!