Der MySQL-Server kann in verschiedenen SQL-Modi betrieben werden und diese Modi auf unterschiedliche Weise für verschiedene Clients anwenden. Diese Funktionalität erlaubt es jeder Anwendung, den Betriebsmodus des Servers an die eigenen Anforderungen anzupassen.
Modi definieren, welche SQL-Syntax MySQL unterstützen soll und welche Art von Gültigkeitsprüfungen in Bezug auf die Daten durchgeführt werden sollen. Dies erleichtert die Verwendung von MySQL in verschiedenen Umgebungen und in Verbindung mit anderen Datenbankservern.
Sie stellen den SQL-Standardmodus ein, indem Sie
mysqld mit der Option
--sql-mode="
starten. modes
"modes
ist hierbei eine
Liste verschiedener Modi, die durch Kommata
(‘,
’) voneinander getrennt
sind. Der Standardwert ist leer (d. h. es sind keine Modi
ausgewählt). Der Wert modes
kann
ebenfalls als leer angegeben werden
(--sql-mode=""
), wenn Sie ihn explizit
löschen wollen.
Sie können den SQL-Modus zur Laufzeit mithilfe der Anweisung
SET [GLOBAL|SESSION]
sql_mode='
zur
Einstellung des Systemwertes modes
'sql_mode
ändern. Die Einstellung der
GLOBAL
-Variable erfordert die Berechtigung
SUPER
und wirkt sich auf den Betrieb aller
Clients aus, die nachfolgend eine Verbindung herstellen. Von
der Änderung der SESSION
-Variable ist nur
der aktuelle Client betroffen. Jeder Client kann jederzeit
seinen eigenen sql_mode
-Sitzungswert
ändern.
Sie können den globalen oder sitzungsspezifischen
sql_mode
-Wert mit den folgenden Anweisungen
ändern:
SELECT @@global.sql_mode; SELECT @@session.sql_mode;
Die wahrscheinlich wichtigsten
sql_mode
-Werte sind die folgenden:
Ändert Syntax und Verhalten so, dass eine höhere Kompatibilität mit Standard-SQL erzielt wird.
Wenn ein Wert nicht wie eingegeben in eine transaktionssichere Tabelle eingefügt werden konnte, wird die Anweisung abgebrochen. Bei nicht transaktionssicheren Tabellen wird die Anweisung abgebrochen, wenn der Wert in einer Anweisung für genau einen Datensatz oder im ersten Datensatz einer Anweisung für mehrere Datensätze erscheint. Weitere Informationen erhalten Sie im Verlauf dieses Abschnitts.
Hierbei verhält sich MySQL wie ein
„traditionelles“ SQL-Datenbanksystem. Eine
einfache Beschreibung dieses Modus wäre: „Gib eine
Fehlermeldung anstelle einer Warnung aus, wenn ein
falscher Wert in eine Spalte eingefügt wird“.
Hinweis: Die Anweisung
INSERT
/UPDATE
wird
abgebrochen, sobald der Fehler bemerkt wird. Wenn Sie eine
nicht transaktionssichere Speicher-Engine verwenden, ist
dies ein unter Umständen unerwünschtes Verhalten, weil
Datenänderungen, die vor dem Fehler ausgeführt wurden,
nicht rückgängig gemacht werden, was zu einer
„unvollständigen“ Aktualisierung führt.
Wenn in diesem Handbuch vom „strikten Modus“ die
Rede ist, bezeichnet dies einen Modus, in dem zumindest
STRICT_TRANS_TABLES
oder
STRICT_ALL_TABLES
aktiviert ist.
Die folgende Liste beschreibt alle unterstützten Modi:
Führt keine vollständige Datumsüberprüfung durch.
Geprüft wird nur, ob die Monatsangabe zwischen 1 und 12
und die Tagesangabe zwischen 1 und 31 liegt. Dies ist sehr
praktisch für Webanwendungen, bei denen man die Jahres-,
Monats- und Tagesangabe drei verschiedenen Feldern
entnimmt und diese Angaben dann genau so gespeichert
werden sollen, wie der Benutzer sie eingegeben hat (d. h.
ohne Plausibilitätsprüfung). Dieser Modus betrifft
DATE
- und
DATETIME
-Spalten. Für
TIMESTAMP
-Spalten gilt er hingegen
nicht, da diese immer ein gültiges Datum erfordern.
Für den Server ist es erforderlich, dass Monats- und
Tagesangaben gültig sind und sich nicht einfach nur in
den Bereichen 1 bis 12 bzw. 1 bis 31 bewegen. Wenn der
strikte Modus deaktiviert ist, werden ungültige Daten wie
'2004-04-31'
in
'0000-00-00'
umgewandelt, und es wird
eine Warnung erzeugt. Ist der strikte Modus hingegen
aktiviert, dann erzeugen ungültige Datumsangaben einen
Fehler. Um derartige Daten zuzulassen, aktivieren Sie
ALLOW_INVALID_DATES
.
Hierbei wird ‘"
’ als
Anführungszeichen für Bezeichner (wie
‘`
’) und nicht als
String-Anführungszeichen behandelt. Auch wenn dieser
Modus aktiviert ist, können Sie
‘`
’ als Anführungszeichen
für Bezeichner verwenden. Ist
ANSI_QUOTES
aktiviert, dann dürfen Sie
doppelte Anführungszeichen für einen literalen String
verwenden, da dieser andernfalls als Bezeichner erkannt
würde.
Erzeugt im strikten Modus einen Fehler (sonst eine
Warnung), wenn bei INSERT
- oder
UPDATE
-Anweisungen eine Division durch
Null (oder MOD(X,0)
) auftritt. Wenn
dieser Modus nicht aktiviert ist, gibt MySQL stattdessen
NULL
als Ergebnis einer Division durch
Null zurück. Bei INSERT IGNORE
oder
UPDATE IGNORE
erzeugt MySQL eine
Warnung bezüglich einer Division durch Null, das Ergebnis
des Vorgangs ist aber NULL
.
Die Vorrangstellung des Operators NOT
besteht darin, dass Ausdrücke wie NOT a BETWEEN
b AND c
als NOT (a BETWEEN b AND
c)
verarbeitet werden. Bei einigen älteren
Versionen von MySQL wurde der Ausdruck hingegen als
(NOT a) BETWEEN b AND c
aufgefasst.
Dieses ältere Vorrangsverhalten kann verwendet werden,
indem man den SQL-Modus
HIGH_NOT_PRECEDENCE
aktiviert.
mysql>SET sql_mode = '';
mysql>SELECT NOT 1 BETWEEN -5 AND 5;
-> 0 mysql>SET sql_mode = 'broken_not';
mysql>SELECT NOT 1 BETWEEN -5 AND 5;
-> 1
Gestattet Leerzeichen zwischen einem Funktionsnamen und
dem Zeichen ‘(
’. Hierdurch
wird die Behandlung aller Funktionsnamen als reservierte
Wörter erzwungen. Dies wiederum bedingt, dass Sie
Datenbank-, Tabellen- oder Spaltennamen, die Sie verwenden
wollen, als referenzierte Wörter in Anführungszeichen
setzen müssen. Weil es beispielsweise eine Funktion
USER()
gibt, sind die Namen der Tabelle
user
in der
mysql
-Datenbank und der Spalte
User
in dieser Tabelle reservierte
Wörter, müssen also in Anführungszeichen gesetzt
werden:
SELECT "User" FROM mysql."user";
Der SQL-Modus IGNORE_SPACE
gilt für
integrierte Funktionen, nicht aber für gespeicherte
Routinen. Nach einem Routinennamen müssen unabhängig
davon, ob IGNORE_SPACE
aktiviert ist
oder nicht, immer Leerzeichen stehen dürfen.
Verhindert, dass GRANT
automatisch neue
Benutzer erstellt, sofern es dies tun würde (es sei denn,
es wird ein nicht leeres Passwort angegeben).
NO_AUTO_VALUE_ON_ZERO
wirkt sich auf
die Verarbeitung von
AUTO_INCREMENT
-Spalten aus.
Normalerweise erzeugen Sie die nächste Sequenznummer für
die Spalte, indem Sie entweder NULL
oder 0
einfügen.
NO_AUTO_VALUE_ON_ZERO
unterdrückt
dieses Verhalten für 0
, sodass nur
NULL
die nächste Sequenznummer
erzeugt.
Dieser Modus kann nützlich sein, wenn
0
in einer
AUTO_INCREMENT
-Spalte einer Tabelle
gespeichert wurde. (Nebenbei gesagt: Das Speichern von
0
ist keine empfehlenswerte
Vorgehensweise.) Wenn Sie beispielsweise die Tabelle mit
mysqldump speichern und sie dann neu
laden, erzeugt MySQL normalerweise neue Sequenznummern,
sobald die 0
-Werte gefunden werden; das
Ergebnis ist also eine Tabelle, deren Inhalt sich von dem
ursprünglich gespeicherten unterscheidet. Die Aktivierung
von NO_AUTO_VALUE_ON_ZERO
vor dem
Neuladen der Speicherauszugsdatei löst dieses Problem.
mysqldump schließt nun automatisch
eine Anweisung in seine Ausgabe mit ein, die
NO_AUTO_VALUE_ON_ZERO
aktiviert, um das
Problem zu vermeiden.
Deaktiviert die Verwendung des Backslashs
(‘\
’) als Escape-Zeichen
innerhalb von Strings. Wenn dieser Modus aktiviert ist,
wird der Backslash zu einem ganz gewöhnlichen Zeichen.
Wenn Sie eine Tabelle erstellen, werden in diesem Modus
alle INDEX DIRECTORY
- und DATA
DIRECTORY
-Direktiven ignoriert. Diese Option ist
praktisch bei Slave-Replikationsservern.
NO_ENGINE_SUBSTITUTION
Verhindert die automatische Ersetzung der vorgabeseitigen
Speicher-Engine, wenn eine Anweisung wie CREATE
TABLE
eine Speicher-Engine angibt, die
deaktiviert oder nicht einkompiliert ist.
Sorgt dafür, dass MySQL-spezifische Spaltenoptionen in
der Ausgabe von SHOW CREATE TABLE
nicht
angegeben werden. Dieser Modus wird von
mysqldump im Portabilitätsmodus
verwendet.
Sorgt dafür, dass MySQL-spezifische Indexoptionen in der
Ausgabe von SHOW CREATE TABLE
nicht
angegeben werden. Dieser Modus wird von
mysqldump im Portabilitätsmodus
verwendet.
Sorgt dafür, dass MySQL-spezifische Tabellenoptionen (wie
ENGINE
) in der Ausgabe von
SHOW CREATE TABLE
nicht angegeben
werden. Dieser Modus wird von mysqldump
im Portabilitätsmodus verwendet.
Bei Subtraktionsoperationen wird das Ergebnis nicht als
UNSIGNED
gekennzeichnet, wenn einer der
Operanden ohne Vorzeichen ist. Beachten Sie, dass hiermit
BIGINT UNSIGNED
nicht mehr in allen
Kontexten hundertprozentig einsetzbar ist. Siehe auch
Abschnitt 12.8, „Cast-Funktionen und Operatoren“.
Im strikten Modus wird '0000-00-00'
nicht als gültiges Datum zugelassen. Mit der Option
IGNORE
können Sie trotzdem
Nulldatumsangaben einfügen. Außerhalb des strikten Modus
wird das Datum zwar akzeptiert, aber es wird eine Warnung
erzeugt.
Im strikten Modus werden Daten nicht akzeptiert, wenn die
Monats- oder Tagesangabe 0 ist. Wenn der Modus mit der
Option IGNORE
verwendet wird, fügt
MySQL das Datum '0000-00-00'
für
derartige Datumsangaben ein. Außerhalb des strikten Modus
wird das Datum zwar akzeptiert, aber es wird eine Warnung
erzeugt.
Erlaubt keine Abfragen, bei denen die GROUP
BY
-Klausel auf eine Spalte verweist, die in der
Ausgabespaltenliste nicht vorhanden ist.
Behandelt ||
als Operator zur
String-Verkettung (also identisch mit
CONCAT()
) statt als Synonym von
OR
.
Behandelt REAL
als Synonym von
FLOAT
. Standardmäßig behandelt MySQL
REAL
als Synonym von
DOUBLE
.
Aktiviert den strikten Modus für alle Speicher-Engines. Ungültige Datenwerte werden abgewiesen. Zusätzliche Informationen folgen.
Aktiviert den strikten Modus für transaktionssichere Speicher-Engines sowie – sofern möglich – für nicht transaktionssichere Speicher-Engines. Zusätzliche Informationen folgen.
Der strikte Modus steuert, wie MySQL Eingabewerte behandelt,
die ungültig sind oder fehlen. Ein Wert kann aus mehreren
Gründen ungültig sein. So kann er den falschen Datentyp für
die Spalte aufweisen oder außerhalb des zulässigen Bereichs
liegen. Ein Wert fehlt, wenn ein neuer Datensatz, der
eingefügt werden soll, keinen Wert für eine Spalte enthält,
für die keine explizite DEFAULT
-Klausel
definiert ist.
Bei transaktionssicheren Tabellen tritt bei ungültigen oder
fehlenden Werten in einer Anweisung ein Fehler auf, wenn einer
der Modi STRICT_ALL_TABLES
oder
STRICT_TRANS_TABLES
aktiviert ist. Die
Anweisung wird abgebrochen, und es erfolgt ein Rollback.
Bei nicht transaktionssicheren Tabellen ist das Verhalten in beiden Modi gleich, wenn der betreffende Wert im ersten einzufügenden oder zu aktualisierenden Datensatz auftritt. Die Anweisung wird abgebrochen, und die Tabelle bleibt unverändert. Wenn die Anweisung mehrere Datensätze einfügt oder ändert und der unpassende Wert im zweiten oder einem nachfolgenden Datensatz auftritt, dann hängt das Ergebnis davon ab, welche Option aktiviert ist:
Bei STRICT_ALL_TABLES
gibt MySQL einen
Fehler zurück und ignoriert die verbleibenden
Datensätze. Allerdings bleiben die zuvor durch Einfügung
oder Aktualisierung an Datensätzen vorgenommenen
Änderung erhalten. Das bedeutet, dass unter Umständen
eine Teilaktualisierung erfolgt, was vielleicht nicht
wünschenswert ist. Um dies zu vermeiden, sollten Sie am
besten Anweisungen nur für jeweils einen Datensatz
verwenden, da diese abgebrochen werden können, ohne die
Tabelle zu verändern.
Bei STRICT_TRANS_TABLES
wandelt MySQL
einen ungültigen Wert in den nächstgelegenen für die
Spalte gültigen Wert um und fügt diesen umgewandelten
Wert dann ein. Fehlt ein Wert, dann fügt MySQL den
impliziten Vorgabewert für den Spaltendatentyp ein. In
beiden Fällen erzeugt MySQL zudem eine Warnung (statt
eines Fehlers) und fährt dann mit der Verarbeitung der
Anweisung fort. Implizite Vorgabewerte sind in
Abschnitt 11.1.4, „Vorgabewerte von Datentypen“, beschrieben.
Der strikte Modus untersagt ungültige Datumswerte wie
'2004-04-31'
. Nicht verboten sind hingegen
Datumsangaben mit Nullbestandteilen wie etwa
'2004-04-00'
oder „Nulldaten“.
Um auch diese zu unterbinden, aktivieren Sie die SQL-Modi
NO_ZERO_IN_DATE
und
NO_ZERO_DATE
zusätzlich zum strikten
Modus.
Wenn Sie den strikten Modus nicht verwenden (d. h. weder
STRICT_TRANS_TABLES
noch
STRICT_ALL_TABLES
sind aktiviert), dann
fügt MySQL korrigierte Werte für ungültige oder fehlende
Angaben ein und erzeugt Warnungen. Im strikten Modus können
Sie dieses Verhalten erzeugen, indem Sie INSERT
IGNORE
bzw. UPDATE IGNORE
verwenden. Siehe auch Abschnitt 13.5.4.25, „SHOW WARNINGS
“.
Die folgenden Spezialmodi sind als Abkürzungen für Kombinationen von Moduswerten aus obiger Liste aufzufassen.
Die Beschreibungen enthalten alle Moduswerte, die in der aktuellen MySQL-Version vorhanden sind. Bei älteren Versionen enthalten die Kombinationsmodi keine einzelnen Moduswerte, die erst in neueren Versionen verfügbar sind.
Entspricht REAL_AS_FLOAT
,
PIPES_AS_CONCAT
,
ANSI_QUOTES
,
IGNORE_SPACE
. Siehe auch
Abschnitt 1.9.3, „MySQL im ANSI-Modus laufen lassen“.
Entspricht PIPES_AS_CONCAT
,
ANSI_QUOTES
,
IGNORE_SPACE
,
NO_KEY_OPTIONS
,
NO_TABLE_OPTIONS
,
NO_FIELD_OPTIONS
.
Entspricht PIPES_AS_CONCAT
,
ANSI_QUOTES
,
IGNORE_SPACE
,
NO_KEY_OPTIONS
,
NO_TABLE_OPTIONS
,
NO_FIELD_OPTIONS
,
NO_AUTO_CREATE_USER
.
Entspricht PIPES_AS_CONCAT
,
ANSI_QUOTES
,
IGNORE_SPACE
,
NO_KEY_OPTIONS
,
NO_TABLE_OPTIONS
,
NO_FIELD_OPTIONS
.
Entspricht NO_FIELD_OPTIONS
,
HIGH_NOT_PRECEDENCE
.
Entspricht NO_FIELD_OPTIONS
,
HIGH_NOT_PRECEDENCE
.
Entspricht PIPES_AS_CONCAT
,
ANSI_QUOTES
,
IGNORE_SPACE
,
NO_KEY_OPTIONS
,
NO_TABLE_OPTIONS
,
NO_FIELD_OPTIONS
,
NO_AUTO_CREATE_USER
.
Entspricht PIPES_AS_CONCAT
,
ANSI_QUOTES
,
IGNORE_SPACE
,
NO_KEY_OPTIONS
,
NO_TABLE_OPTIONS
,
NO_FIELD_OPTIONS
.
Entspricht STRICT_TRANS_TABLES
,
STRICT_ALL_TABLES
,
NO_ZERO_IN_DATE
,
NO_ZERO_DATE
,
ERROR_FOR_DIVISION_BY_ZERO
,
NO_AUTO_CREATE_USER
.
Dies ist eine Übersetzung des MySQL-Referenzhandbuchs, das sich auf dev.mysql.com befindet. Das ursprüngliche Referenzhandbuch ist auf Englisch, und diese Übersetzung ist nicht notwendigerweise so aktuell wie die englische Ausgabe. Das vorliegende deutschsprachige Handbuch behandelt MySQL bis zur Version 5.1.