MySQL Funkcje: łańcuchowe, numeryczne, definiowane przez użytkownika, przechowywane

Jakie są MySQL Funkcje?

MySQL może zrobić znacznie więcej niż tylko przechowywanie i pobieranie danych. Może też wykonywać manipulacje na danych przed pobraniem lub zapisaniem. To właśnie tam MySQL Pojawiają się funkcje. Funkcje to po prostu fragmenty kodu, które wykonują operację, a następnie zwracają wynik. Niektóre funkcje akceptują parametry, a inne nie.

Przyjrzyjmy się pokrótce przykładowi. Domyślnie, MySQL Zapisuje typy danych daty w formacie „RRRR-MM-DD”. Załóżmy, że stworzyliśmy aplikację, a nasi użytkownicy chcą zwrócić datę w formacie „DD-MM-RRRR”. Możemy użyć MySQL wbudowana funkcja DATE_FORMAT, aby to osiągnąć. DATE_FORMAT jest jedną z najczęściej używanych funkcji w MySQL, i przyjrzymy się temu szczegółowo później w tej lekcji.

Niezależnie od rodzaju, funkcja zawsze zwraca pojedynczą wartość, może akceptować zero lub więcej parametrów w nawiasach i można używać wszędzie tam, gdzie dozwolone jest wyrażenie — na liście SELECT, w klauzuli WHERE lub klauzuli ORDER BY.

Dlaczego warto korzystać MySQL Funkcje?

Skoro już wiemy, czym jest funkcja, następne pytanie brzmi: dlaczego w ogóle powinniśmy przesyłać tę pracę do bazy danych?

Jak pokazano na powyższym diagramie, funkcja przyjmuje wartość wejściową, stosuje logikę raz w silniku bazy danych i zwraca pojedynczy wynik każdej aplikacji, która o niego poprosi.

Programiści mogą myśleć: „Po co zawracać sobie tym głowę?” MySQL Funkcje? Ten sam efekt można osiągnąć za pomocą języka skryptowego lub programowania”. Prawdą jest, że możemy to osiągnąć, pisząc procedurę w programie aplikacyjnym.

Wracając do naszego przykładu DATY, aby nasi użytkownicy mogli otrzymać dane w pożądanym formacie, warstwa biznesowa musiałaby sama wykonać niezbędne przetwarzanie.

Staje się to problemem, gdy aplikacja musi integrować się z innymi systemami. Kiedy używamy MySQL Funkcje takie jak DATE_FORMAT są osadzone w bazie danych, a każda aplikacja potrzebująca danych otrzymuje je w wymaganym formacie. zmniejsza konieczność ponownego wprowadzania zmian w logice biznesowej i zmniejsza niespójności danych.

Kolejny powód do rozważenia MySQL funkcjami jest to, że mogą pomóc zmniejszyć ruch sieciowy w aplikacjach klient/serwerWarstwa biznesowa musi jedynie wywołać funkcję składowaną, bez konieczności pobierania surowych wierszy przez sieć w celu ich manipulacji. Średnio rzecz biorąc, użycie funkcji może znacznie poprawić ogólną wydajność systemu.

Rodzaje MySQL Funkcje

Mając już ustalone „co” i „dlaczego”, możemy teraz przyjrzeć się trzem rodzinom funkcji MySQL oferuje: funkcje wbudowane, funkcje składowane i funkcje zdefiniowane przez użytkownika.

Wbudowane funkcje

MySQL jest dostarczany z wieloma wbudowanymi funkcjami — funkcjami już zaimplementowanymi w MySQL Serwer. Umożliwiają one wykonywanie wielu rodzajów manipulacji danymi i dzielą się na następujące, powszechnie używane grupy.

• Funkcje ciągów – operuj na typach danych typu string
• Funkcje numeryczne – operować na typach danych numerycznych
• Funkcje daty – operuj na typach danych dat
• Funkcje agregujące – przeprowadzaj operacje na wszystkich powyższych typach danych i generuj podsumowane zestawy wyników.
• Inne funkcje - MySQL obsługuje również inne typy wbudowanych funkcji, ale w tej lekcji ograniczymy się do grup wymienionych powyżej.

Przyjrzyjmy się teraz szczegółowo każdej z wyżej wymienionych grup. Wyjaśnimy najczęściej używane funkcje na podstawie naszej przykładowej bazy danych „Myflixdb”.

Funkcje ciągów

Funkcje łańcuchowe operują na wartościach tekstowych. W naszej tabeli filmów tytuły są przechowywane z użyciem kombinacji małych i wielkich liter. Załóżmy, że chcemy zapytania, które zwraca tytuły zapisane wielkimi literami. Funkcja „UCASE” przyjmuje ciąg znaków jako parametr i konwertuje każdą literę na wielką, jak pokazuje poniższy skrypt.

SELECT `movie_id`, `title`, UCASE(`title`) AS `upper_case_title` FROM `movies`;

TUTAJ

• UCASE(`tytuł`) jest wbudowaną funkcją przyjmującą tytuł jako parametr i zwracającą go wielkimi literami.
• JAKO `upper_case_title` nadaje kolumnie obliczeniowej alias, dzięki czemu zestaw wyników zawiera czytelny nagłówek zamiast surowego wyrażenia.

Wykonanie powyższego skryptu w MySQL Porównanie Workbencha z Myflixdb daje nam wyniki pokazane poniżej.

Oprócz UCASE warto pamiętać o dwóch towarzyszach: LCAZA konwertuje ciąg na małe litery i KONKAT łączy dwa lub więcej ciągów znaków w jeden. Pełną listę znajdziesz w MySQL odniesienie do funkcji łańcuchowej.

Funkcje numeryczne

Jak wspomniano wcześniej, funkcje numeryczne operują na typach danych liczbowych. Możemy również wykonywać obliczenia matematyczne na danych liczbowych bezpośrednio w naszych poleceniach SQL.

Operatory arytmetyczne

MySQL obsługuje następujące operatory arytmetyczne, których można używać do wykonywania obliczeń w poleceniach SQL.

Przykłady poszczególnych operatorów podano poniżej.

Dzielenie całkowite (DIV) — DIV odrzuca część ułamkową i zwraca tylko liczbę całkowitą.

SELECT 23 DIV 6;

Wykonanie powyższego skryptu daje nam 3.

Operator dzielenia (/) — w przeciwieństwie do DIV, operator dzielenia zachowuje część dziesiętną wyniku.

SELECT 23 / 6;

Wykonanie powyższego skryptu daje nam 3.8333.

Podłożetracoperator cji (-)

SELECT 23 - 6;

Wykonanie powyższego skryptu daje nam 17.

Operator dodawania (+)

SELECT 23 + 6;

Wykonanie powyższego skryptu daje nam 29.

Operator mnożenia (*)

SELECT 23 * 6 AS `multiplication_result`;

Wynik:

Operator modulo (% lub MOD)

Operator modulo dzieli N przez M i daje nam resztę. Przyjrzyjmy się przykładowi operatora modulo, używając tych samych wartości, co w poprzednich przykładach.

SELECT 23 % 6;
-- OR, equivalently:
SELECT 23 MOD 6;

Wykonanie któregokolwiek ze skryptów daje nam 5.

Przyjrzyjmy się teraz niektórym typowym funkcjom numerycznym w MySQL.

PODŁOGA – ta funkcja usuwa miejsca dziesiętne z liczby i zaokrągla ją w dół do najbliższej liczby całkowitej. Poniższy skrypt demonstruje jej użycie.

SELECT FLOOR(23 / 6) AS `floor_result`;

Wynik:

ROUND – ta funkcja zaokrągla liczbę do najbliższej liczby całkowitej. Ponieważ 23/6 daje wynik 3.8333, funkcja ROUND zwraca 4, a FLOOR 3 — te dwie funkcje nie są zamienne.

SELECT ROUND(23 / 6) AS `round_result`;

Wynik:

SKRAJ – ta funkcja generuje liczbę losową. Jej wartość zmienia się przy każdym wywołaniu funkcji. Poniższy skrypt demonstruje jej użycie.

SELECT RAND() AS `random_result`;

Funkcje daty

Funkcje daty operują na typach danych daty i daty-godziny. Funkcja DATE_FORMAT rozwiązuje problem „RRRR-MM-DD kontra DD-MM-RRRR” opisany we wstępie.

FORMAT DATY Przyjmuje dwa parametry: wartość daty do sformatowania oraz ciąg formatujący utworzony z symboli zastępczych. Poniższy skrypt zwraca każdą datę wydania w formacie dzień-miesiąc-rok, o który prosili nasi użytkownicy.

SELECT `title`, DATE_FORMAT(`date_released`, '%d-%m-%Y') AS `formatted_date`
FROM `movies`;

Poniżej wymieniono najczęściej używane symbole zastępcze formatu.

W codziennej pracy stale pojawiają się trzy inne funkcje daty:

• CURDATE () zwraca bieżącą datę w formacie RRRR-MM-DD.
• TERAZ() zwraca aktualną datę oraz czas.
• DATA.RÓŻNICA(d1, d2) zwraca liczbę dni pomiędzy dwiema datami — podstawę każdego raportu o przeterminowanym wynajmie.

Aby zobaczyć pełną listę, zobacz MySQL odniesienie do funkcji daty i czasu.

Przechowywane funkcje

Funkcje wbudowane obejmują typowe przypadki. Gdy reguła biznesowa jest bardziej szczegółowa, piszemy własną – i właśnie do tego służy funkcja zapisana.

Funkcje składowane zachowują się tak samo jak funkcje wbudowane, z tą różnicą, że definiuje się je samodzielnie. Po utworzeniu funkcja składowana może być używana w poleceniach SQL dokładnie tak samo, jak każda inna funkcja. Poniżej przedstawiono podstawową składnię.

CREATE FUNCTION sf_name ([parameter(s)])
RETURNS data_type
[DETERMINISTIC | NOT DETERMINISTIC]
BEGIN
    -- procedural statements
END

TUTAJ

• „UTWÓRZ FUNKCJĘ sf_name ([parametr(y)])” jest obowiązkowy i informuje MySQL serwer, aby utworzyć funkcję o nazwie `sf_name` z opcjonalnymi parametrami zdefiniowanymi w nawiasach.
• „ZWRACA typ_danych” jest obowiązkowe i określa typ danych zwracanych przez funkcję.
• „DETERMINISTYCZNY” deklaruje, że funkcja zwraca tę samą wartość za każdym razem, gdy podane zostaną te same argumenty. „NIEDETERMINISTYCZNE” oświadcza coś przeciwnego.
• „POCZĄTEK…KONIEC” opakowuje kod proceduralny, który wykonuje funkcja.

Załóżmy, że chcemy wiedzieć, które wypożyczone filmy są już po terminie zwrotu. Możemy utworzyć funkcję składowaną, która przyjmuje datę zwrotu jako parametr i porównuje ją z bieżącą datą na serwerze. Jeśli bieżąca data jest późniejsza niż data zwrotu, film jest przeterminowany i zwracamy „Tak”; w przeciwnym razie zwracamy „Nie”.

DELIMITER |
CREATE FUNCTION sf_past_movie_return_date (return_date DATE)
RETURNS VARCHAR(3)
NOT DETERMINISTIC
BEGIN
    DECLARE sf_value VARCHAR(3);
    IF CURDATE() > return_date THEN
        SET sf_value = 'Yes';
    ELSEIF CURDATE() <= return_date THEN
        SET sf_value = 'No';
    END IF;
    RETURN sf_value;
END|
DELIMITER ;

⚠️ Ostrzeżenie — nie oznaczaj tej funkcji jako DETERMINISTYCZNA. Treść wywołuje funkcję CURDATE(), więc ten sam argument może zwrócić dziś „Nie”, a jutro „Tak”. Deklaracja funkcji zależnej od czasu DETERMINISTIC wprowadza optymalizator w błąd i jest niebezpieczna dla replikacji opartej na poleceniach. Użyj NIE DETERMINISTYCZNY za każdym razem, gdy ciało wywołuje CURDATE(), NOW() lub RAND().

Wykonanie powyższego skryptu tworzy funkcję składowaną `sf_past_movie_return_date`. Przetestujmy ją teraz.

SELECT `movie_id`, `membership_number`, `return_date`, CURDATE(),
       sf_past_movie_return_date(`return_date`) AS `is_overdue`
FROM `movierentals`;

Wykonanie powyższego skryptu w MySQL Workbench i myflixdb dają nam następujące wyniki.

Zwróć uwagę na dwa wiersze NULL. Gdy `return_date` ma wartość NULL, oba porównania zwracają NULL zamiast TRUE lub FALSE, więc żadna z gałęzi IF nie jest wykonywana, a funkcja zwraca NULL — oczekiwany wynik, ponieważ niezwrócony film nie ma daty powrotu do porównania.

Funkcje zdefiniowane przez użytkownika

Gdy sam SQL nie jest wystarczająco szybki, MySQL pozwala na trzecią opcję. Funkcje zdefiniowane przez użytkownika (UDF) są pisane w języku kompilowanym, takim jak C or C++, wbudowane w bibliotekę współdzieloną i zarejestrowane na serwerze. Po dodaniu są wywoływane jak każda inna funkcja. Ponieważ funkcja UDF działa jako kod natywny w procesie serwera, nadaje się do intensywnych obliczeń — ale błąd w jednej z nich może spowodować awarię serwera, dlatego funkcje UDF są używane znacznie rzadziej niż funkcje składowane.

Funkcje wbudowane, zapisane, zdefiniowane przez użytkownika: których powinieneś używać?

Wszystkie trzy rodziny zwracają pojedynczą wartość i można je wywołać z dowolnego polecenia SQL, ale różnią się pod względem tego, kto je pisze, gdzie są uruchamiane i jakie ryzyko ze sobą niosą. Poniższa tabela podsumowuje te różnice.

Z reguły zacznij od funkcji wbudowanej. Jeśli żadna nie pasuje, napisz funkcję składowaną, aby reguła była zachowana w jednym miejscu. Sięgaj po funkcję zdefiniowaną przez użytkownika (UDF) tylko wtedy, gdy funkcja składowana jest mierzalnie zbyt wolna.

FAQ