MySQL Funktionen: Zeichenfolge, Numerisch, Benutzerdefiniert, Gespeichert

Was sind MySQL Funktionen?

MySQL kann viel mehr als nur Daten speichern und abrufen. Es kann auch Manipulationen an den Daten vornehmen bevor es abgerufen oder gespeichert wird. Dort MySQL Funktionen kommen ins Spiel. Funktionen sind einfach Codeabschnitte, die eine Operation ausführen und anschließend ein Ergebnis zurückgeben. Manche Funktionen akzeptieren Parameter, andere hingegen keine.

Betrachten wir kurz ein Beispiel. Standardmäßig gilt: MySQL Speichert Datumsdatentypen im Format „JJJJ-MM-TT“. Angenommen, wir haben eine Anwendung entwickelt und unsere Benutzer wünschen die Datumsrückgabe im Format „TT-MM-JJJJ“. Wir können die MySQL Die integrierte Funktion DATE_FORMAT ermöglicht dies. DATE_FORMAT ist eine der am häufigsten verwendeten Funktionen in MySQLWir werden das später in dieser Lektion genauer betrachten.

Unabhängig von der Art der Funktion Gibt immer einen einzelnen Wert zurück, kann null oder mehr Parameter akzeptieren innerhalb von Klammern, kann überall dort verwendet werden, wo ein Ausdruck zulässig ist. — in einer SELECT-Liste, einer WHERE-Klausel oder einer ORDER BY-Klausel.

Warum verwenden MySQL Funktionen?

Nachdem wir nun wissen, was eine Funktion ist, stellt sich die Frage, warum wir diese Arbeit überhaupt in die Datenbank auslagern sollten.

Wie das obige Diagramm zeigt, nimmt eine Funktion einen Eingabewert entgegen, wendet die Logik einmal innerhalb der Datenbank-Engine an und gibt ein einzelnes Ergebnis an jede Anwendung zurück, die danach fragt.

Programmierer mögen sich fragen: „Warum sich die Mühe machen?“ MySQL Funktionen? Derselbe Effekt lässt sich mit einer Skript- oder Programmiersprache erzielen.“ Es stimmt, dass wir dies erreichen können, indem wir eine Prozedur im Anwendungsprogramm schreiben.

Um auf unser DATUM-Beispiel zurückzukommen: Damit unsere Benutzer die Daten im gewünschten Format erhalten, müsste die Geschäftsschicht die notwendige Verarbeitung selbst durchführen.

Dies wird zu einem Problem, wenn die Anwendung in andere Systeme integriert werden muss. Wenn wir verwenden MySQL Funktionen wie DATE_FORMAT sind in die Datenbank eingebettet, und jede Anwendung, die die Daten benötigt, erhält sie im erforderlichen Format. reduziert Nacharbeiten in der Geschäftslogik und verringert Dateninkonsistenzen..

Ein weiterer Grund zum Nachdenken MySQL Eine ihrer Funktionen besteht darin, dass sie dazu beitragen können, den Netzwerkverkehr in Client/Server-Anwendungen zu reduzieren.Die Geschäftslogikschicht muss lediglich die gespeicherte Funktion aufrufen, ohne Rohdatensätze über das Netzwerk abzurufen und zu bearbeiten. Im Durchschnitt kann die Verwendung von Funktionen die Gesamtleistung des Systems erheblich verbessern.

Arten von MySQL Funktionen

Nachdem wir das „Was“ und das „Warum“ geklärt haben, können wir uns nun die drei Funktionsfamilien ansehen. MySQL Bietet: integrierte Funktionen, gespeicherte Funktionen und benutzerdefinierte Funktionen.

Eingebaute Funktionen

MySQL Es enthält eine Reihe integrierter Funktionen – Funktionen, die bereits in der MySQL Server. Sie ermöglichen uns viele Arten der Datenmanipulation und lassen sich in die folgenden häufig verwendeten Gruppen einteilen.

• String-Funktionen – mit String-Datentypen arbeiten
• Numerische Funktionen – mit numerischen Datentypen arbeiten
• Datumsfunktionen – mit Datumsdatentypen arbeiten
• Aggregierte Funktionen – Bearbeiten Sie alle oben genannten Datentypen und erstellen Sie zusammengefasste Ergebnissätze.
• Weitere Funktionen - MySQL Unterstützt auch andere Arten von integrierten Funktionen, aber wir beschränken diese Lektion auf die oben genannten Gruppen.

Betrachten wir nun jede der oben genannten Gruppen im Detail. Wir werden die am häufigsten verwendeten Funktionen anhand unserer Beispieldatenbank „Myflixdb“ erläutern.

String-Funktionen

Zeichenkettenfunktionen arbeiten mit Textwerten. In unserer Filmtabelle sind die Titel in Groß- und Kleinbuchstaben gespeichert. Angenommen, wir möchten eine Abfrage, die die Titel in Großbuchstaben zurückgibt. Die Funktion „UCASE“ nimmt eine Zeichenkette als Parameter und wandelt jeden Buchstaben in einen Großbuchstaben um, wie das folgende Skript zeigt.

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

HIER KLICKEN

• UCASE(`title`) ist die eingebaute Funktion, die den Titel als Parameter entgegennimmt und ihn in Großbuchstaben zurückgibt.
• AS `upper_case_title` weist der berechneten Spalte einen Alias ​​zu, sodass das Ergebnis-Set anstelle des Rohausdrucks eine lesbare Kopfzeile enthält.

Ausführen des obigen Skripts in MySQL Der Vergleich von Workbench mit Myflixdb liefert die unten dargestellten Ergebnisse.

Neben UCASE sollte man sich zwei weitere Personen merken: LCASE wandelt eine Zeichenkette in Kleinbuchstaben um, und CONCAT Verbindet zwei oder mehr Zeichenketten zu einer einzigen. Die vollständige Liste finden Sie in der MySQL Referenz auf eine Zeichenkettenfunktion.

Numerische Funktionen

Wie bereits erwähnt, arbeiten numerische Funktionen mit numerischen Datentypen. Wir können mathematische Berechnungen mit numerischen Daten auch direkt in unseren SQL-Anweisungen durchführen.

Rechenzeichen

MySQL Unterstützt die folgenden arithmetischen Operatoren, die zur Durchführung von Berechnungen in SQL-Anweisungen verwendet werden können.

Es folgen Beispiele für jeden Operator.

Ganzzahldivision (DIV) — DIV verwirft den Nachkommateil und gibt nur die ganze Zahl zurück.

SELECT 23 DIV 6;

Die Ausführung des obigen Skripts ergibt Folgendes: 3.

Divisionsoperator (/) — Im Gegensatz zu DIV behält der Divisionsoperator den Dezimalteil des Ergebnisses bei.

SELECT 23 / 6;

Die Ausführung des obigen Skripts ergibt Folgendes: 3.8333.

Subtractionsoperator (-)

SELECT 23 - 6;

Die Ausführung des obigen Skripts ergibt Folgendes: 17.

Additionsoperator (+)

SELECT 23 + 6;

Die Ausführung des obigen Skripts ergibt Folgendes: 29.

Multiplikationsoperator (*)

SELECT 23 * 6 AS `multiplication_result`;

Ergebnis:

Modulo-Operator (% oder MOD)

Der Modulo-Operator teilt N durch M und liefert den Rest. Betrachten wir das Beispiel des Modulo-Operators mit denselben Werten wie in den vorherigen Beispielen.

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

Die Ausführung eines der beiden Skripte ergibt Folgendes: 5.

Schauen wir uns nun einige der häufigsten numerischen Funktionen an MySQL.

FLOOR Diese Funktion entfernt die Dezimalstellen einer Zahl und rundet sie auf die nächste ganze Zahl ab. Das unten stehende Skript veranschaulicht ihre Verwendung.

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

Ergebnis:

ROUND Diese Funktion rundet eine Zahl auf die nächste ganze Zahl. Da 23 / 6 den Wert 3.8333 ergibt, gibt ROUND 4 zurück, während FLOOR 3 zurückgibt – die beiden Funktionen sind nicht austauschbar.

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

Ergebnis:

RAND Diese Funktion generiert eine Zufallszahl. Ihr Wert ändert sich bei jedem Funktionsaufruf. Das unten stehende Skript veranschaulicht ihre Verwendung.

SELECT RAND() AS `random_result`;

Datumsfunktionen

Datumsfunktionen arbeiten mit Datums- und Datums-/Uhrzeit-Datentypen. Die Funktion DATE_FORMAT löst das in der Einleitung beschriebene Problem „JJJJ-MM-TT versus TT-MM-JJJJ“.

DATUMSFORMAT Das Skript benötigt zwei Parameter: das zu formatierende Datum und eine Formatzeichenfolge aus Platzhaltern. Das folgende Skript gibt jedes Veröffentlichungsdatum im gewünschten Format Tag-Monat-Jahr zurück.

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

Die am häufigsten verwendeten Formatplatzhalter sind unten aufgeführt.

Drei weitere Datumsfunktionen tauchen ständig im Arbeitsalltag auf:

• CURDATE () Gibt das aktuelle Datum im Format JJJJ-MM-TT zurück.
• JETZT() gibt das aktuelle Datum zurück und Zeit.
• DATEDIFF(d1, d2) Gibt die Anzahl der Tage zwischen zwei Daten zurück – die Grundlage jeder Mahnung wegen überfälliger Miete.

Die vollständige Liste finden Sie unter MySQL Referenz zur Datums- und Zeitfunktion.

Gespeicherte Funktionen

Die integrierten Funktionen decken die häufigsten Anwendungsfälle ab. Wenn eine Geschäftsregel spezifischer ist, schreiben wir unsere eigene – und genau dafür sind gespeicherte Funktionen da.

Gespeicherte Funktionen verhalten sich genau wie integrierte Funktionen, nur dass Sie sie selbst definieren. Nach ihrer Erstellung kann eine gespeicherte Funktion in SQL-Anweisungen genauso verwendet werden wie jede andere Funktion. Die grundlegende Syntax ist unten dargestellt.

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

HIER KLICKEN

• “CREATE FUNCTION sf_name ([Parameter(s)])” ist obligatorisch und teilt die MySQL Der Server soll eine Funktion namens `sf_name` mit optionalen Parametern erstellen, die in Klammern definiert sind.
• „GIBT DATENTYP ZURÜCK“ ist obligatorisch und gibt den Datentyp an, den die Funktion zurückgibt.
• „DETERMINISTISCH“ deklariert, dass die Funktion immer denselben Wert zurückgibt, wenn dieselben Argumente übergeben werden. „NICHT DETERMINISTISCH“ behauptet das Gegenteil.
• „ANFANG … ENDE“ umschließt den prozeduralen Code, den die Funktion ausführt.

Angenommen, wir möchten wissen, welche Leihfilme überfällig sind. Wir können eine gespeicherte Funktion erstellen, die das Rückgabedatum als Parameter entgegennimmt und es mit dem aktuellen Datum auf dem Server vergleicht. Ist das aktuelle Datum nach dem Rückgabedatum, ist der Film überfällig und wir geben „Ja“ zurück; andernfalls geben wir „Nein“ zurück.

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 ;

⚠️ Warnung — Diese Funktion darf nicht als DETERMINISTISCH bezeichnet werden. Der Funktionskörper ruft CURDATE() auf, daher kann dasselbe Argument heute „Nein“ und morgen „Ja“ zurückgeben. Die Deklaration einer zeitabhängigen Funktion als DETERMINISTIC führt den Optimierer in die Irre und ist unsicher für die anweisungsbasierte Replikation. NICHT DETERMINISTISCH immer dann, wenn der Funktionskörper CURDATE(), NOW() oder RAND() aufruft.

Durch Ausführen des obigen Skripts wird die gespeicherte Funktion `sf_past_movie_return_date` erstellt. Testen wir sie nun.

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

Ausführen des obigen Skripts in MySQL Die Workbench-Analyse von myflixdb liefert folgende Ergebnisse.

Beachten Sie die beiden NULL-Zeilen. Wenn `return_date` NULL ist, ergeben beide Vergleiche NULL anstatt TRUE oder FALSE, sodass keiner der IF-Zweige ausgeführt wird und die Funktion NULL zurückgibt – das erwartete Ergebnis, da ein nicht zurückgegebener Film kein Rückgabedatum zum Vergleichen hat.

Benutzerdefinierte Funktionen

Wenn SQL allein nicht schnell genug ist, MySQL ermöglicht eine dritte Option. Benutzerdefinierte Funktionen (UDFs) werden in einer kompilierten Sprache wie z. B. geschrieben. C or C++UDFs werden in eine gemeinsam genutzte Bibliothek eingebunden und beim Server registriert. Nach dem Hinzufügen werden sie wie jede andere Funktion aufgerufen. Da eine UDF als nativer Code innerhalb des Serverprozesses ausgeführt wird, eignet sie sich für rechenintensive Aufgaben – ein Fehler in einer UDF kann jedoch zum Absturz des Servers führen. Daher werden UDFs deutlich seltener verwendet als gespeicherte Funktionen.

Integrierte, gespeicherte und benutzerdefinierte Funktionen: Welche sollten Sie verwenden?

Alle drei Familien liefern einen einzelnen Wert zurück und können von jeder SQL-Anweisung aufgerufen werden. Sie unterscheiden sich jedoch darin, wer sie entwickelt hat, wo sie ausgeführt werden und welches Risiko damit verbunden ist. Die folgende Tabelle fasst diese Unterschiede zusammen.

Als Faustregel gilt: Beginnen Sie mit einer integrierten Funktion. Falls keine passt, schreiben Sie eine benutzerdefinierte Funktion, damit die Regel zentral definiert ist. Greifen Sie erst dann auf eine benutzerdefinierte Funktion zurück, wenn eine integrierte Funktion nachweislich zu langsam ist.

Häufig gestellte Fragen