MySQL Funksjoner: streng, numerisk, brukerdefinert, lagret

Hva er MySQL Funksjoner?

MySQL kan gjøre mye mer enn bare å lagre og hente data. Det kan også utføre manipulasjoner på dataene før den hentes eller lagres. Det er der MySQL Funksjoner kommer inn. Funksjoner er rett og slett kodebiter som utfører en operasjon og deretter returnerer et resultat. Noen funksjoner godtar parametere, mens andre ikke godtar noen.

La oss kort se på et eksempel. Som standard, MySQL lagrer datodatatyper i formatet «ÅÅÅÅ-MM-DD». Anta at vi har bygget et program og brukerne våre ønsker at datoen skal returneres i formatet «DD-MM-ÅÅÅÅ». Vi kan bruke MySQL innebygd funksjon DATE_FORMAT for å oppnå dette. DATE_FORMAT er en av de mest brukte funksjonene i MySQL, og vi ser nærmere på det senere i denne leksjonen.

Uansett type, en funksjon returnerer alltid én enkelt verdi, kan godta null eller flere parametere i parentes, og kan brukes hvor som helst et uttrykk er tillatt — i en SELECT-liste, en WHERE-klausul eller en ORDER BY-klausul.

Hvorfor bruk MySQL Funksjoner?

Nå som vi vet hva en funksjon er, er det neste spørsmålet hvorfor vi i det hele tatt bør legge dette arbeidet inn i databasen.

Som diagrammet ovenfor viser, tar en funksjon en inngangsverdi, bruker logikken når den er inne i databasemotoren, og sender tilbake et enkelt resultat til hver applikasjon som ber om det.

Programmerere tenker kanskje: «Hvorfor bry seg med MySQL Funksjoner? Den samme effekten kan oppnås med et skript- eller programmeringsspråk.» Det er sant at vi kan oppnå det ved å skrive en prosedyre i applikasjonsprogrammet.

For å gå tilbake til DATE-eksemplet vårt, må forretningslaget gjøre den nødvendige behandlingen selv for at brukerne våre skal få dataene i ønsket format.

Dette blir et problem når applikasjonen skal integreres med andre systemer. Når vi bruker MySQL funksjoner som DATE_FORMAT, den funksjonaliteten er innebygd i databasen, og alle applikasjoner som trenger dataene får dem i ønsket format. Dette reduserer omarbeid i forretningslogikken og reduserer datainkonsekvenser.

Enda en grunn til å vurdere MySQL funksjonene er at de kan bidra til å redusere nettverkstrafikk i klient/server-applikasjonerForretningslaget trenger bare å kalle den lagrede funksjonen, uten å trekke rå rader over nettverket for å manipulere dem. I gjennomsnitt kan bruk av funksjoner forbedre den generelle systemytelsen betraktelig.

Typer av MySQL Funksjoner

Når «hva» og «hvorfor» er avklart, kan vi nå se på de tre funksjonsfamiliene MySQL tilbyr: innebygde funksjoner, lagrede funksjoner og brukerdefinerte funksjoner.

Innebygde funksjoner

MySQL leveres med en rekke innebygde funksjoner – funksjoner som allerede er implementert i MySQL server. De lar oss utføre mange typer manipulering av dataene, og faller inn i følgende vanlige grupper.

• Strengefunksjoner – operere på strengdatatyper
• Numeriske funksjoner – operere på numeriske datatyper
• Datofunksjoner – operere på datodatatyper
• Aggregerte funksjoner – operere på alle de ovennevnte datatypene og produsere oppsummerte resultatsett.
• Andre funksjoner - MySQL støtter også andre typer innebygde funksjoner, men vi begrenser denne leksjonen til gruppene nevnt ovenfor.

La oss nå se nærmere på hver av gruppene nevnt ovenfor. Vi vil forklare de mest brukte funksjonene ved hjelp av vår eksempeldatabase «Myflixdb».

Strengefunksjoner

Strengfunksjoner opererer på tekstverdier. I filmtabellen vår lagres titler ved hjelp av en blanding av små og store bokstaver. Anta at vi ønsker en spørring som returnerer titlene med store bokstaver. «UCASE»-funksjonen tar en streng som parameter og konverterer hver bokstav til store bokstaver, slik skriptet nedenfor viser.

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

HER

• UCASE(`tittel`) er den innebygde funksjonen som tar tittelen som en parameter og returnerer den med store bokstaver.
• Som `stor_bokstav_tittel` gir den beregnede kolonnen et alias, slik at resultatsettet har en lesbar overskrift i stedet for det rå uttrykket.

Utfører skriptet ovenfor i MySQL Workbench mot Myflixdb gir oss resultatene vist nedenfor.

To ledsagere er verdt å huske ved siden av UCASE: LCASE konverterer en streng til små bokstaver, og KONCAT kobler sammen to eller flere strenger til én. For en fullstendig liste, se MySQL strengfunksjonsreferanse.

Numeriske funksjoner

Som nevnt tidligere, opererer numeriske funksjoner på numeriske datatyper. Vi kan også utføre matematiske beregninger på numeriske data direkte i SQL-setningene våre.

Aritmetiske operatører

MySQL støtter følgende aritmetiske operatorer, som kan brukes til å utføre beregninger i SQL-setninger.

Eksempler på hver operator følger.

Heltallsdivisjon (DIV) — DIV forkaster brøkdelen og returnerer bare hele tallet.

SELECT 23 DIV 6;

Å kjøre skriptet ovenfor gir oss 3.

Divisjonsoperatør (/) — i motsetning til DIV beholder divisjonsoperatoren desimaldelen av resultatet.

SELECT 23 / 6;

Å kjøre skriptet ovenfor gir oss 3.8333.

Subtracsjonsoperator (-)

SELECT 23 - 6;

Å kjøre skriptet ovenfor gir oss 17.

Tilleggsoperatør (+)

SELECT 23 + 6;

Å kjøre skriptet ovenfor gir oss 29.

Multiplikasjonsoperator (*)

SELECT 23 * 6 AS `multiplication_result`;

Resultat:

Modulo-operator (% eller MOD)

Modulo-operatoren deler N med M og gir oss resten. La oss se på eksemplet med modulo-operatoren, og bruke de samme verdiene som i de foregående eksemplene.

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

Å kjøre et av skriptene gir oss 5.

La oss nå se på noen av de vanlige numeriske funksjonene i MySQL.

GULV – denne funksjonen fjerner desimalene fra et tall og runder det ned til nærmeste hele tall. Skriptet som vises nedenfor demonstrerer bruken av den.

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

Resultat:

ROUND – denne funksjonen runder et tall til nærmeste hele tall. Fordi 23 / 6 evalueres til 3.8333, returnerer ROUND 4 mens FLOOR returnerer 3 – de to er ikke utskiftbare.

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

Resultat:

RAND – denne funksjonen genererer et tilfeldig tall. Verdien endres hver gang funksjonen kalles. Skriptet nedenfor demonstrerer bruken av det.

SELECT RAND() AS `random_result`;

Datofunksjoner

Datofunksjoner opererer på dato- og dato-klokkeslett-datatyper. DATE_FORMAT er funksjonen som løser problemet «ÅÅÅÅ-MM-DD versus DD-MM-ÅÅÅÅ» som er beskrevet i innledningen.

DATE_FORMAT tar to parametere: datoverdien som skal formateres, og en formatstreng bygget fra plassholdere. Skriptet nedenfor returnerer hver utgivelsesdato i dag-måned-år-stilen brukerne våre ba om.

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

De mest brukte formatplassholderne er listet opp nedenfor.

Tre andre datofunksjoner dukker opp konstant i det daglige arbeidet:

• OPPRETTINGSDATUM() returnerer gjeldende dato som ÅÅÅÅ-MM-DD.
• NÅ() returnerer gjeldende dato og tid.
• DATODIFF(d1, d2) returnerer antall dager mellom to datoer – grunnlaget for enhver rapport om forfalt utleie.

For den fullstendige listen, se MySQL referanse for dato- og klokkeslettfunksjon.

Lagrede funksjoner

Innebygde funksjoner dekker vanlige tilfeller. Når en forretningsregel er mer spesifikk, skriver vi vår egen – og det er det en lagret funksjon er til for.

Lagrede funksjoner oppfører seg akkurat som innebygde funksjoner, bortsett fra at du definerer dem selv. Når en lagret funksjon er opprettet, kan den brukes i SQL-setninger akkurat som enhver annen funksjon. Den grunnleggende syntaksen vises nedenfor.

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

HER

• «OPPRETT FUNKSJON sf_name ([parameter(er)])» er obligatorisk og forteller MySQL server for å opprette en funksjon kalt `sf_name` med valgfrie parametere definert i parentesene.
• «RETURNERINGER datatype» er obligatorisk og angir datatypen som funksjonen returnerer.
• "DETERMINISTISK" erklærer at funksjonen returnerer samme verdi hver gang de samme argumentene oppgis. «IKKE DETERMINISTISK» erklærer det motsatte.
• «BEGYNN … SLUTT» pakker inn prosedyrekoden som funksjonen utfører.

Anta at vi vil vite hvilke leide filmer som har gått ut på returdatoen. Vi kan opprette en lagret funksjon som godtar returdatoen som en parameter og sammenligner den med gjeldende dato på serveren. Hvis gjeldende dato er etter returdatoen, er filmen forfalt, og vi returnerer «Ja»; ellers returnerer vi «Nei».

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 ;

⚠️ Advarsel – ikke merk denne funksjonen DETERMINISTISK. Brødteksten kaller CURDATE(), slik at det samme argumentet kan returnere «Nei» i dag og «Ja» i morgen. Å deklarere en tidsavhengig funksjon DETERMINISTIC villeder optimaliseringen og er utrygt for setningsbasert replikering. IKKE DETERMINISTISK hver gang kroppen kaller CURDATE(), NOW() eller RAND().

Når skriptet ovenfor kjøres, opprettes den lagrede funksjonen `sf_past_movie_return_date`. La oss nå teste den.

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

Utfører skriptet ovenfor i MySQL Arbeidsbenk mot myflixdb gir oss følgende resultater.

Legg merke til de to NULL-radene. Når `return_date` er NULL, evalueres begge sammenligningene til NULL i stedet for TRUE eller FALSE, så ingen av IF-grenene kjører og funksjonen returnerer NULL – det forventede resultatet, siden en ureturnert film ikke har noen returdato å sammenligne mot.

Brukerdefinerte funksjoner

Når SQL alene ikke er raskt nok, MySQL tillater et tredje alternativ. Brukerdefinerte funksjoner (UDF-er) skrives i et kompilert språk som f.eks. C or C++, innebygd i et delt bibliotek og registrert på serveren. Når de er lagt til, kalles de akkurat som alle andre funksjoner. Fordi en UDF kjører som innebygd kode i serverprosessen, egner den seg for tung beregning – men en feil i en kan krasje serveren, så UDF-er brukes mye sjeldnere enn lagrede funksjoner.

Innebygde vs. lagrede vs. brukerdefinerte funksjoner: Hvilken bør du bruke?

Alle tre familiene returnerer én verdi og kan kalles fra en hvilken som helst SQL-setning, men de er forskjellige i hvem som skriver dem, hvor de kjører og hvor mye risiko de medfører. Tabellen nedenfor oppsummerer disse forskjellene.

Som en tommelfingerregel bør du starte med en innebygd funksjon. Hvis ingen av dem passer, skriv en lagret funksjon slik at regelen finnes på ett sted. Bruk bare en UDF når en lagret funksjon er målbart for treg.

Spørsmål og svar