4coders

Dziś nauczymy się jak dodać swoją funkcję w MySQL. Temat będzie jedynie liźnięty i zobrazowany ładnym przykładem, który dziś miałem okazję popełnić przy okazji tego dziwnego tygodnia jakim jest ostatni tydzień roku. O tym później.

Po co może nam się coś takiego przydać?

Dla przykładu mamy kolumnę w tabeli, która jest datą (typ date), chcemy mieć funkcję, która dla tej daty zwróci nam poprawny numer tygodnia w roku oraz rok, zapiszmy to tak: Y/v (Y – rok, v -numer tygodnia).

Zwyczajowe podejście polegałoby na napisaniu takiego modyfikatora:

date_format(DATE,"%Y/%v")

Co będzie działało popranie przez wszystkie dni tygodnia oprócz właśnie tego „magicznego” ostatniego tygodnia roku.

Do rozwiązania problemu przydadzą nam się właśnie nasze definiowane funkcje w MySQL. Napiszmy własną funkcję, która w przypadku gdy data jest grudniowa oraz tydzień wskazuje na pierwszy, odpowiednio zwiększa rok. Rzućmy okiem na kod zapisany w pliku gpw.sql ( GPW to nie Giełda Papierów Wartościowych ;-) ):

SET GLOBAL log_bin_trust_function_creators = 1;
DROP FUNCTION IF EXISTS GPW;
 
DELIMITER //
 
CREATE FUNCTION GPW (thedate DATE) RETURNS VARCHAR(20)
BEGIN
    DECLARE r VARCHAR(7);
    DECLARE y INT(4);
    DECLARE v VARCHAR(2);
 
    IF DATE_FORMAT(thedate,"%v") = '01' THEN
        IF DATE_FORMAT(thedate,"%m") = '12' THEN
            SET y = DATE_FORMAT(thedate,"%Y") + 1;
            SET v = '01';
            SET r = CONCAT(y,'/', v);
        ELSE
            SET r = DATE_FORMAT(thedate,"%Y/%v");
        END IF;
    ELSE
        SET r = DATE_FORMAT(thedate,"%Y/%v");
    END IF;
 
    RETURN r;
END
 
//
 
DELIMITER ;

Szybko o tym co widzimy w kodzie:

• w pierwszym wierszu mówimy, aby MySQL nie burzył nam się gdy będziemy chcieli dodać swoją nową funkcję
• drugi wiersz, usuwa nam funkcję GPW() jeśli taka już istnieje
• potem mówimy aby separatorem wierszy od teraz były dwa znaki //
• następnie ciało naszej funkcji, chyba proste?
• potem, dwa ukośniki są istotne, kończą nam nasz długi „wiersz”
• na końcu mówimy aby separatorem był znów średnik

Mamy funkcję w pliku, celowo mówię o pliku, gdyż tak po prostu pisze się sprawniej nasze funkcję, otwieramy nasz MySQL klient:

mysql -u root -h host -p

W konsoli:

• przechodzimy do bazy w jakiej nasza funkcja będzie widoczna (polecenie use, np. use temp)
• importujemy nasz plik z kodem funkcji poleceniem source, np. source /home/temp/gpw.sql

Od tej pory nasza funkcja GPW() powinna być widoczna w naszej bazie, tak długo, aż jej nie usuniemy. Możemy ją przetestować wpisując:

SELECT GPW('2008-12-29');

Co odpowie nam poprawnym wynikiem w postaci ciągu:

2009/01

Nasze podejście pierwotne wysypałoby się odpowiadając 2008/01.

To tyle na szybko o definiowanych funkcjach w MySQL.