MySQL Funkcije: niz, numerička, korisnički definirana, pohranjena

Što su MySQL Funkcije?

MySQL može učiniti puno više od pukog pohranjivanja i dohvaćanja podataka. Može i vršiti manipulacije podacima prije nego što ga preuzmete ili spremite. Tu je MySQL Tu dolaze funkcije. Funkcije su jednostavno dijelovi koda koji izvršavaju operaciju, a zatim vraćaju rezultat. Neke funkcije prihvaćaju parametre, dok druge funkcije ne prihvaćaju nijedan.

Pogledajmo ukratko primjer. Prema zadanim postavkama, MySQL sprema tipove podataka o datumu u formatu „GGGG-MM-DD“. Pretpostavimo da smo izradili aplikaciju i naši korisnici žele da se datum vrati u formatu „DD-MM-GGGG“. Možemo koristiti MySQL ugrađena funkcija DATE_FORMAT za postizanje ovoga. DATE_FORMAT je jedna od najčešće korištenih funkcija u MySQL, a detaljnije ćemo ga pogledati kasnije u ovoj lekciji.

Bez obzira na vrstu, funkcija uvijek vraća jednu vrijednost, može prihvatiti nula ili više parametara unutar zagrada i može se koristiti bilo gdje gdje je dopušten izraz — u SELECT listi, WHERE klauzuli ili ORDER BY klauzuli.

Zašto koristiti MySQL Funkcije?

Sada kada znamo što je funkcija, sljedeće pitanje je zašto bismo uopće trebali gurati ovaj rad u bazu podataka.

Kao što gornji dijagram pokazuje, funkcija uzima ulaznu vrijednost, primjenjuje logiku unutar mehanizma baze podataka i vraća jedan rezultat svakoj aplikaciji koja ga zatraži.

Programeri možda misle: „Zašto se uopće truditi s MySQL Funkcije? Isti učinak može se postići skriptnim ili programskim jezikom.” Istina je da to možemo postići pisanjem procedure u aplikacijskom programu.

Vraćajući se na naš primjer DATE, da bi naši korisnici dobili podatke u željenom formatu, poslovni sloj bi morao sam obaviti potrebnu obradu.

To postaje problem kada se aplikacija mora integrirati s drugim sustavima. Kada koristimo MySQL funkcije kao što je DATE_FORMAT, ta funkcionalnost je ugrađena u bazu podataka i svaka aplikacija kojoj su potrebni podaci dobiva ih u potrebnom formatu. Ovo smanjuje ponovni rad u poslovnoj logici i smanjuje nedosljednosti podataka.

Još jedan razlog za razmatranje MySQL funkcije je da mogu pomoći u smanjenju mrežnog prometa u klijent/poslužitelj aplikacijamaPoslovni sloj treba samo pozvati pohranjenu funkciju, bez povlačenja sirovih redaka preko mreže radi manipuliranja njima. U prosjeku, korištenje funkcija može uvelike poboljšati ukupne performanse sustava.

Vrste MySQL Funkcije

Nakon što smo riješili „što“ i „zašto“, sada možemo pogledati tri obitelji funkcija. MySQL nudi: ugrađene funkcije, pohranjene funkcije i korisnički definirane funkcije.

Ugrađene funkcije

MySQL dolazi u paketu s nizom ugrađenih funkcija - funkcijama koje su već implementirane u MySQL poslužitelj. Omogućuju nam izvođenje mnogih vrsta manipulacija podacima i spadaju u sljedeće često korištene skupine.

• String funkcije – raditi na tipovima podataka niza
• Numeričke funkcije – raditi s numeričkim tipovima podataka
• Funkcije datuma – raditi na tipovima podataka datuma
• Agregatne funkcije – raditi na svim gore navedenim tipovima podataka i proizvoditi sažete skupove rezultata.
• Ostale funkcije - MySQL također podržava i druge vrste ugrađenih funkcija, ali ovu lekciju ograničavamo na gore navedene grupe.

Pogledajmo sada detaljno svaku od gore navedenih skupina. Objasnit ćemo najčešće korištene funkcije koristeći našu primjernu bazu podataka „Myflixdb“.

String funkcije

Funkcije za prikaz nizova znakova operiraju s tekstualnim vrijednostima. U našoj tablici filmova, naslovi su pohranjeni pomoću kombinacije malih i velikih slova. Pretpostavimo da želimo upit koji vraća naslove velikim slovima. Funkcija "UCASE" uzima niz znakova kao parametar i pretvara svako slovo u veliko slovo, kao što pokazuje skript u nastavku.

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

OVDJE

• UCASE(`naslov`) je ugrađena funkcija koja prima naslov kao parametar i vraća ga velikim slovima.
• AS `veliki_slovni_naslov` daje izračunatom stupcu pseudonim, tako da skup rezultata nosi čitljivo zaglavlje umjesto sirovog izraza.

Izvršavanje gornje skripte u MySQL Workbench u usporedbi s Myflixdb-om daje nam rezultate prikazane u nastavku.

Uz UCASE vrijedi zapamtiti dva suputnika: LCASE pretvara niz u mala slova i CONCAT spaja dva ili više nizova u jedan. Za potpuni popis pogledajte MySQL referenca string funkcije.

Numeričke funkcije

Kao što je ranije spomenuto, numeričke funkcije operiraju s numeričkim tipovima podataka. Matematičke izračune na numeričkim podacima možemo izvoditi i izravno u našim SQL naredbama.

Aritmetički operatori

MySQL podržava sljedeće aritmetičke operatore koji se mogu koristiti za izvođenje izračuna u SQL naredbama.

Slijede primjeri svakog operatora.

Dijeljenje cijelih brojeva (DIV) — DIV odbacuje decimalni dio i vraća samo cijeli broj.

SELECT 23 DIV 6;

Izvršavanje gornjeg skripta nam daje 3.

Operator dijeljenja (/) — za razliku od DIV-a, operator dijeljenja zadržava decimalni dio rezultata.

SELECT 23 / 6;

Izvršavanje gornjeg skripta nam daje 3.8333.

ispodtracoperator cije (-)

SELECT 23 - 6;

Izvršavanje gornjeg skripta nam daje 17.

Operator zbrajanja (+)

SELECT 23 + 6;

Izvršavanje gornjeg skripta nam daje 29.

Operator množenja (*)

SELECT 23 * 6 AS `multiplication_result`;

Rezultat:

Modulo operator (% ili MOD)

Modulo operator dijeli N s M i daje nam ostatak. Pogledajmo primjer modulo operatora, koristeći iste vrijednosti kao u prethodnim primjerima.

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

Izvršavanje bilo kojeg od ovih skripti nam daje 5.

Pogledajmo sada neke od uobičajenih numeričkih funkcija u MySQL.

KAT – ova funkcija uklanja decimalna mjesta iz broja i zaokružuje ga na najbliži cijeli broj. Skript prikazan u nastavku demonstrira njezinu upotrebu.

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

Rezultat:

OKRUGLI – ova funkcija zaokružuje broj na najbliži cijeli broj. Budući da 23 / 6 daje rezultat 3.8333, ROUND vraća 4, dok FLOOR vraća 3 - ta dva nisu zamjenjiva.

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

Rezultat:

RAND – ova funkcija generira slučajni broj. Njegova vrijednost se mijenja svaki put kada se funkcija pozove. Skript prikazan u nastavku demonstrira njegovu upotrebu.

SELECT RAND() AS `random_result`;

Funkcije datuma

Funkcije datuma rade s tipovima podataka datum i datum-vrijeme. DATE_FORMAT je funkcija koja rješava problem "GGGG-MM-DD u odnosu na DD-MM-GGGG" opisan u uvodu.

FORMAT_DATE prima dva parametra: vrijednost datuma za formatiranje i niz formata izgrađen od rezerviranih mjesta. Skripta u nastavku vraća svaki datum izdanja u stilu dan-mjesec-godina koji su naši korisnici tražili.

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

Najčešće korištena rezervirana mjesta za format navedena su u nastavku.

Tri druge funkcije datuma stalno se pojavljuju u svakodnevnom radu:

• CURDATE() vraća trenutni datum kao GGGG-MM-DD.
• SADA() vraća trenutni datum i vrijeme.
• DATUMIRANJE(d1, d2) vraća broj dana između dva datuma - osnovu bilo kojeg izvješća o zakašnjenju s najamninom.

Za potpuni popis pogledajte MySQL referenca funkcije datuma i vremena.

Pohranjene funkcije

Ugrađene funkcije pokrivaju uobičajene slučajeve. Kada je poslovno pravilo specifičnije, pišemo vlastito - i tome služi pohranjena funkcija.

Pohranjene funkcije ponašaju se baš kao ugrađene funkcije, osim što ih sami definirate. Nakon što se stvori, pohranjena funkcija može se koristiti u SQL naredbama baš kao i bilo koja druga funkcija. Osnovna sintaksa prikazana je u nastavku.

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

OVDJE

• „STVORI FUNKCIJU naziv_funkcije ([parametar(i)])“ je obavezno i ​​govori MySQL poslužitelj za stvaranje funkcije pod nazivom `sf_name` s opcionalnim parametrima definiranim unutar zagrada.
• "VRATAK_vrste_podataka" je obavezan i određuje tip podataka koji funkcija vraća.
• “DETERMINISTIČKI” deklarira da funkcija vraća istu vrijednost kad god su navedeni isti argumenti. „NIJE DETERMINISTIČNO“ izjavljuje suprotno.
• „POČETAK … KRAJ“ obavija proceduralni kod koji funkcija izvršava.

Pretpostavimo da želimo znati koji su iznajmljeni filmovi prošli svoj datum povrata. Možemo stvoriti pohranjenu funkciju koja prihvaća datum povrata kao parametar i uspoređuje ga s trenutnim datumom na poslužitelju. Ako je trenutni datum veći od datuma povrata, film je prošao i vraćamo "Da"; inače vraćamo "Ne".

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 ;

⚠️ Upozorenje — nemojte ovu funkciju označavati kao DETERMINISTIČKU. Tijelo poziva CURDATE(), tako da isti argument može vratiti "Ne" danas i "Da" sutra. Deklarisanje vremenski ovisne funkcije DETERMINISTIC zavarava optimizator i nije sigurno za replikaciju temeljenu na naredbama. Koristite NIJE DETERMINISTIČKO kad god tijelo poziva CURDATE(), NOW() ili RAND().

Izvršavanje gornjeg skripta stvara pohranjenu funkciju `sf_past_movie_return_date`. Sada je testirajmo.

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

Izvršavanje gornje skripte u MySQL Workbench na myflixdb daje nam sljedeće rezultate.

Obratite pozornost na dva NULL retka. Kada je `return_date` NULL, obje usporedbe se evaluiraju kao NULL, a ne kao TRUE ili FALSE, pa se nijedna IF grana ne izvršava i funkcija vraća NULL - očekivani rezultat, budući da nevraćeni film nema datum povratka za usporedbu.

Korisnički definirane funkcije

Kada sam SQL nije dovoljno brz, MySQL omogućuje treću mogućnost. Korisnički definirane funkcije (UDF-ovi) napisane su u kompiliranom jeziku kao što je C or C++, ugrađene u dijeljenu biblioteku i registrirane na poslužitelju. Nakon dodavanja, pozivaju se baš kao i svaka druga funkcija. Budući da se UDF izvršava kao izvorni kod unutar procesa poslužitelja, prikladan je za teška izračunavanja - ali greška u jednom od njih može srušiti poslužitelj, pa se UDF-ovi koriste puno rjeđe od pohranjenih funkcija.

Ugrađene vs. pohranjene vs. korisnički definirane funkcije: Koje biste trebali koristiti?

Sve tri obitelji vraćaju jednu vrijednost i mogu se pozvati iz bilo koje SQL naredbe, ali se razlikuju po tome tko ih piše, gdje se izvode i koliki rizik nose. Tablica u nastavku sažima te razlike.

Kao opće pravilo, počnite s ugrađenom funkcijom. Ako nijedna ne odgovara, napišite pohranjenu funkciju tako da se pravilo nalazi na jednom mjestu. Posegnite za UDF-om samo kada je pohranjena funkcija mjerljivo prespora.

Pitanja i odgovori