Neuerungen von MySQL 5: Stored Procedures, Trigger und Views

Mit der Version 5 ist MySQL den großen Datenbanken wie MS-SQL oder Oracle wieder ein Stück näher gerückt. Die wichtigsten neuen Fähigkeiten sollten Sie kennen, denn sie machen Ihre Programme sicherer und effizienter. 

Mit den Stored Procedures von MySQL können Sie Programmcode innerhalb des Datenbankservers ausführen lassen. Das macht MySQL um einiges mächtiger. Ein typisches Beispiel: Bislang erforderte der Umgang mit hierarchischen Datenstrukturen, etwa dem Seitenbaum einer Website, einigen Aufwand in der verwendeten Skriptsprache.

So ist etwa die Abfrage “gib mir den Pfad von einer bestimmten Inhaltsseite hoch bis zur Startseite” in SQL nicht auszudrücken. Denn zur Beantwortung müsste eine variable Anzahl von Joins eingesetzt werden – je nachdem, wie viele Ebenen über der gewünschten Seite existieren.

Mit der Hilfe der Stored Procedures kann man ein kleines Programm schreiben, das das Problem elegant löst. Es geht von der angegebenen Seite aus immer einen Schritt nach oben, bis der Wurzelknoten erreicht ist. Die Id-Nummern der dabei durchlaufenen Seiten werden als String zurückgeliefert. Das aufrufende Programm kann diese Information dann für weitere Auswertungen nutzen, etwa für die Anzeige des Seitenpfades (“Breadcrumbs”) in der Navigation.

Angenommen, der Seitenbaum liegt in der Tabelle pages vor. Alle Seiten besitzen im Feld uid eine eindeutige Id und im Feld pid ist die Id ihrer übergeordneten Seite vermerkt. Die Wurzelseite hat die pid 0. Dann erledigt folgende Funktion die gestellte Aufgabe:

DELIMITER '$';
CREATE FUNCTION f_rootpath (param_id INT) RETURNS
CHAR(255)
BEGIN
   DECLARE retval CHAR(255);
  DECLARE father_id INT;
  SET retval=param_id;
  SET father_id=param_id;
  WHILE father_id<>0 DO
    SELECT pid INTO father_id
    FROM pages WHERE uid=father_id;
    SET retval =
    CONCAT(retval,'-',father_id);
  END WHILE;
  RETURN retval;
END$
DELIMITER ;

Die Funktion führt in einer Schleife solange die Ermittlung der übergeordneten Seite durch, bis sie an der Wurzel angekommen ist und baut dabei in der Variablen retval nach und nach das Ergebnis auf. Das Ganze ließe sich eleganter über eine Rekursion lösen, aber die unterstützt MySQL in der aktuellen Version noch nicht. Ein Trost: Diese iterative Variante ist schneller als eine Rekursion.

Die Änderung des Delimiters vom standardmäßigen Semikolon zu einem Dollarzeichen ist notwendig, sonst würden die trennenden Strichpunkte innerhalb der Funktion von MySQL als Endemarke für den aktuellen Befehl angesehen. Der letzte Befehl stellt wieder den normalen Zustand her.

Um nun beispielsweise den Pfad für die Seite mit der id 11 zu ermitteln, geben Sie ein:

SELECT f_rootpath(11);

Ein Ergebnis könnte dann so aussehen:

+-----------------+
| f_rootpath(11)  |
+-----------------+
| 11-2-1-0        |
+-----------------+
1 row in set (0.00 sec)

Trigger arbeiten automatisch

Wie Stored Procedures sind auch Trigger sind serverseitige Routinen, werden aber nicht vom Programmierer aufgerufen, sondern von einem Event angestoßen, etwa dem Löschen oder Ändern eines Datensatzes.

Ein Beispiel für den Einsatzzweck: Bei einer Kundenverwaltung möchte der Anwender jede Änderung im Datenbestand nachvollziehen können. Sobald also jemand in der Tabelle kunden ein Feld ändert, sollen die vorherigen Feldwerte in der Tabelle kunden_changes vermerkt werden, die denselben Satz an Feldern aufweist und zusätzlich ein Feld für den Zeitpunkt der Änderung besitzt.

Beschränken wir uns bei den Kundendaten auf die Felder id, strasse und

name, dann würde der dazu notwendige Befehl so aussehen:

CREATE TRIGGER kunden_change
AFTER UPDATE ON kunden
FOR EACH ROW
  INSERT INTO kunden_changes
  ( id, name, strasse, datechanged)
  VALUES
  (old.id, old.name, old.strasse, NOW())
;

Sobald nun bestehende Kunden-Einträge geändert werden, startet die Trigger-Prozedur. Für jeden geänderten Satz wird in der Tabelle kunden_changes der Zustand vor der Änderung als neuer Datensatz abgespeichert. Um die Werte vor dem UPDATE zu referenzieren, dient der vordefinierte Präfix old bei den Feldbezeichnern.

Sie könnten sich mit dieser Methode zu jedem Kunden eine Änderungshistorie ausgeben lassen, die auflistet, welche Überarbeitungen in seinen Daten stattgefunden haben.

Andere Einsatzgebiete für Trigger sind das Behandeln von in einer anderen Tabelle gespeicherten Detaildaten. So könnten Sie damit beispielsweise beim Deaktivieren eines Lieferanten automatisch alle seine Artikel im eigenen Sortiment ausblenden lassen.

Views vereinfachen und schotten ab

Views sind eine weitere Neuerung in MySQL 5. Ein View benimmt sich wie ein Tabelle, tatsächlich steckt dahinter aber eine von Ihnen definierte Abfrage, die ihre Ergebnisse nur wie eine neue Tabelle darstellt.

Ein einfaches Beispiel bei einem Content-Management-System wäre ein

View, der alle derzeit gültigen Artikel ausgibt, also gelöschte oder zeitlich eingeschränkte Artikel unterdrückt. Er könnte so aussehen:

CREATE VIEW v_akt_artikel AS

SELECT * FROM artikel

WHERE deleted=0 AND

publish_until < NOW();

Weil der View sich wie eine Tabelle einsetzen lässt, könnten Sie beispielsweise schreiben:

SELECT * FROM v_akt_artikel WHERE stichwort='Heimwerken';

Auch das Rechtesystem von MySQL behandelt Views wie Tabellen, und erlaubt deshalb eine Freigabe auf View-Ebene. Das ermöglicht zum Beispiel folgendes Szenario: Sie haben geschäftliche Kundendaten in Ihrer Datenbank und möchten die einem Geschäftspartner zugänglich machen. Allerdings soll er manche Felder nicht sehen dürfen, etwa die Umsatzdaten.

Um den eingeschränkten Zugriff zu ermöglichen, definieren Sie einen View, der nur die notwendigen Felder ausgibt und erlauben dem Partner lediglich darauf den Zugriff: 

CREATE VIEW v_kunden_restr AS SELECT

kundennr,name,plz,strasse,ort FROM kunden;

GRANT SELECT ON v_kunden TO 'kunde'@'%' IDENTIFIED BY

'geheim';

Stored Procedures als Abstraktionsebene

Bei Applikationen im großen Stil sichern Stored Procedures die Integrität von Daten. Betrachten Sie beispielsweise das Verarbeiten von Bestellungen in einem Online-Shop. Der Kunde soll eine Bestellung stornieren dürfen, solange sie noch nicht ausgeführt ist. Ein Weg wäre, die Freigabe der bestellten Artikel und die Deaktivierung des Datensatzes in der BestellungsTabelle einzeln in der ShopApplikation zu lösen.

Würde man diese Aktionen aber in einer Stored Procedure zusammenfassen, die alle Validierungen durchführt und die Teilaspekte des Stornos erledigt, ergäben sich einige Vorteile. Ein Zugriff von einer anderen Plattform als dem Webshop könnte sich auch dieser existierenden Prozedur bedienen. Man würde sich also ersparen, alle Tabellenoperationen nochmals in einem anderen Kontext zu programmieren.

Varianten serverseitiger Routinen

Genau genommen handelt es sich bei der im Artikel gezeigten Routine f_rootpath() gar nicht um eine Stored Procedure, sondern um eine Stored Function. Diese Variante wurde verwendet, weil für die Aufgabenstellung ein Rückgabewert benötigt wird.

Die echten Procedures werden im Gegensatz dazu mit CREATE PROCEDURE definiert und haben einen leicht abweichenden Aufbau. So verzichten sie beispielsweise auf das RETURN, das bei den Funktionen den Rücksprung zum Aufrufer und die Übermittlung des Funktionswerts bewirkt.

Ein weiterer wichtiger Unterschied: Stored Procedures werden nicht per SELECT sondern über CALL aufgerufen

Rückgabewerte sind hier prinzipiell auch vorgesehen. Dann allerdings müssen Sie SQL-Variablen als Rückgabeparameter einsetzen. Die Verwendung einer als Stored Procedure realisierten sp_rootpath() mit einem zusätzlichen Parameter zur Rückgabe würde dann so aussehen:

CALL sp_rootpath(11,@pfad);

SELECT @pfad; Stored

Procedures mit phpMyAdmin

Mit phpMyAdmin klappt die Definition einer Stored Procedure in der Grundkonfiguration nicht. Erst, wenn Sie vom aktiviertem mysql-Interface auf die neuere Variante mysqli umschalten, lässt sich phpMyAdmin einsetzen.

Dazu ändern Sie in der Datei config.inc.php die Zeile, die die “Server-Extension” definiert. Fügen Sie dort ein “i” hinzu, so dass dort steht:

$cfg['Servers'][$i] ['extension']='mysqli'

mySQL Query-Browser

Noch besser geeignet ist der Query-Browser von MySQL, der Stored Procedures mit Funktionen wie Syntax-Highlighting unterstützt. Dieses kostenlose Programm können Sie einfach von der MySQL-Homepage als Teil des Pakets “GUI-Tools” herunterladen.

Ähnliche Beiträge