MySQL Functies: String, Numeriek, Door gebruiker gedefinieerd, Opgeslagen

Wat zijn MySQL Functies?

MySQL kan veel meer dan alleen gegevens opslaan en ophalen. Het kan ook manipulaties uitvoeren op de gegevens voordat je het ophaalt of opslaat. Dat is waar MySQL Functies komen in beeld. Functies zijn simpelweg stukjes code die een bewerking uitvoeren en vervolgens een resultaat retourneren. Sommige functies accepteren parameters, andere niet.

Laten we kort naar een voorbeeld kijken. Standaard, MySQL Slaat datumgegevenstypen op in het formaat "JJJJ-MM-DD". Stel dat we een applicatie hebben gebouwd en onze gebruikers de datum in het formaat "DD-MM-JJJJ" willen ontvangen. We kunnen hiervoor de volgende methode gebruiken: MySQL Gebruik hiervoor de ingebouwde functie DATE_FORMAT. DATE_FORMAT is een van de meest gebruikte functies in MySQLEn we zullen dit later in deze les in detail bekijken.

Ongeacht het type, een functie retourneert altijd één enkele waarde., kan nul of meer parameters accepteren binnen haakjes, en kan overal worden gebruikt waar een uitdrukking is toegestaan — in een SELECT-lijst, een WHERE-clausule of een ORDER BY-clausule.

Waarom gebruiken MySQL Functies?

Nu we weten wat een functie is, is de volgende vraag waarom we dit werk überhaupt in de database zouden moeten opslaan.

Zoals het bovenstaande diagram laat zien, neemt een functie een invoerwaarde, past de logica eenmaal toe in de database-engine en geeft één enkel resultaat terug aan elke applicatie die erom vraagt.

Programmeurs denken misschien: "Waarom zou ik me daar druk om maken?" MySQL Functies? Hetzelfde effect kan worden bereikt met een script- of programmeertaal.” Het klopt dat we dat kunnen bereiken door een procedure in het applicatieprogramma te schrijven.

Om terug te komen op ons DATE-voorbeeld: om onze gebruikers de gegevens in het gewenste formaat te kunnen geven, zou de bedrijfslogica zelf de nodige verwerking moeten uitvoeren.

Dit wordt een probleem wanneer de applicatie moet integreren met andere systemen. Wanneer wij gebruiken MySQL Functies zoals DATE_FORMAT zorgen ervoor dat die functionaliteit in de database is ingebed, en elke applicatie die de gegevens nodig heeft, krijgt ze in het vereiste formaat. Vermindert herwerk in de bedrijfslogica en vermindert inconsistenties in de gegevens..

Nog een reden om te overwegen MySQL Een van de functies is dat ze kunnen helpen het netwerkverkeer in client/server-applicaties te verminderen.De bedrijfslaag hoeft alleen de opgeslagen functie aan te roepen, zonder ruwe rijen via het netwerk op te halen om ze te bewerken. Gemiddeld genomen kan het gebruik van functies de algehele systeemprestaties aanzienlijk verbeteren.

Types van MySQL Functies

Nu de "wat" en de "waarom" duidelijk zijn, kunnen we de drie functiecategorieën bekijken. MySQL Biedt: ingebouwde functies, opgeslagen functies en door de gebruiker gedefinieerde functies.

Ingebouwde functies

MySQL Het programma wordt geleverd met een aantal ingebouwde functies — functies die al in het programma zijn geïmplementeerd. MySQL server. Ze stellen ons in staat om veel verschillende soorten bewerkingen op de gegevens uit te voeren en vallen in de volgende veelgebruikte groepen.

• String-functies – werken op string-gegevenstypen
• Numerieke functies – werken op numerieke gegevenstypen
• Datumfuncties – werken op datumgegevenstypen
• Geaggregeerde functies – werken op alle bovenstaande gegevenstypen en produceren samengevatte resultaatsets.
• Andere functies - MySQL Het ondersteunt ook andere soorten ingebouwde functies, maar we beperken deze les tot de hierboven genoemde groepen.

Laten we nu elk van de hierboven genoemde groepen in detail bekijken. We zullen de meest gebruikte functies toelichten aan de hand van onze voorbeelddatabase "Myflixdb".

String-functies

Stringfuncties werken met tekstwaarden. In onze tabel met films worden titels opgeslagen met een mix van kleine en hoofdletters. Stel dat we een query willen die de titels in hoofdletters retourneert. De functie "UCASE" neemt een string als parameter en converteert elke letter naar een hoofdletter, zoals het onderstaande script laat zien.

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

HIER

• UCASE(`title`) is de ingebouwde functie die de titel als parameter accepteert en deze in hoofdletters retourneert.
• AS `upper_case_title` Geeft de berekende kolom een ​​alias, zodat de resultaatset een leesbare koptekst bevat in plaats van de ruwe expressie.

Voer het bovenstaande script uit in MySQL Workbench heeft de Myflixdb-database op de juiste manier benaderd en levert de onderstaande resultaten op.

Naast UCASE zijn er nog twee andere bedrijven die het vermelden waard zijn: LCASE converteert een tekenreeks naar kleine letters, en CONCAT Voegt twee of meer tekenreeksen samen tot één. Voor de volledige lijst, zie de MySQL stringfunctie-referentie.

Numerieke functies

Zoals eerder vermeld, werken numerieke functies met numerieke gegevenstypen. We kunnen ook rechtstreeks wiskundige berekeningen uitvoeren op numerieke gegevens in onze SQL-instructies.

Rekenkundige operatoren

MySQL Ondersteunt de volgende rekenkundige operatoren, die gebruikt kunnen worden om berekeningen uit te voeren in SQL-instructies.

Hieronder volgen voorbeelden van elke operator.

Gehele deling (DIV) — DIV negeert het decimale deel en geeft alleen het hele getal terug.

SELECT 23 DIV 6;

Door het bovenstaande script uit te voeren, krijgen we... 3.

Deeloperator (/) — in tegenstelling tot DIV behoudt de delingsoperator het decimale deel van het resultaat.

SELECT 23 / 6;

Door het bovenstaande script uit te voeren, krijgen we... 3.8333.

Subtractie operator (-)

SELECT 23 - 6;

Door het bovenstaande script uit te voeren, krijgen we... 17.

Opteloperator (+)

SELECT 23 + 6;

Door het bovenstaande script uit te voeren, krijgen we... 29.

Vermenigvuldigingsoperator (*)

SELECT 23 * 6 AS `multiplication_result`;

Resultaat:

Modulo-operator (% of MOD)

De modulo-operator deelt N door M en geeft ons de rest. Laten we het voorbeeld met de modulo-operator eens bekijken, met dezelfde waarden als in de vorige voorbeelden.

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

Door een van beide scripts uit te voeren, krijgen we 5.

Laten we nu eens kijken naar enkele veelgebruikte numerieke functies in MySQL.

VERDIEPING Deze functie verwijdert de decimalen uit een getal en rondt het af naar het dichtstbijzijnde hele getal. Het onderstaande script demonstreert het gebruik ervan.

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

Resultaat:

ROUND – Deze functie rondt een getal af naar het dichtstbijzijnde hele getal. Omdat 23 / 6 gelijk is aan 3.8333, geeft ROUND 4 terug, terwijl FLOOR 3 teruggeeft — de twee zijn niet uitwisselbaar.

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

Resultaat:

RAND – Deze functie genereert een willekeurig getal. De waarde ervan verandert elke keer dat de functie wordt aangeroepen. Het onderstaande script demonstreert het gebruik ervan.

SELECT RAND() AS `random_result`;

Datumfuncties

Datumfuncties werken met datum- en datum-tijdgegevenstypen. DATE_FORMAT is de functie die het probleem "JJJJ-MM-DD versus DD-MM-JJJJ" oplost, zoals beschreven in de inleiding.

DATUMNOTATIE De functie vereist twee parameters: de datumwaarde die moet worden geformatteerd en een opmaakstring die is opgebouwd uit plaatsaanduidingen. Het onderstaande script retourneert elke releasedatum in de door onze gebruikers gevraagde dag-maand-jaar-indeling.

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

De meest gebruikte opmaakplaceholders staan ​​hieronder vermeld.

Drie andere datumfuncties komen constant terug in het dagelijkse werk:

• CURDATE () Geeft de huidige datum weer als JJJJ-MM-DD.
• NU() geeft de huidige datum terug en tijd.
• DATEDIFF(d1, d2) Geeft het aantal dagen tussen twee datums weer — de basis van elk rapport over achterstallige huur.

Voor de volledige lijst, zie de MySQL referentie voor datum- en tijdfuncties.

Opgeslagen functies

Ingebouwde functies dekken de meest voorkomende gevallen. Wanneer een bedrijfsregel specifieker is, schrijven we onze eigen regel – en daarvoor dienen opgeslagen functies.

Opgeslagen functies gedragen zich net als ingebouwde functies, met als enige verschil dat je ze zelf definieert. Eenmaal aangemaakt, kan een opgeslagen functie in SQL-instructies worden gebruikt, net als elke andere functie. De basissyntaxis wordt hieronder weergegeven.

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

HIER

• “CREATE FUNCTION sf_name ([parameter(s)])” is verplicht en vertelt de MySQL De server moet een functie aanmaken met de naam `sf_name`, waarbij optionele parameters tussen haakjes zijn gedefinieerd.
• “RETOURNEERT data_type” is verplicht en specificeert het gegevenstype dat de functie retourneert.
• “DETERMINISTISCH” verklaart dat de functie dezelfde waarde retourneert wanneer dezelfde argumenten worden meegegeven. “NIET DETERMINISTISCH” beweert het tegenovergestelde.
• “BEGIN … EINDE” Omhult de procedurele code die de functie uitvoert.

Stel, we willen weten welke gehuurde films de retourdatum hebben overschreden. We kunnen een opgeslagen functie maken die de retourdatum als parameter accepteert en deze vergelijkt met de huidige datum op de server. Als de huidige datum later is dan de retourdatum, is de film te laat en retourneren we "Ja"; anders retourneren we "Nee".

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 ;

⚠️ Waarschuwing — noem deze functie niet DETERMINISTISCH. De functie roept CURDATE() aan, waardoor hetzelfde argument vandaag "Nee" en morgen "Ja" kan retourneren. Het declareren van een tijdsafhankelijke functie als DETERMINISTIC misleidt de optimizer en is onveilig voor op statements gebaseerde replicatie. Gebruik NIET DETERMINISTISCH telkens wanneer de body CURDATE(), NOW() of RAND() aanroept.

Door het bovenstaande script uit te voeren, wordt de opgeslagen functie `sf_past_movie_return_date` aangemaakt. Laten we deze nu testen.

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

Voer het bovenstaande script uit in MySQL Workbench levert de volgende resultaten op bij het gebruik van myflixdb.

Let op de twee NULL-rijen. Wanneer `return_date` NULL is, leveren beide vergelijkingen NULL op in plaats van WAAR of ONWAAR, waardoor geen van beide IF-takken wordt uitgevoerd en de functie NULL retourneert — het verwachte resultaat, aangezien een niet-geretourneerde film geen retourdatum heeft om mee te vergelijken.

Door de gebruiker gedefinieerde functies

Als SQL alleen niet snel genoeg is, MySQL biedt een derde optie. Gebruikersgedefinieerde functies (UDF's) worden geschreven in een gecompileerde taal zoals C or C++Ze worden ingebouwd in een gedeelde bibliotheek en geregistreerd bij de server. Eenmaal toegevoegd, worden ze aangeroepen zoals elke andere functie. Omdat een UDF als native code binnen het serverproces draait, is deze geschikt voor zware berekeningen. Een bug erin kan echter de server laten crashen, waardoor UDF's veel minder vaak worden gebruikt dan opgeslagen functies.

Ingebouwde functies, opgeslagen functies en door de gebruiker gedefinieerde functies: welke moet je gebruiken?

Alle drie de families retourneren één enkele waarde en kunnen vanuit elke SQL-instructie worden aangeroepen, maar ze verschillen in wie ze schrijft, waar ze worden uitgevoerd en hoeveel risico ze met zich meebrengen. De onderstaande tabel vat die verschillen samen.

Begin in principe met een ingebouwde functie. Als die niet geschikt is, schrijf dan een opgeslagen functie zodat de regel op één plek staat. Gebruik een UDF (User-Defined Function) alleen als een opgeslagen functie aantoonbaar te traag is.

Veelgestelde vragen