Nützliche MySQL Funktionen - Concat, If, Date_Format, Inet_Aton, Inet

Nützliche MySQL Funktionen

Nützliche MySQL Funktionen die jeder Anfänger kennen sollte

In diesem kleinen Artikel möchte ich dem angehenden Programmierer einige sehr nützliche MySQL Funktionen vorstellen, die einem das Leben erleichtern, Tipparbeit sparen und zum Teil die Ausführung des Scripts deutlich beschleunigen können.

Als PHP Programmierer stolpert man zwangsläufig ständig über MySQL. Was viele, speziell Anfänger, nicht wissen ist, dass MySQL eine riesige Fülle an eigenen Funktionen bereit hält, die zum Teil extrem viel schneller arbeiten, als deren PHP Pendant. Ungeübertere Programmierer beschränken ihre MySQL-Exkursionen meist auf simple INSERT oder SELECT Anweisungen, ziehen Daten aus der Datenbank und verarbeiten diese dann mit PHP. Dabei werden nicht selten abenteuerliche Keulen ausgepackt um z.B. ein Timestamp oder Datum im MySQL Format in ein Deutsches Datum umzuwandeln oder man möchte ganz simpel einen Status (Ja oder Nein) ermitteln und packt dann irgend welche IF-Konstrukte aus.

Das vieles davon aber direkt mit MySQL nicht nur viel einfacher, sondern zudem auch noch viel performanter geht, ist den meisten nicht bewusst. Das wird sich jetzt aber ändern! ;-)

CONCAT
Concat ist die Kurzform von Concatenate, was so viel bedeutet wie verknüpfen. Es handelt sich also um eine String-Funktion um freien Text oder auch Felder aus einem Datensatz zu verknüpfen.
Habe ich in meiner Tabelle beispielweise 2 Felder für Vorwahl (0123) und die Rufnummer (456789), kann ich mir beides als ein Wert im Format 0123/456789 zurückgeben lassen.

$sql = "SELECT CONCAT(`vorwahl`, '/', `rufnummer`) as `telefonnummer`
        FROM `tabelle`";

DATE_FORMAT
Um sich ein Datum aus einem englischen MySQL Format oder von einem Timestamp im deutschen Format zurückliefern zu lassen, bedient man sich einfach der DATE_FORMAT Funktion:

$sql = "SELECT DATE_FORMAT(`datum`, '%d.%m.%Y') as `datum`
        FROM `tabelle`";

So wird aus einem Datum im Format 2010-03-09 16:04:37 ein Datum im Format 09.03.2010. Eine ausführlichere Übersicht über die Datums- und Zeitfunktionen von MySQL findet ihr ebenfalls auf phpBuddy.eu.

IF
MySQL kennt genau wie PHP eine IF-Anweisung. Diese arbeitet zugegebenermaßen etwas spartanisch, aber erfüllt voll und ganz ihren Zweck.
Angenommen wir haben eine Seite, auf der sich Mitglieder registrieren und anschließend ihr Konto z.B. per Mail freischalten/aktivieren können. Wenn wir nun eine Übersicht erzeugen möchten, welche Mitglieder es gibt und ob deren Account aktiviert wurde -was wir mit einer kleinen Grafik verdeutlichen möchten, grüner Punkt = aktiviert, roter Punkt = inaktiv- wie gehen wir normalerweise vor?
Meistens ruft man, neben den anderen Informationen, das Feld "aktiviert" ab und prüft, welchen Wert es hat und bindet dementsprechend die Grafik ein. Das sieht ganz grob beschrieben etwa so aus:

while ($zeile = mysql_fetch_array( $ergebnis ))
{
    if ($zeile['aktiviert'] == 1)
    {
        echo '<img src="aktiv.png" />';
    }
    elseif ($zeile['aktiviert'] == 0)
    {
        echo '<img src="inaktiv.png" />';
    }
}

Übergeben wir MySQL die Aufgabe festzustellen welchen Status ein Mitglied hat, lässt sich unser Code vereinfachen und übersichtlicher gestalten.

$sql = "SELECT IF(`aktiviert`=1, 'aktiv.png', 'inaktiv.png') as `status`
        FROM `tabelle`";
 
// Die Ausgabe dann...
while ($zeile = mysql_fetch_array( $ergebnis ))
{
    echo '<img src="' . $zeile['status'] . '" />';
}

In der MySQL IF-Funktion ist der 1. Parameter der Ausdruck der über Wahr oder Falsch entscheidet. Ist der Ausdruck Wahr, wird der 2. Parameter als status (as `status`) zurück gegeben, andernfalls liefert MySQL den 3. Parameter als status.

IN
Die Funktion IN ist ausgesprochen praktisch, wenn man in der WHERE Klausel nach mehreren möglichen Übereinstimmungen für ein Feld suchen möchte. Statt umständlich mehrere Kriterien mit OR zu verknüpfen, übergibt man einfach ein Set von Optionen.
(schlechtes) Beispiel mit verknüpften Optionen:

$sql = "SELECT `name`
        FROM `tabelle`
        WHERE `beruf` = 'Maurer' OR `beruf` = 'Tischler' OR `beruf` = 'Fotograf'";

Es werden also Datensätze gesucht, in denen der Beruf Maurer oder Tischler oder Fotograf vorkommt. Eleganter und übersichtlicher geht's so:

$sql = "SELECT `name`
        FROM `tabelle`
        WHERE `beruf` IN('Maurer', 'Tischler', 'Fotograf')";

INET_ATON und INET_NTOA
Diese Funktionen sind equivalent zu den PHP Funktionen ip2long und long2ip und dienen dazu, eine IP4 Adresse in einen Integer Wert umzuwandeln, bzw. den Integer Wert zurück in eine IP. Das hört sich zunächst unspektakulär an, hat es aber speziell für größere Anwendungen, in denen mit IP Adressen gearbeitet wird, in sich.

Um umgewandelte IP Adressen als Integer speichern zu können ist es dringend empfohlen, als Feldtyp in MySQL, INT unsigned (Integer unsigniert) zu verwenden, da es sonst beim zurück konvertieren von IP Adressen über dem Bereich 127.x.x.x zu Probleme kommen kann.

Schauen wir uns zuerst die Anwendungsbeispiele an.
IP als Integer in MySQL speichern:

$sql = "INSERT INTO `tabelle` (`ip`)
        VALUES(INET_ATON('192.168.1.254'))";

Auslesen einer IP, die als INT gespeichert wurde:

$sql = "SELECT INET_NTOA(`ip`) as `ip`
        FROM `tabelle`";

Warum das Ganze mit INT, statt IPs direkt als VARCHAR zu speichern? Nun, zum einen verbrauchen IP Adressen, je nach Bereich, erheblich mehr Speicherplatz als der Integer Wert. Zum anderen, und das ist der wichtigere Aspekt, lassen sich Integer Werte in einer Datenbank erheblich schneller durchsuchen, als andere Datentypen. Mit erheblich schneller ist gemeint, dass je nach Vergleichsdaten und Datentyp ein Durchsuchen von Integer Werten bis zu 100 mal schneller ist, als das Durchsuchen von VARCHAR, TEXT oder gar BLOB. Besonders bei vielen gespeicherten IP Adressen, wie etwa in Logs oder Tracking Anwendungen, macht sich das extrem bemerkbar.

INSERT INTO...ON DUPLICATE KEY
Eine extrem nützliche MySQL Anweisung die versucht einen Datensatz hinzuzufügen, aber falls MySQL merkt das bereits ein Datensatz existiert, der bestimmte Kriterien erfüllt, ein UPDATE auf den existierenden Datensatz ausführt.
Dieses "bestimmte Kriterien" bedeutet, dass eine Übereinstimmung bei einem Primary Key (z.B. ID mit auto_increment) oder bei ein einem Feld, dass als Unique deklariert ist, stattfinden muss.

Nehmen wir noch einmal an, dass wir eine Mitgliedertabelle haben. In dieser Tabelle ist das Feld name als UNIQUE gekennzeichnet. Damit wird verhindert, dass es mehr als einen User mit einem identischen Namen gibt. Jedes Forum, jede Blogsoftware funktioniert nach diesem Prinzip. Nehmen wir ferner an, dass ein Datensatz hinzugefügt werden soll der, sofern der Name nicht existiert, neu angelegt wird, aber falls der Name bereits existiert, nur der Wohnort aktualisiert werden soll. Die MySQL Anweisung dafür sieht etwa so aus:

$sql = "INSERT INTO `tabelle` (`name`, `wohnort`)
        VALUES('Andreas', 'Kaiserslautern')
        ON DUPLICATE KEY UPDATE `wohnort` = 'Kaiserslautern'";

MySQL versucht nun zuerst einen neuen Datensatz hinzuzufügen. Ist das Feld name aber als UNIQUE gekennzeichnet und der Name kommt bereits vor, würde MySQL mit einem Fehler abbrechen. Durch das ON DUPLICATE KEY wird aber die UPDATE Anweisung auf den Datensatz ausgeführt, die dem Feld entspricht, dass den Fehler ausgelöst hat. Hätte Andreas vorher in Frankfurt gewohnt, würde er nachher also in Kaiserslautern wohnen.

REPLACE
Na wer denkt denn da sofort an die PHP Funktion str_replace?? Nicht ganz zuunrecht, denn die MySQL Funktion REPLACE arbeitet ähnlich. Sie nimmt eine Zeichenkette und tauscht diese gegen eine andere aus, beschränkt auf ein bestimmtes Feld natürlich. Diese Funktion ist nicht nur in SELECT Anweisungen nützlich, sondern auch, wenn man en bloc etwas in einer Tabelle mit UPDATE ändern möchte. Hier ein Beispiel, wie wir im Feld farbe beim Auslesen die Farbe Rot in Blau umwandeln:

$sql = "SELECT REPLACE(`farbe`, 'Rot', 'Blau') as `farbe`
        FROM `tabelle`";

Wie die IF-Funktion erwartet REPLACE 3 Parameter. Der 1. Parameter ist das Feld, der 2. Parameter die zu ersetzende Zeichenkette, der 3. Parameter die Ersetzung. Die Daten werden in diesem Beispiel nur beim Auslesen geändert, ohne das der eigentliche Datensatz geändert wird!

Viel Spaß mit den Tipps und bis zum nächsten Mal,
Andreas a.k.a. phpBuddy

Funktionsübersicht und Download