MySQL Funktioner: Streng, Numerisk, Brugerdefineret, Lagret

Hvad er MySQL Funktioner?

MySQL kan meget mere end blot at gemme og hente data. Det kan også udføre manipulationer på dataene før den hentes eller gemmes. Det er her MySQL Funktioner kommer ind i billedet. Funktioner er simpelthen stykker kode, der udfører en operation og derefter returnerer et resultat. Nogle funktioner accepterer parametre, mens andre funktioner ikke accepterer nogen.

Lad os kort se på et eksempel. Som standard, MySQL gemmer datodatatyper i formatet "ÅÅÅÅ-MM-DD". Antag, at vi har bygget en applikation, og vores brugere ønsker datoen returneret i formatet "DD-MM-ÅÅÅÅ". Vi kan bruge MySQL indbygget funktion DATE_FORMAT for at opnå dette. DATE_FORMAT er en af ​​de mest anvendte funktioner i MySQL, og vi ser nærmere på det senere i denne lektion.

Uanset typen, en funktion returnerer altid en enkelt værdi, kan acceptere nul eller flere parametre inden for parenteser, og kan bruges overalt, hvor et udtryk er tilladt — i en SELECT-liste, en WHERE-klausul eller en ORDER BY-klausul.

Hvorfor brug MySQL Funktioner?

Nu hvor vi ved, hvad en funktion er, er det næste spørgsmål, hvorfor vi overhovedet skal pushe dette arbejde ind i databasen.

Som diagrammet ovenfor viser, tager en funktion en inputværdi, anvender logikken, når den er inde i databaseprogrammet, og sender et enkelt resultat tilbage til hver applikation, der beder om det.

Programmører tænker måske: "Hvorfor besvære sig med MySQL Funktioner? Den samme effekt kan opnås med et script- eller programmeringssprog.” Det er sandt, at vi kan opnå det ved at skrive en procedure i applikationsprogrammet.

For at vende tilbage til vores DATE-eksempel, skal forretningslaget selv udføre den nødvendige behandling for at vores brugere kan få dataene i det ønskede format.

Dette bliver et problem, når applikationen skal integreres med andre systemer. Når vi bruger MySQL funktioner som DATE_FORMAT, er denne funktionalitet integreret i databasen, og enhver applikation, der har brug for dataene, får dem i det nødvendige format. Dette reducerer genarbejde i forretningslogikken og reducerer datainkonsistenser.

Endnu en grund til at overveje MySQL funktioner er, at de kan hjælpe med at reducere netværkstrafik i klient/server-applikationerForretningslaget behøver kun at kalde den lagrede funktion uden at trække rå rækker på tværs af netværket for at manipulere dem. I gennemsnit kan brugen af ​​funktioner forbedre den samlede systemydelse betydeligt.

Typer af MySQL Funktioner

Med "hvad" og "hvorfor" afklaret, kan vi nu se på de tre funktionsfamilier MySQL tilbyder: indbyggede funktioner, gemte funktioner og brugerdefinerede funktioner.

Indbyggede funktioner

MySQL leveres med en række indbyggede funktioner — funktioner, der allerede er implementeret i MySQL server. De giver os mulighed for at udføre mange typer manipulation af dataene og falder ind under følgende almindeligt anvendte grupper.

• String funktioner – operere på strengdatatyper
• Numeriske funktioner – operere på numeriske datatyper
• Dato funktioner – operere på datodatatyper
• Samlede funktioner – operere på alle ovenstående datatyper og producere opsummerede resultatsæt.
• Andre funktioner - MySQL understøtter også andre typer indbyggede funktioner, men vi begrænser denne lektion til de ovenfor nævnte grupper.

Lad os nu se nærmere på hver af de ovennævnte grupper. Vi vil forklare de mest anvendte funktioner ved hjælp af vores eksempeldatabase "Myflixdb".

String funktioner

Strengfunktioner fungerer på tekstværdier. I vores filmtabel gemmes titler ved hjælp af en blanding af små og store bogstaver. Antag, at vi ønsker en forespørgsel, der returnerer titlerne med store bogstaver. Funktionen "UCASE" tager en streng som parameter og konverterer hvert bogstav til store bogstaver, som scriptet nedenfor viser.

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

HER

• UCASE(`titel`) er den indbyggede funktion, der tager titlen som en parameter og returnerer den med store bogstaver.
• Som `stor_bogstav_titel` giver den beregnede kolonne et alias, så resultatsættet har en læsbar header i stedet for det rå udtryk.

Udførelse af ovenstående script i MySQL Workbench mod Myflixdb giver os resultaterne vist nedenfor.

To ledsagere er værd at huske sammen med UCASE: LCASE konverterer en streng til små bogstaver, og KONCAT forbinder to eller flere strenge til én. For den komplette liste, se MySQL strengfunktionsreference.

Numeriske funktioner

Som tidligere nævnt, opererer numeriske funktioner på numeriske datatyper. Vi kan også udføre matematiske beregninger på numeriske data direkte i vores SQL-sætninger.

Aritmetiske operatorer

MySQL understøtter følgende aritmetiske operatorer, som kan bruges til at udføre beregninger i SQL-sætninger.

Eksempler på hver operator følger.

Heltalsdivision (DIV) — DIV kasserer brøkdelen og returnerer kun hele tallet.

SELECT 23 DIV 6;

Udførelse af ovenstående script giver os 3.

Divisionsoperatør (/) — i modsætning til DIV beholder divisionsoperatoren decimaldelen af ​​resultatet.

SELECT 23 / 6;

Udførelse af ovenstående script giver os 3.8333.

Undertractionsoperator (-)

SELECT 23 - 6;

Udførelse af ovenstående script giver os 17.

Tilføjelsesoperator (+)

SELECT 23 + 6;

Udførelse af ovenstående script giver os 29.

Multiplikationsoperator (*)

SELECT 23 * 6 AS `multiplication_result`;

Resultat:

Modulo-operator (% eller MOD)

Modulo-operatoren dividerer N med M og giver os resten. Lad os se på eksemplet med modulo-operatoren, hvor vi bruger de samme værdier som i de foregående eksempler.

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

Udførelse af et af scriptene giver os 5.

Lad os nu se på nogle af de almindelige numeriske funktioner i MySQL.

ETAGE – denne funktion fjerner decimalerne fra et tal og runder det ned til nærmeste hele tal. Scriptet vist nedenfor demonstrerer brugen af ​​det.

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

Resultat:

ROUND – denne funktion afrunder et tal til nærmeste hele tal. Fordi 23 / 6 evalueres til 3.8333, returnerer ROUND 4, mens FLOOR returnerer 3 — de to er ikke udskiftelige.

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

Resultat:

RAND – denne funktion genererer et tilfældigt tal. Dets værdi ændres hver gang funktionen kaldes. Scriptet nedenfor demonstrerer brugen af ​​det.

SELECT RAND() AS `random_result`;

Dato funktioner

Datofunktioner fungerer på dato- og dato-tidsdatatyper. DATE_FORMAT er den funktion, der løser problemet "ÅÅÅÅ-MM-DD versus DD-MM-ÅÅÅÅ", som er beskrevet i introduktionen.

DATE_FORMAT tager to parametre: datoværdien, der skal formateres, og en formatstreng bygget fra pladsholdere. Scriptet nedenfor returnerer hver udgivelsesdato i den dag-måned-år-stil, som vores brugere har bedt om.

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

De mest anvendte formatpladsholdere er anført nedenfor.

Tre andre datofunktioner optræder konstant i det daglige arbejde:

• CURDATE () returnerer den aktuelle dato som ÅÅÅÅ-MM-DD.
• NU() returnerer den aktuelle dato og tid.
• DATODIFF(d1, d2) returnerer antallet af dage mellem to datoer — grundlaget for enhver rapport om forfalden leje.

For den fulde liste, se MySQL reference til dato- og tidsfunktion.

Gemte funktioner

Indbyggede funktioner dækker de almindelige tilfælde. Når en forretningsregel er mere specifik, skriver vi vores egen – og det er dét, en lagret funktion er til for.

Lagrede funktioner opfører sig ligesom indbyggede funktioner, bortset fra at du selv definerer dem. Når en lagret funktion er oprettet, kan den bruges i SQL-sætninger præcis som enhver anden funktion. Den grundlæggende syntaks er vist nedenfor.

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

HER

• "OPRET FUNKTION sf_name ([parameter(er)])" er obligatorisk og fortæller MySQL server til at oprette en funktion med navnet `sf_name` med valgfrie parametre defineret i parenteserne.
• "RETURNERINGER datatype" er obligatorisk og angiver den datatype, som funktionen returnerer.
• "DETERMINISTISK" erklærer, at funktionen returnerer den samme værdi, når de samme argumenter angives. "IKKE DETERMINISTISK" erklærer det modsatte.
• "BEGYND … SLUT" ombryder den procedurekode, som funktionen udfører.

Antag, at vi vil vide, hvilke lejede film der har overskredet deres returdato. Vi kan oprette en gemt funktion, der accepterer returdatoen som en parameter og sammenligner den med den aktuelle dato på serveren. Hvis den aktuelle dato er senere end returdatoen, er filmen overskredet, og vi returnerer "Ja"; ellers returnerer vi "Nej".

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 — denne funktion må ikke mærkes DETERMINISTISK. Brødteksten kalder CURDATE(), så det samme argument kan returnere "Nej" i dag og "Ja" i morgen. Deklarering af en tidsafhængig funktion DETERMINISTIC vildleder optimeringsværktøjet og er usikkert til sætningsbaseret replikering. IKKE DETERMINISTISK hver gang kroppen kalder CURDATE(), NOW() eller RAND().

Udførelse af ovenstående script opretter den gemte funktion `sf_past_movie_return_date`. Lad os nu teste den.

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

Udførelse af ovenstående script i MySQL Workbench mod myflixdb giver os følgende resultater.

Bemærk de to NULL-rækker. Når `return_date` er NULL, evalueres begge sammenligninger til NULL i stedet for TRUE eller FALSE, så ingen af ​​IF-grenene kører, og funktionen returnerer NULL — det forventede resultat, da en ureturneret film ikke har nogen returdato at sammenligne imod.

Brugerdefinerede funktioner

Når SQL alene ikke er hurtigt nok, MySQL tillader en tredje mulighed. Brugerdefinerede funktioner (UDF'er) skrives i et kompileret sprog som f.eks. C or C++, indbygget i et delt bibliotek og registreret på serveren. Når de er tilføjet, kaldes de ligesom enhver anden funktion. Fordi en UDF kører som native kode inde i serverprocessen, er den egnet til tung beregning - men en fejl i en kan få serveren til at gå ned, så UDF'er bruges langt sjældnere end lagrede funktioner.

Indbyggede vs. gemte vs. brugerdefinerede funktioner: Hvilke skal du bruge?

Alle tre familier returnerer en enkelt værdi og kan kaldes fra enhver SQL-sætning, men de adskiller sig i, hvem der skriver dem, hvor de kører, og hvor stor risiko de indebærer. Tabellen nedenfor opsummerer disse forskelle.

Som en tommelfingerregel bør du starte med en indbygget funktion. Hvis ingen af ​​dem passer, så skriv en gemt funktion, så reglen findes ét sted. Brug kun en UDF, når en gemt funktion er målbart for langsom.

Ofte Stillede Spørgsmål