MySQL Funcții: șir, numeric, definit de utilizator, stocat

Care sunt MySQL Funcții?

MySQL poate face mult mai mult decât doar stocarea și preluarea datelor. Poate deasemenea efectuează manipulări asupra datelor înainte de a-l recupera sau salva. Acolo este locul unde MySQL Funcțiile intervin. Funcțiile sunt pur și simplu bucăți de cod care efectuează o operație și apoi returnează un rezultat. Unele funcții acceptă parametri, în timp ce altele nu acceptă niciunul.

Să luăm pe scurt un exemplu. În mod implicit, MySQL salvează tipurile de date de tip dată în formatul „AAAA-LL-ZZ”. Să presupunem că am construit o aplicație și utilizatorii noștri doresc ca data să fie returnată în formatul „ZZ-LL-AAAA”. Putem folosi MySQL funcția încorporată DATE_FORMAT pentru a realiza acest lucru. DATE_FORMAT este una dintre cele mai utilizate funcții în MySQLși vom analiza acest lucru în detaliu mai târziu în această lecție.

Indiferent de tipul său, o funcție returnează întotdeauna o singură valoare, poate accepta zero sau mai mulți parametri între paranteze și poate fi folosit oriunde este permisă o expresie — într-o listă SELECT, o clauză WHERE sau o clauză ORDER BY.

De ce folosiți MySQL Funcții?

Acum, că știm ce este o funcție, următoarea întrebare este de ce ar trebui să inserăm această lucrare în baza de date.

După cum arată diagrama de mai sus, o funcție primește o valoare de intrare, aplică logica odată ce se află în motorul bazei de date și returnează un singur rezultat fiecărei aplicații care îl solicită.

Programatorii s-ar putea gândi: „De ce să te mai deranjezi să MySQL „Funcții? Același efect poate fi obținut cu un limbaj de scripting sau de programare.” Este adevărat că putem obține acest lucru scriind o procedură în programul aplicației.

Revenind la exemplul nostru DATE, pentru ca utilizatorii noștri să obțină datele în formatul dorit, nivelul business ar trebui să efectueze singur procesarea necesară.

Aceasta devine o problemă atunci când aplicația trebuie să se integreze cu alte sisteme. Când folosim MySQL funcții precum DATE_FORMAT, această funcționalitate este încorporată în baza de date și orice aplicație care are nevoie de date le obține în formatul necesar. Aceasta reduce reluarea logicii de business și reduce inconsecvențele datelor.

Un alt motiv de luat în considerare MySQL funcțiile este că pot ajuta la reducerea traficului de rețea în aplicațiile client/serverNivelul business trebuie doar să apeleze funcția stocată, fără a extrage rânduri brute din rețea pentru a le manipula. În medie, utilizarea funcțiilor poate îmbunătăți considerabil performanța generală a sistemului.

Tipuri de MySQL funcţii

Odată ce „ce”-ul și „de ce”-ul sunt clarificate, putem acum analiza cele trei familii de funcții MySQL oferă: funcții încorporate, funcții stocate și funcții definite de utilizator.

Funcții încorporate

MySQL vine la pachet cu o serie de funcții încorporate — funcții deja implementate în MySQL server. Acestea ne permit să efectuăm multe tipuri de manipulări asupra datelor și se încadrează în următoarele grupuri utilizate în mod obișnuit.

• Funcții șiruri – operați pe tipuri de date șir
• Funcții numerice – operați pe tipuri de date numerice
• Funcții de dată – operați pe tipuri de date de date
• Funcții agregate – operați pe toate tipurile de date de mai sus și produceți seturi de rezultate rezumate.
• Alte funcții - MySQL acceptă și alte tipuri de funcții încorporate, dar limităm această lecție la grupurile menționate mai sus.

Să analizăm acum în detaliu fiecare dintre grupurile menționate mai sus. Vom explica cele mai utilizate funcții folosind baza noastră de date exemplu „Myflixdb”.

Funcții șiruri

Funcțiile de tip șir de caractere operează asupra valorilor text. În tabelul nostru de filme, titlurile sunt stocate folosind o combinație de litere mici și majuscule. Să presupunem că dorim o interogare care returnează titlurile cu majuscule. Funcția „UCASE” primește un șir de caractere ca parametru și convertește fiecare literă în majuscule, așa cum demonstrează scriptul de mai jos.

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

AICI

• UCASE(`titlu`) este funcția încorporată care primește titlul ca parametru și îl returnează cu litere majuscule.
• AS `titlu_cu_majuscule` oferă coloanei calculate un alias, astfel încât setul de rezultate poartă un antet lizibil în loc de expresia brută.

Executarea scriptului de mai sus în MySQL Workbench-ul împotriva Myflixdb ne oferă rezultatele prezentate mai jos.

Doi parteneri merită amintiți alături de UCASE: LCASE convertește un șir de caractere în litere mici și CONCAT unește două sau mai multe șiruri de caractere într-unul singur. Pentru lista completă, consultați MySQL referință funcție șir.

Funcții numerice

Așa cum am menționat anterior, funcțiile numerice operează asupra tipurilor de date numerice. De asemenea, putem efectua calcule matematice asupra datelor numerice direct în instrucțiunile noastre SQL.

Operatori aritmetici

MySQL acceptă următorii operatori aritmetici, care pot fi utilizați pentru a efectua calcule în instrucțiunile SQL.

Urmează exemple pentru fiecare operator.

Împărțirea între numere întregi (DIV) — DIV elimină partea fracționară și returnează doar numărul întreg.

SELECT 23 DIV 6;

Executarea scriptului de mai sus ne oferă 3.

Operator de divizie (/) — spre deosebire de DIV, operatorul de împărțire păstrează partea zecimală a rezultatului.

SELECT 23 / 6;

Executarea scriptului de mai sus ne oferă 3.8333.

Subtracoperatorul de țiune (-)

SELECT 23 - 6;

Executarea scriptului de mai sus ne oferă 17.

Operator de adăugare (+)

SELECT 23 + 6;

Executarea scriptului de mai sus ne oferă 29.

operator de multiplicare (*)

SELECT 23 * 6 AS `multiplication_result`;

Rezultat:

Operator modulo (% sau MOD)

Operatorul modulo împarte N la M și ne dă restul. Să analizăm exemplul operatorului modulo, folosind aceleași valori ca în exemplele anterioare.

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

Executarea oricăruia dintre scripturi ne oferă 5.

Să ne uităm acum la unele dintre funcțiile numerice comune în MySQL.

PODEA – această funcție elimină zecimalele dintr-un număr și îl rotunjește la cel mai apropiat număr întreg. Scriptul prezentat mai jos demonstrează utilizarea sa.

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

Rezultat:

RUNDA – această funcție rotunjește un număr la cel mai apropiat număr întreg. Deoarece 23 / 6 se evaluează la 3.8333, ROUND returnează 4, în timp ce FLOOR returnează 3 — cele două nu sunt interschimbabile.

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

Rezultat:

MARGINE – această funcție generează un număr aleator. Valoarea sa se schimbă de fiecare dată când funcția este apelată. Scriptul de mai jos demonstrează utilizarea sa.

SELECT RAND() AS `random_result`;

Funcții de dată

Funcțiile de tip dată operează cu tipuri de date de tip dată și dată-oră. DATE_FORMAT este funcția care rezolvă problema „AAAA-LL-ZZ versus ZZ-LL-AAAA” descrisă în introducere.

FORMATUL DATEI primește doi parametri: valoarea datei de formatat și un șir de format construit din substituenți. Scriptul de mai jos returnează fiecare dată de lansare în stilul zi-lună-an solicitat de utilizatorii noștri.

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

Cele mai frecvent utilizate substituente de format sunt enumerate mai jos.

Alte trei funcții de dată apar constant în munca de zi cu zi:

• CURDATE () returnează data curentă în format AAAA-LL-ZZ.
• ACUM() returnează data curentă și timp.
• DATEDIFF(d1, d2) returnează numărul de zile dintre două date — baza oricărui raport de chirii restante.

Pentru lista completă, consultați MySQL referință funcție dată și oră.

Funcții stocate

Funcțiile încorporate acoperă cazurile comune. Când o regulă de business este mai specifică, ne scriem propria regulă — și acesta este scopul unei funcții stocate.

Funcțiile stocate se comportă exact ca funcțiile predefinite, cu excepția faptului că le definiți singur. Odată creată, o funcție stocată poate fi utilizată în instrucțiuni SQL exact ca orice altă funcție. Sintaxa de bază este prezentată mai jos.

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

AICI

• „CREATE FUNCTION sf_name ([parametru(i)])” este obligatorie și îi spune MySQL server pentru a crea o funcție numită `sf_name` cu parametri opționali definiți în paranteze.
• „RETURNĂ tipul_de_date” este obligatorie și specifică tipul de date returnat de funcție.
• "DETERMINAT" declară că funcția returnează aceeași valoare ori de câte ori sunt furnizate aceleași argumente. „NU DETERMINIST” declară contrariul.
• „ÎNCEPUT … SFÂRȘIT” încapsulează codul procedural pe care funcția îl execută.

Să presupunem că vrem să știm ce filme închiriate au depășit data de returnare. Putem crea o funcție stocată care acceptă data de returnare ca parametru și o compară cu data curentă de pe server. Dacă data curentă este mai mare decât data de returnare, filmul este scadent și returnăm „Da”; altfel returnăm „Nu”.

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 ;

⚠️ Atenție — nu etichetați această funcție drept DETERMINISTICĂ. Corpul apelează CURDATE(), deci același argument poate returna „Nu” astăzi și „Da” mâine. Declararea unei funcții dependente de timp DETERMINISTIC induce în eroare optimizatorul și nu este sigură pentru replicarea bazată pe instrucțiuni. Folosește NU DETERMINISTICE ori de câte ori corpul apelează CURDATE(), NOW() sau RAND().

Executarea scriptului de mai sus creează funcția stocată `sf_past_movie_return_date`. Să o testăm acum.

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

Executarea scriptului de mai sus în MySQL Workbench-ul pentru myflixdb ne dă următoarele rezultate.

Observați cele două rânduri NULL. Când `return_date` este NULL, ambele comparații se evaluează ca NULL în loc de TRUE sau FALSE, deci nicio ramură IF nu rulează și funcția returnează NULL — rezultatul așteptat, deoarece un film nereturnat nu are o dată de returnare cu care să se compare.

Funcții definite de utilizator

Când SQL singur nu este suficient de rapid, MySQL permite o a treia opțiune. Funcțiile definite de utilizator (UDF-uri) sunt scrise într-un limbaj compilat, cum ar fi C or C++, încorporate într-o bibliotecă partajată și înregistrate la server. Odată adăugate, acestea sunt apelate la fel ca orice altă funcție. Deoarece o UDF rulează ca cod nativ în interiorul procesului serverului, este potrivită pentru calcule intense - dar o eroare într-una dintre ele poate bloca serverul, așa că UDF-urile sunt utilizate mult mai rar decât funcțiile stocate.

Funcții încorporate vs. funcții stocate vs. funcții definite de utilizator: pe care ar trebui să le utilizați?

Toate cele trei familii returnează o singură valoare și pot fi apelate din orice instrucțiune SQL, dar diferă prin cine le scrie, unde rulează și cât de mult risc prezintă. Tabelul de mai jos rezumă aceste diferențe.

Ca regulă generală, începeți cu o funcție încorporată. Dacă niciuna nu se potrivește, scrieți o funcție stocată astfel încât regula să rămână într-un singur loc. Folosiți o funcție definită de utilizator (UDF) doar atunci când o funcție stocată este considerabil prea lentă.

Întrebări frecvente