MySQL Funktioner: Sträng, Numerisk, Användardefinierad, Lagrad

Vad är MySQL Funktioner?

MySQL kan göra mycket mer än att bara lagra och hämta data. Det kan också utföra manipulationer på uppgifterna innan den hämtas eller sparas. Det är där MySQL Funktioner kommer in i bilden. Funktioner är helt enkelt kodbitar som utför en operation och sedan returnerar ett resultat. Vissa funktioner accepterar parametrar, medan andra inte accepterar några.

Låt oss kortfattat titta på ett exempel. Som standard, MySQL sparar datumdatatyper i formatet "ÅÅÅÅ-MM-DD". Anta att vi har byggt en applikation och våra användare vill att datumet ska returneras i formatet "DD-MM-ÅÅÅÅ". Vi kan använda MySQL inbyggd funktion DATE_FORMAT för att uppnå detta. DATE_FORMAT är en av de mest använda funktionerna i MySQL, och vi tittar på det i detalj senare i den här lektionen.

Oavsett typ, en funktion returnerar alltid ett enda värde, kan acceptera noll eller fler parametrar inom parentes, och kan användas var som helst där ett uttryck är tillåtet — i en SELECT-lista, en WHERE-klausul eller en ORDER BY-klausul.

Varför använda MySQL Funktioner?

Nu när vi vet vad en funktion är, är nästa fråga varför vi överhuvudtaget ska lägga in detta arbete i databasen.

Som diagrammet ovan visar tar en funktion ett indatavärde, tillämpar logiken inuti databasmotorn och skickar tillbaka ett enda resultat till varje applikation som frågar efter det.

Programmerare kanske tänker: ”Varför bry sig om MySQL Funktioner? Samma effekt kan uppnås med ett skript- eller programmeringsspråk.” Det är sant att vi kan uppnå det genom att skriva en procedur i applikationsprogrammet.

För att återgå till vårt DATE-exempel, för att våra användare ska få data i önskat format, måste affärslagret självt göra den nödvändiga bearbetningen.

Detta blir ett problem när applikationen måste integreras med andra system. När vi använder MySQL funktioner som DATE_FORMAT, den funktionen är inbäddad i databasen, och alla applikationer som behöver informationen får den i önskat format. Detta minskar omarbete i affärslogiken och minskar datainkonsekvenser.

Ytterligare en anledning att överväga MySQL funktioner är att de kan bidra till att minska nätverkstrafiken i klient/server-applikationerAffärslagret behöver bara anropa den lagrade funktionen, utan att hämta råa rader över nätverket för att manipulera dem. I genomsnitt kan användningen av funktioner förbättra systemets totala prestanda avsevärt.

Typer av MySQL Funktioner

Med "vad" och "varför" avklarade kan vi nu titta på de tre funktionsfamiljerna MySQL erbjuder: inbyggda funktioner, lagrade funktioner och användardefinierade funktioner.

Inbyggda funktioner

MySQL levereras med ett antal inbyggda funktioner – funktioner som redan är implementerade i MySQL server. De låter oss utföra många typer av manipulationer på data och de faller inom följande vanliga grupper.

• Strängfunktioner – arbeta på strängdatatyper
• Numeriska funktioner – arbeta med numeriska datatyper
• Datumfunktioner – arbeta med datumdatatyper
• Aggregerade funktioner – arbeta på alla ovanstående datatyper och producera sammanfattade resultatuppsättningar.
• Övriga funktioner - MySQL stöder även andra typer av inbyggda funktioner, men vi begränsar den här lektionen till grupperna som nämns ovan.

Låt oss nu titta på var och en av de grupper som nämns ovan i detalj. Vi kommer att förklara de mest använda funktionerna med hjälp av vår exempeldatabas "Myflixdb".

Strängfunktioner

Strängfunktioner arbetar med textvärden. I vår filmtabell lagras titlar med en blandning av gemener och versaler. Anta att vi vill ha en fråga som returnerar titlarna med versaler. Funktionen "UCASE" tar en sträng som parameter och konverterar varje bokstav till versaler, vilket skriptet nedan visar.

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

HÄR

• UCASE(`titel`) är den inbyggda funktionen som tar titeln som en parameter och returnerar den med versaler.
• Som `versal_case_titel` ger den beräknade kolumnen ett alias, så resultatmängden har en läsbar rubrik istället för det råa uttrycket.

Exekvera skriptet ovan i MySQL Workbench mot Myflixdb ger oss resultaten som visas nedan.

Två följeslagare är värda att minnas vid sidan av UCASE: LCASE konverterar en sträng till gemener, och KONCAT sammanfogar två eller flera strängar till en. För en fullständig lista, se MySQL strängfunktionsreferens.

Numeriska funktioner

Som tidigare nämnts arbetar numeriska funktioner med numeriska datatyper. Vi kan också utföra matematiska beräkningar på numeriska data direkt i våra SQL-satser.

Aritmetiska operatorer

MySQL stöder följande aritmetiska operatorer, som kan användas för att utföra beräkningar i SQL-satser.

Exempel på varje operator följer.

Heltalsdivision (DIV) — DIV tar bort bråkdelen och returnerar endast hela talet.

SELECT 23 DIV 6;

Att köra ovanstående skript ger oss 3.

Divisionsoperatör (/) — till skillnad från DIV behåller divisionsoperatorn decimaldelen av resultatet.

SELECT 23 / 6;

Att köra ovanstående skript ger oss 3.8333.

Subtractionsoperator (-)

SELECT 23 - 6;

Att köra ovanstående skript ger oss 17.

Tilläggsoperator (+)

SELECT 23 + 6;

Att köra ovanstående skript ger oss 29.

Multiplikationsoperator (*)

SELECT 23 * 6 AS `multiplication_result`;

Resultat:

Modulo-operator (% eller MOD)

Modulooperatorn dividerar N med M och ger oss resten. Låt oss titta på exemplet med modulooperatorn och använda samma värden som i de föregående exemplen.

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

Att köra något av skripten ger oss 5.

Låt oss nu titta på några av de vanliga numeriska funktionerna i MySQL.

GOLV – den här funktionen tar bort decimalerna från ett tal och avrundar det nedåt till närmaste heltal. Skriptet som visas nedan demonstrerar dess användning.

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

Resultat:

RUNT – den här funktionen avrundar ett tal till närmaste heltal. Eftersom 23 / 6 utvärderas till 3.8333 returnerar ROUND 4 medan FLOOR returnerar 3 — de två är inte utbytbara.

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

Resultat:

RAND – den här funktionen genererar ett slumptal. Dess värde ändras varje gång funktionen anropas. Skriptet nedan demonstrerar dess användning.

SELECT RAND() AS `random_result`;

Datumfunktioner

Datumfunktioner fungerar på datatyperna datum och datum-tid. DATE_FORMAT är funktionen som löser problemet "ÅÅÅÅ-MM-DD kontra DD-MM-ÅÅÅÅ" som beskrivs i inledningen.

DATUMFORMAT tar två parametrar: datumvärdet att formatera och en formatsträng som byggs från platshållare. Skriptet nedan returnerar varje utgivningsdatum i den dag-månad-år-format som våra användare begärde.

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

De vanligaste formatplatshållarna listas nedan.

Tre andra datumfunktioner förekommer ständigt i det dagliga arbetet:

• CURDATE () returnerar aktuellt datum som ÅÅÅÅ-MM-DD.
• NU() returnerar det aktuella datumet och tid.
• DATUMDIFF(d1, d2) returnerar antalet dagar mellan två datum — grunden för alla rapporter om försenad hyra.

För den fullständiga listan, se MySQL referens för datum- och tidsfunktion.

Lagrade funktioner

Inbyggda funktioner täcker de vanligaste fallen. När en affärsregel är mer specifik skriver vi vår egen – och det är vad en lagrad funktion är till för.

Lagrade funktioner beter sig precis som inbyggda funktioner, förutom att du definierar dem själv. När en lagrad funktion väl har skapats kan den användas i SQL-satser precis som vilken annan funktion som helst. Den grundläggande syntaxen visas nedan.

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

HÄR

• "SKAPA FUNKTION sf_namn ([parameter(er)])" är obligatorisk och berättar för MySQL servern för att skapa en funktion med namnet `sf_name` med valfria parametrar definierade inom parenteserna.
• "RETURNERINGAR datatyp" är obligatorisk och anger datatypen som funktionen returnerar.
• "DETERMINISTISK" deklarerar att funktionen returnerar samma värde varje gång samma argument anges. "INTE DETERMINISTISK" förkunnar motsatsen.
• "BÖRJA … SLUT" slår in den procedurkod som funktionen exekverar.

Anta att vi vill veta vilka hyrda filmer som har passerat sitt återlämningsdatum. Vi kan skapa en lagrad funktion som accepterar återlämningsdatumet som en parameter och jämför det med det aktuella datumet på servern. Om det aktuella datumet är senare än återlämningsdatumet är filmen försenad och vi returnerar "Ja"; annars returnerar 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 ;

⚠️ Varning — märk inte denna funktion DETERMINISTISK. Kroppen anropar CURDATE(), så samma argument kan returnera "Nej" idag och "Ja" imorgon. Att deklarera en tidsberoende funktion DETERMINISTIC vilseleder optimeraren och är osäkert för replikering baserad på sats. INTE DETERMINISTISKT närhelst kroppen anropar CURDATE(), NOW() eller RAND().

Genom att köra ovanstående skript skapas den lagrade funktionen `sf_past_movie_return_date`. Låt oss nu testa den.

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

Exekvera skriptet ovan i MySQL Workbench mot myflixdb ger oss följande resultat.

Lägg märke till de två NULL-raderna. När `return_date` är NULL, utvärderas båda jämförelserna till NULL snarare än TRUE eller FALSE, så ingen av IF-grenarna körs och funktionen returnerar NULL — det förväntade resultatet, eftersom en film som inte returneras inte har något returdatum att jämföra mot.

Användardefinierade funktioner

När SQL ensamt inte är tillräckligt snabbt, MySQL tillåter ett tredje alternativ. Användardefinierade funktioner (UDF:er) skrivs i ett kompilerat språk som t.ex. C or C++, inbyggda i ett delat bibliotek och registrerade på servern. När de väl har lagts till anropas de precis som vilken annan funktion som helst. Eftersom en UDF körs som nativ kod inuti serverprocessen, passar den tung beräkning – men en bugg i en kan krascha servern, så UDF:er används mycket mer sällan än lagrade funktioner.

Inbyggda vs. lagrade vs. användardefinierade funktioner: Vilka ska du använda?

Alla tre familjer returnerar ett enda värde och kan anropas från vilket SQL-uttryck som helst, men de skiljer sig åt i vem som skriver dem, var de körs och hur stor risk de medför. Tabellen nedan sammanfattar dessa skillnader.

Som en tumregel bör du börja med en inbyggd funktion. Om ingen passar, skriv en lagrad funktion så att regeln finns på ett ställe. Använd endast en UDF när en lagrad funktion är mätbart för långsam.

Vanliga frågor