MySQL Funkciók: karakterlánc, numerikus, felhasználó által meghatározott, tárolt

Mik MySQL Függvények?

MySQL sokkal többre képes, mint adatok tárolása és visszakeresése. Az is lehet manipulációkat hajt végre az adatokon mielőtt visszakeresné vagy elmentené. Itt MySQL A függvények jelennek meg. A függvények egyszerűen kódrészletek, amelyek végrehajtanak egy műveletet, majd visszaadnak egy eredményt. Egyes függvények paramétereket fogadnak el, míg mások egyáltalán nem.

Nézzünk röviden egy példát. Alapértelmezés szerint MySQL a dátum adattípusokat „ÉÉÉÉ-HH-NN” formátumban menti. Tegyük fel, hogy létrehoztunk egy alkalmazást, és a felhasználóink ​​„NN-HH-ÉÉÉÉ” formátumban szeretnék visszaadni a dátumot. Használhatjuk a MySQL beépített DATE_FORMAT függvényt használunk ennek eléréséhez. A DATE_FORMAT az egyik leggyakrabban használt függvény a MySQL, és a lecke későbbi részében részletesebben is megvizsgáljuk.

Bármilyen típusú is legyen egy függvény mindig egyetlen értéket ad vissza, nulla vagy több paramétert is elfogadhat zárójelben, és bárhol használható, ahol egy kifejezés megengedett — SELECT listában, WHERE záradékban vagy ORDER BY záradékban.

Miért használja MySQL Függvények?

Most, hogy tudjuk, mi a függvény, a következő kérdés az, hogy miért kellene egyáltalán ezt a munkát beillesztenünk az adatbázisba.

Amint a fenti ábra mutatja, egy függvény fogad egy bemeneti értéket, alkalmazza a logikát, miután belépett az adatbázismotorba, és egyetlen eredményt ad vissza minden olyan alkalmazásnak, amely kéri.

A programozók talán azt gondolják: „Miért is foglalkozzunk vele?” MySQL „Függvények? Ugyanez a hatás érhető el szkriptelési vagy programozási nyelvvel.” Igaz, hogy ezt elérhetjük egy eljárás megírásával az alkalmazásprogramban.

Visszatérve a DATE példánkra, ahhoz, hogy a felhasználóink ​​a kívánt formátumban kapják meg az adatokat, az üzleti rétegnek magának kell elvégeznie a szükséges feldolgozást.

Ez akkor válik problémássá, ha az alkalmazást más rendszerekkel kell integrálni. Amikor használjuk MySQL olyan függvények, mint a DATE_FORMAT, ez a funkció be van ágyazva az adatbázisba, és minden olyan alkalmazás, amelynek szüksége van az adatokra, a kívánt formátumban kapja meg azokat. Ez csökkenti az üzleti logika átdolgozását és az adatinkonzisztenciákat.

Egy másik ok a mérlegelésre MySQL funkciók lényege, hogy segíthetnek csökkenteni a hálózati forgalmat a kliens/szerver alkalmazásokbanAz üzleti rétegnek csak a tárolt függvényt kell meghívnia anélkül, hogy nyers sorokat kellene lekérnie a hálózaton keresztül a manipuláláshoz. A függvények használata átlagosan nagymértékben javíthatja a rendszer teljesítményét.

Típusok MySQL Funkciók

Miután a „mi” és a „miért” kérdéseket tisztáztuk, most már megvizsgálhatjuk a függvények három családját. MySQL kínál: beépített függvényeket, tárolt függvényeket és felhasználó által definiált függvényeket.

Beépített funkciók

MySQL számos beépített függvénnyel érkezik – olyan függvényekkel, amelyek már implementálódtak a MySQL szerver. Lehetővé teszik számunkra, hogy sokféle manipulációt végezzünk az adatokon, és a következő gyakran használt csoportokba sorolhatók.

• String függvények – string adattípusokon működni
• Numerikus függvények – numerikus adattípusokon működni
• Dátum funkciók – dátum adattípusokkal működik
• Összesített függvények – az összes fenti adattípuson működni, és összesített eredményhalmazokat készíteni.
• Egyéb funkciók - MySQL más típusú beépített függvényeket is támogat, de ezt a leckét a fent megnevezett csoportokra korlátozzuk.

Most nézzük meg részletesen a fent említett csoportokat. A leggyakrabban használt függvényeket a „Myflixdb” mintaadatbázisunk segítségével ismertetjük.

String függvények

A karakterláncfüggvények szöveges értékekkel dolgoznak. A filmek táblázatunkban a címek kis- és nagybetűk keverékével vannak tárolva. Tegyük fel, hogy egy olyan lekérdezést szeretnénk, amely nagybetűsként adja vissza a címeket. Az „UCASE” függvény paraméterként egy karakterláncot fogad el, és minden betűt nagybetűvé alakít, ahogy az alábbi szkript is mutatja.

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

ITT

• UCASE(`cím`) a beépített függvény, amely paraméterként veszi a címet, és nagybetűkkel adja vissza.
• AS `nagybetűs_cím` aliast ad a számított oszlopnak, így az eredményhalmaz egy olvasható fejlécet tartalmaz a nyers kifejezés helyett.

A fenti szkript végrehajtása MySQL A Workbench és a Myflixdb összehasonlítása az alábbi eredményeket adja.

Az UCASE mellett két társra érdemes emlékezni: LCASE kisbetűs karakterláncot alakít át, és CONCAT két vagy több karakterláncot egyesít eggyé. A teljes listát lásd a MySQL karakterláncfüggvény-hivatkozás.

Numerikus függvények

Ahogy korábban említettük, a numerikus függvények numerikus adattípusokkal működnek. Matematikai számításokat numerikus adatokon közvetlenül az SQL utasításainkban is elvégezhetünk.

Számtani operátorok

MySQL a következő aritmetikai operátorokat támogatja, amelyekkel SQL utasításokban végezhetők számítások.

Az egyes operátorokra példák következnek.

Egész számokkal való osztás (DIV) — A DIV függvény elveti a tört részt, és csak az egész számot adja vissza.

SELECT 23 DIV 6;

A fenti szkript végrehajtása megadja nekünk 3.

Osztálykezelő (/) – a DIV operátorral ellentétben az osztási operátor az eredmény tizedes részét megtartja.

SELECT 23 / 6;

A fenti szkript végrehajtása megadja nekünk 3.8333.

alatttracciós operátor (-)

SELECT 23 - 6;

A fenti szkript végrehajtása megadja nekünk 17.

Operátor hozzáadása (+)

SELECT 23 + 6;

A fenti szkript végrehajtása megadja nekünk 29.

Szorzási operátor (*)

SELECT 23 * 6 AS `multiplication_result`;

Eredmény:

Modulo operátor (% vagy MOD)

A modulo operátor elosztja N-t M-mel, és megkapjuk a maradékot. Nézzük meg a modulo operátor példáját, ugyanazokat az értékeket használva, mint az előző példákban.

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

Bármelyik szkript végrehajtása megadja nekünk 5.

Nézzünk most meg néhány gyakori numerikus függvényt MySQL.

PADLÓ – ez a függvény eltávolítja a tizedesjegyeket egy számból, és lefelé kerekíti a legközelebbi egész számra. Az alábbi szkript bemutatja a használatát.

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

Eredmény:

FORDULÓ – ez a függvény a legközelebbi egész számra kerekít egy számot. Mivel a 23 / 6 kiértékelése 3.8333-at eredményez, a ROUND 4-et, míg a FLOOR 3-at ad vissza – a kettő nem felcserélhető.

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

Eredmény:

RAND – ez a függvény egy véletlenszámot generál. Az értéke minden alkalommal változik, amikor a függvényt meghívjuk. Az alábbi szkript bemutatja a használatát.

SELECT RAND() AS `random_result`;

Dátum funkciók

A dátumfüggvények dátum és dátum-idő adattípusokkal működnek. A DATE_FORMAT függvény oldja meg a bevezetőben leírt „ÉÉÉÉ-HH-NN vs. NN-HH-ÉÉÉÉ” problémát.

DÁTUM_FORMÁTUM két paramétert fogad el: a formázandó dátumértéket és egy helyőrzőkből létrehozott formázó karakterláncot. Az alábbi szkript minden kiadási dátumot a felhasználóink ​​által kért nap-hónap-év stílusban ad vissza.

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

Az alábbiakban felsoroljuk a leggyakrabban használt formátumhelyőrzőket.

Három másik dátumfüggvény is folyamatosan megjelenik a mindennapi munkában:

• CURDATE() az aktuális dátumot ÉÉÉÉ-HH-NN formátumban adja vissza.
• MOST() visszaadja az aktuális dátumot és a idő.
• DÁTUM.ELTÉRÉS(d1, d2) két dátum között eltelt napok számát adja vissza – ez minden késedelmes bérleti díjról szóló jelentés alapja.

A teljes listát lásd a MySQL dátum és idő függvény referencia.

Tárolt funkciók

A beépített függvények a gyakori eseteket fedik le. Amikor egy üzleti szabály konkrétabb, akkor sajátot írunk – és erre valók a tárolt függvények.

A tárolt függvények ugyanúgy viselkednek, mint a beépített függvények, azzal a különbséggel, hogy a felhasználó definiálja őket. Létrehozás után a tárolt függvények SQL utasításokban pontosan úgy használhatók, mint bármely más függvény. Az alapvető szintaxis alább látható.

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

ITT

• „CREATE FUNCTION sf_name ([paraméter(ek)])” kötelező, és közli a MySQL a szervernek létre kell hoznia egy `sf_name` nevű függvényt, amelynek zárójelben opcionális paramétereket kell megadni.
• „VISSZATÉRÍTÉSI_ARÁNY_ADATTÍPUS” kötelező, és meghatározza a függvény által visszaadott adattípust.
• "MEGHATÁROZÓ" deklarálja, hogy a függvény ugyanazt az értéket adja vissza, valahányszor ugyanazokat az argumentumokat adjuk meg. „NEM DETERMINISTIKUS” az ellenkezőjét hirdeti.
• „KEZDÉS … VÉGE” becsomagolja a függvény által végrehajtott procedurális kódot.

Tegyük fel, hogy tudni szeretnénk, mely kölcsönzött filmek visszavételi határideje múlt. Létrehozhatunk egy tárolt függvényt, amely paraméterként fogadja el a visszavételi dátumot, és összehasonlítja azt a szerveren található aktuális dátummal. Ha az aktuális dátum későbbi, mint a visszavételi dátum, akkor a film lejárt, és „Igen”-t adunk vissza; egyébként „Nem”-et.

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 ;

⚠️ Figyelmeztetés – ne címkézd ezt a függvényt DETERMINISTIC-nek. A törzs meghívja a CURDATE() függvényt, így ugyanaz az argumentum ma „Nem”-et, holnap pedig „Igen”-t adhat vissza. Egy időfüggő DETERMINISTIC függvény deklarálása félrevezeti az optimalizálót, és nem biztonságos az utasításalapú replikációhoz. NEM DETERMINISTA amikor a törzs meghívja a CURDATE(), NOW() vagy RAND() függvényt.

A fenti szkript végrehajtása létrehozza az `sf_past_movie_return_date` tárolt függvényt. Most teszteljük le.

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

A fenti szkript végrehajtása MySQL A Workbench és a myflixdb összehasonlítása a következő eredményeket adja.

Figyeljük meg a két NULL sort. Amikor a `return_date` értéke NULL, mindkét összehasonlítás NULL értéket ad kiértékeléskor TRUE vagy FALSE helyett, így egyik HA ág sem fut le, és a függvény NULL értéket ad vissza – a várt eredményt, mivel egy vissza nem adott filmnek nincs összehasonlítási dátuma.

Felhasználó által definiált funkciók

Amikor az SQL önmagában nem elég gyors, MySQL egy harmadik lehetőséget is lehetővé tesz. A felhasználó által definiált függvények (UDF-ek) fordított nyelven íródnak, például C or C++, egy megosztott könyvtárba vannak beépítve, és regisztrálva vannak a szerveren. Hozzáadás után ugyanúgy meghívódnak, mint bármely más függvény. Mivel egy UDF natív kódként fut a szerverfolyamaton belül, alkalmas nagy számítási igényű feladatokra – de egy hiba az egyikben összeomlaszthatja a szervert, ezért az UDF-eket sokkal ritkábban használják, mint a tárolt függvényeket.

Beépített vs. tárolt vs. felhasználó által definiált függvények: melyiket érdemes használni?

Mindhárom család egyetlen értéket ad vissza, és bármely SQL utasításból meghívhatók, de abban különböznek, hogy ki írja őket, hol futnak, és mekkora kockázatot hordoznak. Az alábbi táblázat összefoglalja ezeket a különbségeket.

Ökölszabályként érdemes beépített függvénnyel kezdeni. Ha egyik sem illik, írj egy tárolt függvényt, hogy a szabály egy helyen legyen. Csak akkor nyúlj UDF-hez, ha egy tárolt függvény mérhetően túl lassú.

GYIK