MySQL Funktsioonid: string, numbriline, kasutaja määratud, salvestatud

Mis on MySQL Funktsioonid?

MySQL saab teha palju enamat kui lihtsalt andmete salvestamine ja toomine. Võib ka andmetega manipuleerimine enne selle taastamist või salvestamist. See on koht, kus MySQL Funktsioonid tulevad mängu. Funktsioonid on lihtsalt koodijupid, mis sooritavad toimingu ja tagastavad seejärel tulemuse. Mõned funktsioonid aktsepteerivad parameetreid, teised aga mitte ühtegi.

Vaatame lühidalt näidet. Vaikimisi MySQL salvestab kuupäeva andmetüübid vormingus „AAAA-KK-PP“. Oletame, et oleme loonud rakenduse ja meie kasutajad soovivad kuupäeva tagastada vormingus „PP-KK-AAAA“. Me saame kasutada MySQL sisseehitatud funktsioon DATE_FORMAT selle saavutamiseks. DATE_FORMAT on üks enimkasutatud funktsioone MySQLja me vaatleme seda selle õppetunni hilisemas osas üksikasjalikumalt.

Olenemata funktsiooni tüübist tagastab alati ühe väärtuse, võib aktsepteerida nulli või rohkem parameetreid sulgudes ja saab kasutada kõikjal, kus avaldis on lubatud — SELECT-loendis, WHERE-klauslis või ORDER BY-klauslis.

Miks kasutada MySQL Funktsioonid?

Nüüd, kui me teame, mis funktsioon on, on järgmine küsimus, miks me peaksime selle töö üldse andmebaasi sisestama.

Nagu ülaltoodud diagramm näitab, võtab funktsioon sisendväärtuse, rakendab loogikat andmebaasimootoris olles ja annab ühe tulemuse tagasi igale rakendusele, mis seda küsib.

Programmeerijad võivad mõelda: „Milleks vaeva näha? MySQL „Funktsioonid? Sama efekti saab saavutada skriptimise või programmeerimiskeelega.” On tõsi, et me saame selle saavutada rakendusprogrammis protseduuri kirjutades.

Tagasi meie DATE näite juurde, et meie kasutajad saaksid andmed soovitud vormingus, peaks ärikiht ise vajaliku töötlemise tegema.

See muutub probleemiks, kui rakendus peab integreeruma teiste süsteemidega. Kui kasutame MySQL funktsioonid nagu DATE_FORMAT, see funktsionaalsus on andmebaasi sisse põimitud ja iga rakendus, mis andmeid vajab, saab need vajalikus vormingus. See vähendab äriloogika ümbertöötlemist ja andmete ebajärjekindlust.

Veel üks põhjus, miks kaaluda MySQL funktsioonideks on see, et need aitavad vähendada võrguliiklust klient-serveri rakendustesÄritasand peab kutsuma ainult salvestatud funktsiooni, ilma et peaks võrgust töötlemata ridu nende manipuleerimiseks üles laadima. Keskmiselt võib funktsioonide kasutamine süsteemi üldist jõudlust oluliselt parandada.

Tüübid MySQL Funktsioonid

Kui „mida“ ja „miks“ on selgitatud, saame nüüd vaadata kolme funktsioonide perekonda. MySQL pakub: sisseehitatud funktsioone, salvestatud funktsioone ja kasutaja määratletud funktsioone.

Sisseehitatud funktsioonid

MySQL on komplektis mitmete sisseehitatud funktsioonidega — funktsioonidega, mis on juba rakenduses rakendatud MySQL server. Need võimaldavad meil andmetega mitut tüüpi manipuleerimist teha ja jagunevad järgmistesse tavaliselt kasutatavatesse rühmadesse.

• Stringi funktsioonid – töötada stringi andmetüüpidega
• Numbrilised funktsioonid – töötada numbriliste andmetüüpidega
• Kuupäeva funktsioonid – kasutada kuupäeva andmetüüpe
• Koondfunktsioonid – kasutada kõiki ülaltoodud andmetüüpe ja koostada summeeritud tulemuste komplektid.
• Muud funktsioonid - MySQL toetab ka muud tüüpi sisseehitatud funktsioone, kuid me piirame seda õppetundi eespool nimetatud rühmadega.

Vaatleme nüüd kõiki ülalmainitud rühmi üksikasjalikumalt. Selgitame enimkasutatud funktsioone meie näidisandmebaasi „Myflixdb” abil.

Stringi funktsioonid

Stringifunktsioonid töötavad tekstiväärtustega. Meie filmide tabelis on pealkirjad salvestatud väike- ja suurtähtede kombinatsioonina. Oletame, et tahame päringut, mis tagastab pealkirjad suurtähtedega. Funktsioon „UCASE” võtab parameetrina stringi ja teisendab iga tähe suurtäheks, nagu allolev skript näitab.

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

SIIN

• UCASE(`pealkiri`) on sisseehitatud funktsioon, mis võtab parameetrina pealkirja ja tagastab selle suurtähtedega.
• AS `suurtäheline_pealkiri` annab arvutatud veerule aliase, seega kannab tulemuste komplekt loetavat päist toore avaldise asemel.

Ülaltoodud skripti käivitamine MySQL Workbench Myflixdb-ga võrreldes annab meile allpool näidatud tulemused.

UCASE kõrval tasub meeles pidada kahte kaaslast: LCASE teisendab stringi väiketähtedeks ja KONKUS ühendab kaks või enam stringi üheks. Täieliku loendi leiate MySQL stringifunktsiooni viide.

Numbrilised funktsioonid

Nagu varem mainitud, töötavad numbrilised funktsioonid numbriliste andmetüüpidega. Samuti saame SQL-lausetes otse numbriliste andmetega matemaatilisi arvutusi teha.

Aritmeetilised operaatorid

MySQL toetab järgmisi aritmeetilisi operaatoreid, mida saab kasutada arvutuste tegemiseks SQL-lausetes.

Iga operaatori näited järgnevad.

Täisarvude jagamine (DIV) — DIV eemaldab murdosa ja tagastab ainult täisarvu.

SELECT 23 DIV 6;

Ülaltoodud skripti käivitamine annab meile 3.

Jaoskonna operaator (/) — erinevalt funktsioonist DIV säilitab jagamisoperaator tulemuse kümnendosa.

SELECT 23 / 6;

Ülaltoodud skripti käivitamine annab meile 3.8333.

allatractsioonioperaator (-)

SELECT 23 - 6;

Ülaltoodud skripti käivitamine annab meile 17.

Lisaoperaator (+)

SELECT 23 + 6;

Ülaltoodud skripti käivitamine annab meile 29.

Korrutustehter (*)

SELECT 23 * 6 AS `multiplication_result`;

Tulemus:

Modulo operaator (% või MOD)

Modulo operaator jagab N M-iga ja annab meile jäägi. Vaatame mooduli operaatori näidet, kasutades samu väärtusi nagu eelmistes näidetes.

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

Mõlema skripti käivitamine annab meile 5.

Vaatame nüüd mõnda levinumat numbrifunktsiooni MySQL.

KORRUS – see funktsioon eemaldab arvust kümnendkohad ja ümardab selle lähima täisarvuni. Allolev skript demonstreerib selle kasutamist.

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

Tulemus:

ROUND – see funktsioon ümardab arvu lähima täisarvuni. Kuna 23 / 6 annab tulemuseks 3.8333, tagastab ROUND 4, samas kui FLOOR tagastab 3 – need kaks arvu ei ole omavahel asendatavad.

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

Tulemus:

RAND – see funktsioon genereerib juhusliku arvu. Selle väärtus muutub iga kord, kui funktsiooni kutsutakse. Allolev skript demonstreerib selle kasutamist.

SELECT RAND() AS `random_result`;

Kuupäeva funktsioonid

Kuupäevafunktsioonid töötavad kuupäeva ja kuupäeva-kellaaja andmetüüpidega. DATE_FORMAT on funktsioon, mis lahendab sissejuhatuses kirjeldatud „AAAA-KK-PP vs PP-KK-AAAA” probleemi.

KUUPÄEVA_VORMING võtab kaks parameetrit: vormindatava kuupäeva väärtuse ja kohatäidetest loodud vormingustringi. Allolev skript tagastab iga väljalaskekuupäeva päev-kuu-aasta stiilis, mida meie kasutajad küsisid.

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

Kõige sagedamini kasutatavad vormingu kohatäited on loetletud allpool.

Igapäevatöös esinevad pidevalt veel kolm kuupäevafunktsiooni:

• KUUPÄEV() tagastab praeguse kuupäeva kujul AAAA-KK-PP.
• KOHE () tagastab praeguse kuupäeva ja aega.
• DATEDIFF(d1, d2) tagastab kahe kuupäeva vahelise päevade arvu – mis on aluseks mis tahes üürivõlgnevuste aruandele.

Täieliku nimekirja leiate aadressilt MySQL kuupäeva ja kellaaja funktsiooni viide.

Salvestatud funktsioonid

Sisseehitatud funktsioonid katavad levinumad juhtumid. Kui ärireegel on spetsiifilisem, kirjutame oma – ja selleks ongi salvestatud funktsioon.

Salvestatud funktsioonid käituvad samamoodi nagu sisseehitatud funktsioonid, välja arvatud see, et defineerite need ise. Kui salvestatud funktsioon on loodud, saab seda SQL-lausetes kasutada täpselt nagu iga teist funktsiooni. Põhisüntaks on näidatud allpool.

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

SIIN

• „LOO FUNKTSIOON sf_nimi ([parameeter(id)])” on kohustuslik ja annab teada MySQL serveril luua funktsioon nimega `sf_name`, mille sulgudes on defineeritud valikulised parameetrid.
• „TAGASTAB andmetüübi” on kohustuslik ja määrab funktsiooni tagastatava andmetüübi.
• "DETERMINISTILINE" deklareerib, et funktsioon tagastab sama väärtuse alati, kui esitatakse samad argumendid. "MITTE DETERMINISTLIK" deklareerib vastupidist.
• „ALGUS … LÕPP” mähib protseduurilise koodi, mida funktsioon käivitab.

Oletame, et tahame teada, millised renditud filmid on tagastuskuupäevast möödas. Saame luua salvestatud funktsiooni, mis võtab parameetrina tagastuskuupäeva ja võrdleb seda serveris oleva praeguse kuupäevaga. Kui praegune kuupäev on tagastuskuupäevast hilisem, on film tagastustähtajast möödas ja tagastame väärtuse „Jah“; vastasel juhul tagastame väärtuse „Ei“.

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 ;

⚠️ Hoiatus — ärge märgistage seda funktsiooni DETERMINISTILISEKS. Funktsiooni CURDATE() sisu kutsub esile, seega sama argument võib täna tagastada „Ei“ ja homme „Jah“. Ajast sõltuva funktsiooni DETERMINISTIC deklareerimine eksitab optimeerijat ja on lausepõhise replikatsiooni jaoks ohtlik. Kasutage EI OLE DETERMINISTLIK iga kord, kui keha kutsub funktsiooni CURDATE(), NOW() või RAND().

Ülaltoodud skripti käivitamine loob salvestatud funktsiooni `sf_past_movie_return_date`. Testime seda nüüd.

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

Ülaltoodud skripti käivitamine MySQL Workbench myflixdb vastu annab meile järgmised tulemused.

Pane tähele kahte NULL rida. Kui `return_date` on NULL, annavad mõlemad võrdlused tulemuseks NULL väärtuse, mitte TRUE või FALSE, seega kumbki IF haru ei käivitu ja funktsioon tagastab NULL väärtuse – oodatava tulemuse, kuna tagastamata filmil pole võrdluskuupäeva.

Kasutaja määratud funktsioonid

Kui ainuüksi SQL-ist ei piisa kiiruseks, MySQL võimaldab kolmandat võimalust. Kasutaja määratletud funktsioonid (UDF-id) on kirjutatud kompileeritavas keeles, näiteks C or C++, mis on jagatud teeki sisse ehitatud ja serveriga registreeritud. Pärast lisamist kutsutakse neid välja nagu iga teist funktsiooni. Kuna UDF töötab serveriprotsessi sees natiivse koodina, sobib see suure arvutusvõimsusega tööks – kuid viga ühes võib serveri krahhi viia, seega kasutatakse UDF-e palju harvemini kui salvestatud funktsioone.

Sisseehitatud vs salvestatud vs kasutaja määratletud funktsioonid: millist peaksite kasutama?

Kõik kolm perekonda tagastavad ühe väärtuse ja neid saab kutsuda mis tahes SQL-lausest, kuid need erinevad selle poolest, kes neid kirjutab, kus neid käivitatakse ja kui suurt riski nad kannavad. Allolev tabel võtab need erinevused kokku.

Rusikareeglina alusta sisseehitatud funktsiooniga. Kui ükski ei sobi, kirjuta salvestatud funktsioon, et reegel asuks ühes kohas. Kasuta UDF-i ainult siis, kui salvestatud funktsioon on mõõdetavalt liiga aeglane.

KKK