Wpisy oznaczone tagiem date_format
Jak dodać swoją funkcję w MySQL?
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.