Ich möchte mit einer Ankündigung beginnen: MariaDB wird auf modern-sql.com fortan als separate Datenbank behandelt.
Der Grund für die Aufnahme in meinen Club der gängigen SQL-Datenbanken0 ist einfach: Obwohl MariaDB ursprünglich als „Zweig von MySQL, der für Benutzer kompatibel mit der Hauptversion ist,“ beschrieben wurde1, haben sich die beiden Produkte in den letzten Jahren merklich auseinander entwickelt. Anfangs haben die Unterschiede vor allem betriebliche Aspekte (inkl. rechtliche Aspekte) betroffen. In den letzten beiden Jahren kam es aber auch bei den SQL-Dialekten zu nennenswerten Unterschieden. Die getrennte Behandlung von MariaDB ist daher unumgänglich geworden.
Weiters steigt die Popularität von MariaDB kontinuierlich an2 und es scheint, als hätte das MariaDB-Team endlich begonnen den SQL-Standard zur Kenntnis zu nehmen. Eigentlich muss ich sagen, dass sie moderne SQL-Standards zur Kenntnis nehmen – nicht den SQL-92-Standard, der inzwischen sechsfach überholt ist.3
MariaDB 10.3 zeigt das auf beeindruckende Weise. Dieser Artikel erklärt das im Detail.
Inhalt:
- Systemversionierte Tabellen
- Die
Values-Klausel - Sequenzgeneratoren
Percentile_discundPercentile_contIntersectundExcept- Zwei-phasen Ausfürung der
Update’sSet-Klausel - Selbstreferenzierende
UpdateundDelete-Anweisungen - Am Rande:
LimitinGroup_Concat - Veranstaltungshinweis: Mein SQL-Training in Wien
Systemversionierte Tabellen
Hattest du jemals die Anforderung, die alten Daten aufzubewahren, wenn du update oder delete verwendest? So ziemlich jede Geschäftsanwendung hat dieses Problem – z. B. bei den Kundenstammdaten. Systemversionierte Tabellen ist eine der Standard-SQL-Funktionen, die das für dich erledigen.4
Der SQL-Standard verwendet rechtsoffene Intervalle5 – gespeichert in „von“ und „bis“ Timestamp-Spalten – um den Gültigkeitszeitraum jeder Zeile darzustellen. Das „System“ in „systemversioniert“ bedeutet, dass diese Zeitstempel von der Datenbank automatisch gesetzt werden, wenn man in eine solche Tabelle schreibt. Die Systemversionierung ist für eine Applikation daher weitgehend transparent.
Die Syntax zum anlegen einer systemversionierten Tabelle ist wie folgt:
CREATE TABLE … (
[ normale Spalten und Constraints ]
, gilt_von TIMESTAMP(6) GENERATED ALWAYS AS ROW START NOT NULL
, gilt_bis TIMESTAMP(6) GENERATED ALWAYS AS ROW END NOT NULL
, PERIOD FOR SYSTEM_TIME (gilt_von, gilt_bis)
) WITH SYSTEM VERSIONINGWie du sehen kannst, werden die zwei Spalten für das Intervall explizit angelegt. Diese Spalten sind grundsätzlich normale Spalte und müssen bei select und insert berücksichtigt (ignoriert) werden6. Das ist aber auch schon das einzige, was bei Systemversionierung für die Applikation nicht transparent ist. Als nächstes werden diese beiden Spalten zu einer Periode namens system_time zusammengefasst. Schlussendlich aktiviert die With System Versioning-Klausel den Automatismus.
Hinweis in eigener Sache
Ich lebe von SQL-Schulungen, SQL-Tuning und Beratung sowie dem Verkauf meines Buches „SQL Performance Explained“. Mehr auf winand.at.
Natürlich kann man Systemversionierung auch für bestehende Tabellen mittels alter aktivieren. Wenn man den Gültigkeitsbereich bereits als rechtsoffenes Intervall gespeichert hat, kann man diese Spalten natürlich verwenden.
Sobald die Systemversionierung aktiviert ist, verwaltet die Datenbank die Spalten der System_time-Periode automatisch. Das bedeutet, dass eine Delete-Anweisung nichts mehr löscht. Sie setzt lediglich das Gültigkeitsende der betroffenen Zeilen auf die Transaktionszeit.7 Update macht das selbe, fügt aber auch eine neue Zeile mit den geänderten Daten und der Transaktionszeit als Gültigkeitsbeginn an. Einmal mehr ist Update nur ein Delete, das von einem Insert gefolgt wird.
Beachte
Die SQL-Standard legt nicht fest, wie die alten Daten physisch gespeichert werden und definiert auch keinen Mechanismus zur Löschung alter Daten.
MariaDB speichert die alten Daten standardmäßig in der selben Tabelle. Die Tabellenpartitionierung wurde jedoch erweitert, sodass man die historische Daten von den aktuellen Daten physisch getrennt speichern kann.
Eine Select-Anweisung auf eine systemversionierte Tabelle liefert standardmäßig nur die aktuellen Daten. Zum Zugriff auf alte Daten wurde die Syntax der From-Klausel erweitert:
FROM <tabelle> FOR SYSTEM_TIME [ AS OF <ts>
| BETWEEN <ts> AND <ts>
| FROM <ts> TO <ts> ]
[ [AS] <neuer name> ]Die neue For System Time-Klausel folgt unmittelbar hinter dem Tabellennamen in der From-Klausel – d. h. ein neuer Name (as <neuer name>) folgt erst danach.
Die As Of-Klausel liefert natürlich die zum angegebenen Zeitpunkt gültigen Daten. Between und From erwarten dagegen jeweils einen Zeitbereich und unterscheiden sich nur darin, ob die Obergrenze inklusive ist (between) oder nicht (from). Die Untergrenze ist in beiden Fällen exklusive.8
Mehr darüber:
- Verwendet
row beginstattrow start - Oracle’s „Flashback“ bietet eine ähnliche Funktion mit proprietärer Syntax
- Ohne Schlüsselwort
for(period system_time (…)) - Die Syntax unterscheidet sich weitreichend
- Platzhalter (
?) werden nicht unterstützt - Ausdrücke werden nicht unterstützt • Ausdrücke werden nicht unterstützt
- Ohne
between symmetric - Ausdrücke werden nicht unterstützt
Row [start|end]verwendet die Laufzeit der Anweisung, nicht die Transaktionszeit •
Die Values-Klausel
Die Values-Klausel ist vermutlich die grundlegendste SQL-Funktion. Die meisten kennen sie vom Insert-Statement. Die Values-Klausel ist jedoch ein eigenständiges Konzept das auch ohne Insert nützlich ist.
Die Values-Klausel ist die Standard-SQL-Syntax für Select ohne From. Darüber hinaus hat die Values-Klausel den großen Vorteil, dass sie auch mehrere Zeilen auf einmal erzeugen kann:
VALUES (<Spalten der Zeile 1>)
, (<Spalten der Zeile 2>)
, …Es ist also nicht nötig, mehrere Select-Abfragen mittels union all zu verbinden.
Im Vergleich zu Select ohne From hat die Values-Klausel aber auch einen Nachteil: man kann den Spalten keinen Namen zuweisen. Dafür muss man entweder auf die From-Klausel oder die With-Klausel zurückgreifen.
SELECT *
FROM ( VALUES ('a', 'b') ) t(a,b)Leider unterstützt MariaDB 10.3 die oben gezeigte Benennung in der From-Klausel nicht. Es bleibt also nur die für diesen Fall etwas sperrige With-Klausel.
Ein weiteres Problem ist die mangelnde Unterstützung der Datenbanken. Obwohl MariaDB 10.3 die Values-Klausel nun unterstützt, wird Select ohne From von den getesteten Datenbanken noch immer besser unterstützt.
Mehr darüber:
- Nur ohne Schlüsselwort
row - Nur mit Schlüsselwort
row:values row('r1c1','r1c2'), row('r2c1', 'r2c2') - Erfordert Spaltennamen in der
With-Klausel:with x (c1,c2) as (values …)• Nur ohne Schlüsselwortrow - Kein Zugriff auf Spalten möglich • Nur ohne Schlüsselwort
row - Kommt in MySQL 8.0 (funktioniert ab 8.0.1). Nur für derived tables
- Akzeptiert weniger Spalten in der a
<derived column list>als die Basistabelle hat - Nicht für normale Tabellen und Views
- Proprietäre Erweiterung (nicht Standard!)
Sequenzgeneratoren
„Ein Sequenzgenerator ist ein Mechanismus zum generieren fortlaufende, exakter numerischer Werte, einer nach dem anderen“9 Sie ähneln Identitätsspalten, sind jedoch nicht an eine Insert-Anweisung in eine bestimmte Tabelle gebunden so dass sie für beliebige Zwecke verwendet werden können. Der Ausdruck next value for <Sequenzname> liefert den nächsten Wert der Sequenz.
Natürlich kann man diesen Ausdruck in einer selbständigen Values-Anweisung verwenden, um einen neuen Wert zu abzurufen.
VALUES (NEXT VALUE FOR <Sequenzname>)Mehr darüber:
- Unterstützt die proprietäre Synatx
<Sequenzname>.nextval - Unterstützt die proprietäre Funktion
nextval(<Sequenzname>)(Name ohne Hochkomma) - Unterstützt die proprietäre Funktion
nextval('<sequence name>')(Zeichenkette unter Hochkomma)
Percentile_disc und Percentile_cont
Die Funktionen percentile_disc und percentile_cont liefern Perzentilwerte – z. B. den Median – aus einer sortierten Menge von Werten.
Im SQL-Standard erfordern diese Funktionen die Within Group-Klausel und akzeptieren die Over-Klausel optional. In MariaDB 10.3 ist die Over-Klausel jedoch auch zwingend notwendig.
PRECENTILE_DISC(0.5) WITHIN GROUP (ORDER BY x) OVER …Der Unterschied dieser beiden Funktionen ist der Umgang mit Perzentilwerten die zwischen zwei Zeilen fallen. Zum Beispiel, wenn die mittlere Zeile – mit dem Medianwert – aus vier Zeilen benötigt wird.
Percentile_disc liefert immer einen diskreten Wert aus den Eingangsdaten – selbst wenn das geforderte Perzentil zwischen zwei Zeilen fällt. In diesem Fall wir der Wert der ersten Zeile entsprechend der Order By-Klausel geliefert. Im Gegensatz dazu liefert percentile_cont einen gewichteten Mittelwert der aus den Werten der beiden angrenzenden Zeilen berechnet wird.
MariaDB 10.3 hat auch die proprietäre Funktion median(x) als kürzere Syntax für percentile_disc(0.5) within group (order by x) eingeführt.
Mehr darüber:
PERCENTILE_DISC, PRECENTILE_CONT in der MariaDB Dokumentation
MEDIAN in der MariaDB Dokumentation
Intersect und Except
Intersect und except sind Tabellenoperationen wie union. Anstatt zwei Tabellen aneinander zu hängen, liefern sie die gemeinsamen Zeilen (intersect) respektive die Zeilen der ersten Tabelle reduziert um die der Zweiten (except).
Mehr darüber:
Intersect allwird nicht unterstütztExcept allwird nicht unterstützt- Unterstützt die proprietäre
Minus-Operation
Zwei-phasen Ausfürung der Update’s Set-Klausel
Das lässt sich am besten anhand eines Beispiels erklären. Sieh dir die folgende Abfrage genau an.
UPDATE …
SET c1 = c2
, c2 = c1Beachte, dass beide Spalten auf beiden Seiten des Zuweisungsoperators (=) aufscheinen – beide Spalten werden in der selben Anweisung also gelesen und geändert.
Für diesen Fall definiert der SQL-Standard, dass aus logischer Sicht alle Leseoperationen ausgeführt werden, bevor die Änderungen geschrieben werden. Man kann sich das also wie eine Ausführung in zwei Phasen vorstellen: zuerst eine Phase, in der nur gelesen wird, um die Werte auf der rechten Seite der Zuweisungen zu ermitteln, gefolgt von einer Phase in der diese Werte gespeichert werden. Mit dieser Regel tauscht die Anweisung von oben die Werte der Spalten c1 und c2 aus.
Das verhalten von MySQL und MariaDB (inkl. 10.3) ist in der Standardkonfiguration aber anders. Diese Datenbanken führen die Set-Klausel in der Reihenfolge aus, in der sie in der Anweisung aufscheinen. Daher ist das Ergebnis bei diesen Datenbanken, dass beide Spalten den Wert enthalten, der zuvor in der Spalte c2 gespeichert war.
Der neue SQL-Modus SIMULTANEOUS_ASSIGNMENT von MariaDB 10.3 aktiviert standardkonformes Verhalten für diesen Fall.
SET sql_mode = (SELECT CONCAT( @@sql_mode
, ',SIMULTANEOUS_ASSIGNMENT'
)
)Mehr darüber:
- Nur mit
SQL_MODESIMULTANEOUS_ASSIGNMENT
Selbstreferenzierende Update und Delete-Anweisungen
Dieses Thema ist dem vorherigen sehr ähnlich. Der Unterschied ist, dass das es beim vorherigen Thema um zyklische Referenzen zwischen den Spalten einer Zeile ging, wobei es bei diesem Thema um zyklische Referenzen zwischen verschiedenen Zeilen derselben Tabelle geht.
Dieses Problem tritt auf, wenn eine Anweisung, die Daten ändert, auch eine Abfrage enthält, die auf andere Zeilen derselben Tabelle zugreift. Dabei könnten wiederum zyklische Referenz auftreten – diesmal zwischen Zeilen.
Das folgende Beispiel zeigt den einfachsten Fall:
INSERT INTO t1
SELECT *
FROM t1Bei dieser Anweisung stellt sich die grundsätzliche Frage, ob der Select-Zweig die Zeilen, die bereits eingefügt wurden, schon sehen kann. Die Antwort lautet natürlich „Nein“ – ansonsten entstünde eine Endlosschleife.
Dasselbe Problem kann auch mit den anderen Anweisungen auftreten, die Daten ändern: update, delete und merge. Auch diese Anweisungen müssen dafür eine Abfrage enthalten, die nochmals auf die bearbeitete Tabelle zugreift – z. B. in exists oder in – ansonsten kann kein Zugriff auf andere Zeilen der selben Tabelle erfolgen und somit auch keine zyklische Referenz entstehen.
Der SQL-Standard löst dieses Problem nach dem selben Muster wie bei der Set-Klausel oben besprochen. Aus logischer Sicht muss die Ausführung in eine Nur-Lese- und eine Nur-Schreib-Phase aufgeteilt werden. Diesmal definiert der Standard dieses Verhalten aber als optionale Erweiterung (F781, “Self-referencing operations”). Datenbanken, die diese Erweiterung nicht unterstützen, müssen bei solchen Anweisungen einen Fehler ausgeben.
Und tatsächlich produzieren MySQL und MariaDB (vor 10.3) eine Fehlermeldung:
You can't specify target table 't1' for update in FROM clause
Diese Meldung lautet übrigens immer auf „update“, auch wenn sie bei einer Delete-Anweisung auftritt. Bei MariaDB 10.0 wurde die Fehlermeldung dann geändert:
Table 't1' is specified twice, both as a target for 'UPDATE' and as a separate source for data
MariaDB 10.3 kann selbstreferenzierende Update- und Delete-Anweisungen nun ausführen.10
- Syntax akzeptiert. Funktion nicht im Detail getestet
- Unterstützt die
Merge-Anweisung generell nicht
Am Rande: Limit in Group_Concat
Group_concat ist keine Standard-SQL-Funktion. Die Standard-Funktion dafür ist nämlich listagg. Eine Neuerung bei group_concat möchte ich dennoch kurz erwähnen, da listagg eine andere Lösung für dasselbe Problem hat: wie Verhindert man das Überschreiten des Längenlimits des Ergebnisses?
Listagg bietet dafür die On Overflow-Klausel, die beim erreichen des Längenlimits keine weiteren Werte mehr anhängt. Bei MariaDB’s group_concat kann man nun in der optionalen Limit-Klausel eine Obergrenze für die Anzahl der zu aggregierenden Werte festlegen.
Veranstaltungshinweis: Mein SQL-Training in Wien
Wenn du diesen Artikel bis hier her gelesen hast, willst du bestimmt noch mehr lernen. Wenn das so ist, wirf bitte einen Blick auf meine nächste Schulung in Wien (17.-21. September).
Die Schulung dauert eine volle Woche und behandelt alle Themen die ich in meiner Schulungs- und Beratungslaufbahn immer wieder klarstellen musste. Das beinhaltet die richtige Indizierung, SQL-Grundlagen, die sich kaum jemand zu fragen traut und moderne SQL-Funktionen wie Rekursionen und Window-Funktionen im Detail.
