Tworzenie funkcji składowanej MySQL

Strony internetowe, do budowy których została wykorzystana baza danych MySQL, mogą wymagać niekiedy zastosowania skomplikowanego algorytmu dostępu do danych.

Przy budowaniu złożonych zapytań do bazy danych MySQL warto rozważyć odseparowanie części zapytania, które może być wykorzystane również w innych sytuacjach i utworzenie funkcji składowanej. Przeniesienie części kodu do funkcji oprócz możliwości późniejszego jej wykorzystania dodatkowo ograniczy rozmiar kodu zapytania. Gdy funkcja otrzyma dodatkowo nazwę opisującą w wystarczający sposób wynik jej działania znacznie podniesiemy czytelność kodu zapytania.

Mimo powyższych zalet należy jednak pamiętać, że użycie funkcji w klauzuli WHERE może w znaczny sposób wpłynąć negatywnie na wydajność takiego zapytania, a tym samym spowolnić działanie strony internetowej.

Chciałbym zaznaczyć, że omawiane tu zagadnienie ma na celu omówienie trudności jakie projektant stron internetowych może napotkać podczas tworzenia własnej funkcji, a nie przygotowanie algorytmu do zrealizowania przez taką funkcję.

Zacznijmy więc od utworzenia najprostszej funkcji, która nie będzie miała żadnego parametru wejściowego, a będzie po prostu zwracać jakiś wynik. Powiedzmy, że mamy gdzieś zapisaną stopkę dla naszej strony internetowej i chcielibyśmy ją wyświetlić w przeglądarce. Oczywiście taka funkcjonalność może, być zrealizowana przez zwykły SELECT, ale tak jak wspomniałem wcześniej nie chodzi tutaj o poszukiwania optymalnego algorytmu.

Nazwijmy funkcję showFooter. Funkcja będzie pobierać z tabeli website_details treść stopki i będzie mogła być zastosowana wszędzie tam gdzie na stronie internetowej będziemy chcieli wyświetlić taki tekst.

Spróbujmy, więc utworzyć taka funkcję:

CREATE FUNCTION showFooter() RETURNS TEXT
SELECT detailValue From website_details WHERE detailName = footer

W odpowiedzi otrzymamy komunikat:

#1415 – Not allowed to return a result set from a function

i nawet, gdy ograniczymy zapytanie dodając na końcu ogranicznik LIMIT 1, serwer MySQL uzna to w dalszym ciągu za błąd i nie pozwoli na utworzenie takiej funkcji. Co innego, gdy rezultat działania takiego zapytania zapiszemy do zmiennej.

Dodajmy, więc na początku zapytania deklarację zmiennej footer i zapiszmy do niej rezultat naszego query:

CREATE FUNCTION showFooter() RETURNS TEXT
DECLARE footer TEXT;
SELECT detailValue INTO footerFrom website_details WHERE detailName = footer

Uruchomienie zapytania w takiej postaci również zakończy się komunikatem błędu, mówiącym że w drugiej linii mamy błąd składniowy. Funkcje, których kod składa się więcej niż z jednej linii, powinny być ujęte w blok BEGIN END. Niestety przy uzupełnieniu kodu funkcji o polecenia BEGIN i END serwer w dalszym ciągu będzie sygnalizował błąd składni, tym razem w linii 2.

CREATE FUNCTION showFooter() RETURNS TEXT
BEGIN;
DECLARE footer TEXT;
SELECT detailValue INTO footerFrom website_details WHERE detailName = footer
END;

#1064 – You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ” at line 2

Problem w tym, że średnik jest używany jako separator wyrażenia, więc aby nasze zapytanie zostało uznane przez MySQL ( a ściślej przez program, w którym tworzymy zapytanie ) jako całość, musimy zmienić separator. Można to zrealizować poprzez dodanie na początku dyrektywy:
DELIMITER ze znakiem oznaczającym koniec zapytania, należy pamiętać, żeby na końcu zapytania, w którym został zmieniony separator powrócić na koniec do opcji domyślnej, czyli do średnika. Dla przykładu jako separatora użyję znaków //

przyjrzyjmy się więc utworzonemu kodowi funkcji:

DELIMITER //
CREATE FUNCTION showFooter() RETURNS TEXT
BEGIN;
DECLARE footer TEXT;
SELECT detailValue INTO footerFrom website_details WHERE detailName = footer
END//
DELIMITER ;

Niestety to nie koniec naszych zmagań z tworzeniem funkcji. Przy próbie uruchomienia powyższego kodu otrzymamy komunikat błędu, mówiący o tym że:

#1418 – This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)

Problem rozwiązuje ustawienie zmiennej log_bin_trust_function_creators na 1 ( domyślnie ma ona wartość 0)

SET GLOBAL log_bin_trust_function_creators = 1;

lub użycie w deklaracji funkcji dyrektywy DETERMINISTIC, wtedy MySQL uzna, że funkcja nie modyfikuje danych i pozwoli na jej utworzenie. Wróćmy, więc do domyślnych ustawień serwera i uspokójmy używając opcji DETERMINISTIC w kodzie funkcji.

SET GLOBAL log_bin_trust_function_creators = 0;
DELIMITER //
CREATE FUNCTION showFooter() RETURNS TEXT DETERMINISTIC
BEGIN;
DECLARE footer TEXT;
SELECT detailValue INTO footerFrom website_details WHERE detailName = footer
END//
DELIMITER;

Pamiętajmy jednak, że mimo iż jest możliwe utworzenie niedeterministycznej funkcji MySQL nie uruchomi jej gdy na serwerze wykorzystywany jest statement-based binary logging.