Databázový systém MySQL je velmi oblíbený a v praxi rozšířený (nejvíce asi u webů), ale chybí mu poměrně dost funkcí vyspělejších SŘBD. Jednou z nich jsou sekvence. V dnešním článku si ukážeme, jak je v MySQL emulovat.

MySQL sekvence (alespoň ve verzi 5.1) nepodporuje. To někdy nevadí a místo sekvencí se používají sloupečky typu AUTO_INCREMENT, u kterých MySQL automaticky nastavuje rostoucí hodnoty.

Skutečné sekvence mají ale daleko širší využití a jsou pružnější. Sekvence je databázový objekt, který udržuje aktuální hodnotu, jedná se vlastně o počítadlo. Ze sekvence můžeme získat její aktuální hodnotu – v např. PostgreSQL takto:

SELECT currval('moje_sekvence');

nebo si vyžádat další hodnotu:

SELECT nextval('moje_sekvence');

Tím zvedneme hodnotu sekvence o 1 (lze nastavit i větší krok) a získáme tuto hodnotu. Důležité je, že když tutéž funkci bude volat i někdo jiný, nikdy nedostane přidělenou stejnou hodnotu jako my – nemůže dojít ke konfliktu.

Sekvence se často používají ke generování umělých primárních klíčů – nextval('moje_sekvence') nastavíme jako výchozí hodnotu daného sloupce, tudíž se tato funkce zavolá vždy, kdy je třeba.

Poznámka: nedávno jsem potřeboval v MySQL očíslovat záznamy napříč několika tabulkami tak, aby měly jedinečná čísla (doplnit tam nové sloupce). Tudíž nešlo použít AUTO_INCREMENT, protože pak by se číslování v různých tabulkách překrývala. Bylo by sice možné jim nastavit dostatečný „rozestup“ např. milion (s tím, že budeme doufat, že jedna tabulka nebude mít víc než milion řádků), ale to také nešlo použít, protože jsem potřeboval, aby číslování odpovídalo pořadí, v jakém byly záznamy vytvořeny bez ohledu na tabulku, ve které se nacházejí. Proto jsem potřeboval nějak emulovat chování sekvencí v MySQL.

Vytvoříme si pomocnou tabulku, ve které budeme udržovat hodnotu sekvence.

CREATE TABLE  moje_sekvence (
  id INT NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (id)
);

Pro získání aktuální hodnoty místo:

SELECT currval('moje_sekvence');

použijeme:

SELECT max(id) FROM moje_sekvence;

A pro získání další hodnoty si vytvoříme funkci:

DELIMITER $$
CREATE FUNCTION moje_sekvence_generuj_id() RETURNS INT
BEGIN
	INSERT INTO moje_sekvence VALUES (null);
	RETURN last_insert_id();
END$$
DELIMITER ;

a místo:

SELECT nextval('moje_sekvence');

budeme v MySQL psát:

SELECT moje_sekvence_generuj_id();

Teď bychom už jen nastavili výchozí hodnotu daných sloupců (v různých tabulkách) na moje_sekvence_generuj_id(). Jenže to bohužel nejde, protože v MySQL může být výchozí hodnota jen konstanta. Takže je to opět trochu složitější a musíme si pomoci spouští (triggerem), která se bude volat při vložení nového záznamu (spoušť už funkce volat může).

CREATE TRIGGER moje_tabulka_insert 
BEFORE INSERT ON moje_tabulka
FOR EACH ROW SET new.id = moje_sekvence_generuj_id();

Při vložení nového záznamu dojde k zavolání funkce moje_sekvence_generuj_id(), která vloží záznam do pomocné tabulky moje_sekvence a vrátí hodnotu, kterou následně spoušť uloží to sloupce id tabulky moje_tabulka.

Ještě doplníme hodnoty k existujícím záznamům:

UPDATE moje_tabulka
SET id = moje_sekvence_generuj_id()
WHERE id IS NULL
ORDER BY datum;

a můžeme sloupeček id nastavit jako NOT NULL.

Pokud to typ tabulky umožňuje, mohli bychom ještě vytvořit cizí klíč: moje_tabulka.id → moje_sekvence.id

Jak by to vypadalo v PostgreSQL

Vytvoříme sekvenci:

CREATE SEQUENCE moje_sekvence;

Nastavíme výchozí hodnotu sloupce:

ALTER TABLE moje_tabulka
ALTER COLUMN id SET DEFAULT nextval('moje_sekvence');

Obecné řešení

Naše řešení je oproti PostgreSQL stále dost těžkopádné – potřebujeme pro každou sekvenci zvláštní funkci a tabulku a také se nám v databázi kupí vygenerované hodnoty sekvence, což sice můžeme využít pro cizí klíče, ale pravděpodobně to potřebovat nebudeme, takže to jsou zbytečná data.

Pokusíme se tedy emulovat chování PostgreSQL tak, abychom i v MySQL mohli volat funkce currval() a nextval()

Vytvoříme si tabulku, ve které budou uloženy hodnoty všech sekvencí:

CREATE TABLE sekvence (
	nazev VARCHAR(16) NOT NULL,
	hodnota INT NOT NULL,
	krok INT DEFAULT 1,
	PRIMARY KEY (nazev)
) ENGINE = InnoDB;

Vytvoříme si nějaké sekvence:

INSERT INTO sekvence (nazev, hodnota) VALUES ('pokusy', 100);
INSERT INTO sekvence (nazev, hodnota, krok) VALUES ('dvojka', 200, 2);

Při volání nextval() se nemusí sekvence zvyšovat jen o 1, můžeme nastavit volitelný krok.

Vytvoříme si funkci pro získání aktuální hodnoty sekvence:

DELIMITER $$
CREATE FUNCTION currval(nazev_sekvence VARCHAR(16)) RETURNS INT
BEGIN
	RETURN (
		SELECT	hodnota
		FROM	sekvence 
		WHERE	nazev = nazev_sekvence
	);
END$$
DELIMITER ;

V případě neexistující sekvence vrací NULL.

A druhou funkci pro zvyšování hodnoty sekvence (vrací tuto novou hodnotu):

DELIMITER $$
CREATE FUNCTION nextval(nazev_sekvence VARCHAR(16)) RETURNS INT
BEGIN

	DECLARE	nova_hodnota INT;
	
	SELECT	hodnota + krok INTO nova_hodnota
	FROM	sekvence 
	WHERE	nazev = nazev_sekvence 
	FOR UPDATE;
	
	UPDATE	sekvence 
	SET	hodnota = nova_hodnota
	WHERE	nazev = nazev_sekvence;
	
	RETURN nova_hodnota;

END$$
DELIMITER ;

V případě neexistující sekvence opět vrací NULL a nic nezvyšuje.

Ukázka získání současné hodnoty a zvýšení hodnoty sekvence:

SET autocommit = 0;
SELECT currval('pokusy') AS puvodni_hodnota_pokusy;
SELECT nextval('pokusy') AS nova_hodnota_pokusy;
SELECT currval('dvojka') AS puvodni_hodnota_dvojka;
SELECT nextval('dvojka') AS nova_hodnota_dvojka;
-- SELECT * FROM sekvence;
-- ROLLBACK;
-- SELECT * FROM sekvence;
COMMIT;

Při zapnutém autocommitu nefunguje SELECT … FOR UPDATE – viz
MySQL 5.1 Manual