ALTER {DATABASE | SCHEMA} [db_name]
    alter_specification [, alter_specification] ...

alter_specification:
    [DEFAULT] CHARACTER SET charset_name
  | [DEFAULT] COLLATE collation_name

Mit ALTER DATABASE können Sie die grundlegenden Eigenschaften einer Datenbank ändern. Diese Eigenschaften sind in der Datei db.opt im Datenbankverzeichnis gespeichert. Um ALTER DATABASE verwenden zu können, benötigen Sie die Berechtigung ALTER für die Datenbank. ALTER SCHEMA ist ein Synonym für ALTER DATABASE.

Die Klausel CHARACTER SET ändert den Standardzeichensatz der Datenbank. Die Klausel COLLATE ändert die Standardsortierfolge der Datenbank. Kapitel 10, Zeichensatz-Unterstützung, behandelt Namen von Zeichensätzen und Sortierfolgen.

Der Datenbankname kann weggelassen werden. In diesem Fall bezieht sich die Anweisung auf die Standarddatenbank.

ALTER [IGNORE] TABLE tbl_name
    alter_specification [, alter_specification] ...

alter_specification:
    ADD [COLUMN] column_definition [FIRST | AFTER col_name ]
  | ADD [COLUMN] (column_definition,...)
  | ADD INDEX [index_name] [index_type] (index_col_name,...)
  | ADD [CONSTRAINT [symbol]]
        PRIMARY KEY [index_type] (index_col_name,...)
  | ADD [CONSTRAINT [symbol]]
        UNIQUE [INDEX] [index_name] [index_type] (index_col_name,...)
  | ADD FULLTEXT [INDEX] [index_name] (index_col_name,...)
      [WITH PARSER parser_name]
  | ADD SPATIAL [INDEX] [index_name] (index_col_name,...)
  | ADD [CONSTRAINT [symbol]]
        FOREIGN KEY [index_name] (index_col_name,...)
        [reference_definition]
  | ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}
  | CHANGE [COLUMN] old_col_name column_definition
        [FIRST|AFTER col_name]
  | MODIFY [COLUMN] column_definition [FIRST | AFTER col_name]
  | DROP [COLUMN] col_name
  | DROP PRIMARY KEY
  | DROP INDEX index_name
  | DROP FOREIGN KEY fk_symbol
  | DISABLE KEYS
  | ENABLE KEYS
  | RENAME [TO] new_tbl_name
  | ORDER BY col_name
  | CONVERT TO CHARACTER SET charset_name [COLLATE collation_name]
  | [DEFAULT] CHARACTER SET charset_name [COLLATE collation_name]
  | DISCARD TABLESPACE
  | IMPORT TABLESPACE
  | table_options
  | partition_options
  | ADD PARTITION partition_definition
  | DROP PARTITION partition_names
  | COALESCE PARTITION number
  | REORGANIZE PARTITION partition_names INTO (partition_definitions)
  | ANALYZE PARTITION partition_names
  | CHECK PARTITION partition_names
  | OPTIMIZE PARTITION partition_names
  | REBUILD PARTITION partition_names
  | REPAIR PARTITION partition_names

ALTER TABLE ermöglicht Ihnen das Ändern der Struktur einer vorhandenen Tabelle. Sie können beispielsweise Spalten hinzufügen oder entfernen, Indizes erstellen oder löschen, den Typ einer vorhandenen Spalte ändern oder Spalten oder die Tabelle umbenennen. Ferner können Sie den Kommentar für die Tabelle und auch den Tabellentyp modifizieren.

Die Syntax für viele der zulässigen Änderungen ähnelt Klauseln der CREATE TABLE-Anweisung. Hierzu gehören table_options-Modifikationen für Optionen wie ENGINE, AUTO_INCREMENT und AVG_ROW_LENGTH. (Allerdings ignoriert ALTER TABLE die Tabellenoptionen DATA DIRECTORY und INDEX DIRECTORY.) Abschnitt 13.1.5, „CREATE TABLE“, listet alle Tabellenoptionen auf.

Bei manchen Operationen kann eine Warnung erzeugt werden, wenn diese auf eine Tabelle angewendet werden sollen, deren Speicher-Engine die Operation nicht unterstützt. Diese Warnungen lassen sich mit SHOW WARNINGS anzeigen. Siehe auch Abschnitt 13.5.4.25, „SHOW WARNINGS“.

ALTER TABLE erstellt zunächst eine temporäre Kopie der Ursprungstabelle. Die Änderung wird dann an der Kopie vorgenommen, und schließlich wird die Originaltabelle gelöscht und die Kopie entsprechend umbenannt. Während der Ausführung von ALTER TABLE kann die Originaltabelle von anderen Clients gelesen werden. Änderungs- und Schreiboperationen in der Tabelle werden blockiert, bis die neue Tabelle bereit ist, und dann automatisch in die neue Tabelle umgeleitet. Aktualisierungen schlagen nicht fehl.

Beachten Sie, dass, wenn Sie eine andere ALTER TABLE-Option als RENAME verwenden, MySQL immer eine Temporärtabelle erstellt – und zwar auch dann, wenn die Daten strenggenommen nicht kopiert werden müssten (z. B. wenn Sie nur den Namen einer Spalte ändern). Bei MyISAM-Tabellen ist die Neuerstellung des Indexes der zeitaufwändigste Teil des Änderungsvorgangs. Sie können diesen beschleunigen, indem Sie die Systemvariable myisam_sort_buffer_size auf einen hohen Wert setzen.

Mit der C-API-Funktion mysql_info() können Sie ermitteln, wie viele Datensätze kopiert und (sofern IGNORE verwendet wird) aufgrund doppelt vorhandener eindeutiger Schlüsselwerte gelöscht wurden. Siehe auch Abschnitt 24.2.3.34, „mysql_info()“.

Es folgen ein paar Beispiele, die Anwendungsmöglichkeiten für ALTER TABLE zeigen. Beginnen wir mit einer Tabelle t1, die wie folgt erstellt wird:

CREATE TABLE t1 (a INTEGER,b CHAR(10));

So benennen Sie die Tabelle von t1 in t2 um:

ALTER TABLE t1 RENAME t2;

So ändern Sie den Typ der Spalte a von INTEGER auf TINYINT NOT NULL (wobei der Name erhalten bleibt) und den Typ der Spalte b von CHAR(10) zu CHAR(20) sowie deren Namen von b zu c:

ALTER TABLE t2 MODIFY a TINYINT NOT NULL, CHANGE b c CHAR(20);

So fügen Sie eine neue TIMESTAMP-Spalte namens d hinzu:

ALTER TABLE t2 ADD d TIMESTAMP;

So fügen Sie Indizes zu den Spalten d und a hinzu:

ALTER TABLE t2 ADD INDEX (d), ADD INDEX (a);

So entfernen Sie die Spalte c:

ALTER TABLE t2 DROP COLUMN c;

So fügen Sie eine neue AUTO_INCREMENT-Spalte namens c hinzu:

ALTER TABLE t2 ADD c INT UNSIGNED NOT NULL AUTO_INCREMENT,
  ADD PRIMARY KEY (c);

Beachten Sie, dass wir c (als PRIMARY KEY) indiziert haben, weil AUTO_INCREMENT-Spalten indiziert werden müssen, und dass wir außerdem c als NOT NULL deklariert haben, weil Primärschlüsselspalten nicht NULL sein dürfen.

Wenn Sie eine AUTO_INCREMENT-Spalte hinzufügen, werden als Spaltenwerte automatisch Sequenznummern eingetragen. Bei MyISAM-Tabellen können Sie die erste Sequenznummer durch Ausführung von SET INSERT_ID=value vor ALTER TABLE oder durch Verwendung der Tabellenoption AUTO_INCREMENT=value einstellen. Siehe auch Abschnitt 13.5.3, „SET“.

Sie können die Tabellenoption ALTER TABLE ... AUTO_INCREMENT=value bei InnoDB-Tabellen zur Einstellung der Sequenznummer für neue Datensätze benutzen, wenn der Wert größer ist als der höchste Wert in der AUTO_INCREMENT-Spalte. Ist der Wert kleiner als der aktuelle Maximalwert der Spalte, dann wird keine Fehlermeldung ausgegeben, und der aktuelle Sequenzwert wird nicht geändert.

Wenn Sie bei MyISAM-Tabellen die AUTO_INCREMENT-Spalte nicht ändern, ist die Sequenznummer nicht betroffen. Wenn Sie eine AUTO_INCREMENT-Spalte löschen und dann eine neue AUTO_INCREMENT-Spalte hinzufügen, beginnen die Sequenznummern wieder bei 1.

Siehe auch Abschnitt A.7.1, „Probleme mit ALTER TABLE“.

CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name
    [create_specification [, create_specification] ...]

create_specification:
    [DEFAULT] CHARACTER SET charset_name
  | [DEFAULT] COLLATE collation_name

CREATE DATABASE erstellt eine Datenbank des angegebenen Namens. Um CREATE DATABASE verwenden zu können, benötigen Sie die Berechtigung CREATE für die Datenbank. CREATE SCHEMA ist ein Synonym für CREATE DATABASE.

Die Regeln für zulässige Datenbanknamen sind in Abschnitt 9.2, „Datenbank-, Tabellen-, Index-, Spalten- und Aliasnamen“, beschrieben. Wenn die Datenbank vorhanden ist und Sie IF NOT EXISTS nicht angegeben haben, tritt ein Fehler auf.

create_specification-Optionen geben die Datenbankeigenschaften an. Diese Datenbankeigenschaften sind in der Datei db.opt im Datenbankverzeichnis gespeichert. Die Klausel CHARACTER SET gibt den Standardzeichensatz der Datenbank an. Die Klausel COLLATE gibt die Standardsortierfolge der Datenbank an. Kapitel 10, Zeichensatz-Unterstützung, behandelt Namen von Zeichensätzen und Sortierfolgen.

Datenbanken werden in MySQL als Verzeichnisse implementiert, die Dateien enthalten, welche den Tabellen der Datenbank entsprechen. Weil bei der Erstellung einer Datenbank noch keine Tabellen vorhanden sind, erzeugt die Anweisung CREATE DATABASE lediglich ein Verzeichnis im MySQL-Datenverzeichnis sowie die Datei db.opt.

Wenn Sie manuell (z. B. mit mkdir) ein Verzeichnis im Datenverzeichnis erstellen, betrachtet der Server dieses als Datenbankverzeichnis und zeigt es in der Ausgabe von SHOW DATABASES an.

Sie können zur Erstellung von Datenbanken auch das Programm mysqladmin verwenden. Siehe auch Abschnitt 8.7, „mysqladmin — Client für die Verwaltung eines MySQL Servers“.

CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name
    [USING index_type]
    ON tbl_name (index_col_name,...)
    [WITH PARSER parser_name]

index_col_name:
    col_name [(length)] [ASC | DESC]

CREATE INDEX wird mit einer ALTER TABLE-Anweisung verknüpft, um Indizes zu erstellen. Siehe auch Abschnitt 13.1.2, „ALTER TABLE“. Weitere Informationen dazu, wie MySQL Indizes verwendet, finden Sie in Abschnitt 7.4.5, „Wie MySQL Indizes benutzt“.

Normalerweise erstellen Sie alle Indizes für eine Tabelle zu dem Zeitpunkt, an dem Sie auch die Tabelle selbst mit CREATE TABLE anlegen. Siehe auch Abschnitt 13.1.5, „CREATE TABLE“. CREATE INDEX erlaubt Ihnen das Hinzufügen von Indizes zu vorhandenen Tabellen.

Eine Spaltenliste in der Form (col1,col2,...) erstellt einen mehrspaltigen Index. Indexwerte werden gebildet, indem die Werte der betreffenden Spalten verkettet werden.

Bei CHAR-, VARCHAR-, BINARY- und VARBINARY-Spalten können Indizes erstellt werden, die nur einen Teil einer Spalte verwenden. Hier verwenden Sie die Syntax col_name(length), um die Länge des Indexpräfixes anzugeben. Indexeinträge umfassen dann die ersten length Zeichen jedes Spaltenwerts bei CHAR- und VARCHAR-Spalten sowie die ersten length Bytes jedes Spaltenwerts bei BINARY- und VARBINARY-Spalten. BLOB- und TEXT-Spalten lassen sich auch indizieren, aber eine Präfixlänge muss angegeben werden.

Die folgende Anweisung erstellt einen Index unter Verwendung der ersten zehn Zeichen der Spalte name:

CREATE INDEX part_of_name ON customer (name(10));

Wenn die ersten zehn Zeichen aller Namen in der Spalte sich durchgehend unterscheiden, dann sollte dieser Index nicht viel langsamer sein als ein Index, der aus der gesamten Spalte name erstellt wurde. Auch die Verwendung von Teilspalten für Indizes kann die Indexdatei wesentlich kleiner machen, wodurch sich viel Festplattenspeicher sparen lässt und INSERT-Operationen unter Umständen auch beschleunigt werden können.

Präfixe können bis 1.000 Byte lang sein (767 Byte bei InnoDB-Tabellen). Beachten Sie, dass Präfixbeschränkungen in Byte angegeben werden, wohingegen die Präfixlänge in CREATE INDEX-Anweisungen bei nichtbinären Datentypen (CHAR, VARCHAR, TEXT) als Anzahl der Zeichen interpretiert wird. Dies muss bei der Angabe einer Präfixlänge für eine Spalte berücksichtigt werden, die einen Multibytezeichensatz verwendet.

In MySQL 5.1 können Sie

Eine index_col_name-Definition kann auf ASC oder DESC enden. Diese Schlüsselwörter sind für zukünftige Erweiterungen zulässig, um eine Speicherung der Indexwerte in auf- oder absteigender Reihenfolge festzulegen. Zurzeit werden sie zwar erkannt, aber ignoriert – Indexwerte werden immer in aufsteigender Reihenfolge gespeichert.

Einige Speicher-Engines gestatten bei der Erstellung eines Indexes die Angabe eines Indextyps. Die Syntax für die Konfigurationsangabe index_type lautet USING type_name. Zulässige Werte für type_name, die von den verschiedenen Speicher-Engines unterstützt werden, sind in der folgenden Tabelle aufgelistet. Sind mehrere Indextypen aufgeführt, so wird der erste standardmäßig verwendet, wenn keine Angabe für index_type vorhanden ist.

Ein paar Beispiele:

CREATE TABLE lookup (id INT) ENGINE = MEMORY;
CREATE INDEX id_index USING BTREE ON lookup (id);

TYPE type_name kann als Synonym für USING type_name benutzt werden, um einen Indextyp anzugeben. Allerdings ist USING die bevorzugte Form. Ferner ist der Indexname, der in der Syntax zur Indexspezifikation vor dem Indextyp steht, bei TYPE nicht optional: Anders als USING ist TYPE kein reserviertes Wort und wird infolgedessen nicht als Indexname interpretiert.

Wenn Sie einen Indextyp angeben, der für eine gegebene Speicher-Engine nicht zulässig ist, aber ein anderer Indextyp vorhanden ist, den die Engine verwenden kann, ohne dass hiervon Abfrageergebnisse betroffen wären, dann verwendet die Engine diesen verfügbaren Typ.

FULLTEXT-Indizes werden nur für MyISAM-Tabellen unterstützt und dürfen nur CHAR-, VARCHAR- und TEXT-Spalten enthalten. Siehe auch Abschnitt 12.7, „MySQL-Volltextsuche“. Eine WITH PARSER-Klausel kann angegeben werden, um ein Parser-Plug-In mit dem Index zu verknüpfen, wenn Volltextindizierungs- und Suchoperationen besondere Maßnahmen erfordern. Diese Klausel ist nur für FULLTEXT-Indizes zulässig. Informationen zur Erstellung von Plug-Ins finden Sie in Abschnitt 26.2, „Die MySQL-Plug-In-Schnittstelle“.

SPATIAL-Indizes werden nur für MyISAM-Tabellen unterstützt und dürfen nur raumbezogene Spalten enthalten, die als NOT NULL definiert sind. Kapitel 18, Raumbezogene Erweiterungen in MySQL, beschreibt raumbezogene Datentypen.

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    [(create_definition,...)]
    [table_options] [select_statement]

Oder:

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    [(] LIKE old_tbl_name [)];

create_definition:
    column_definition
  | [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name,...)
  | KEY [index_name] [index_type] (index_col_name,...)
  | INDEX [index_name] [index_type] (index_col_name,...)
  | [CONSTRAINT [symbol]] UNIQUE [INDEX]
        [index_name] [index_type] (index_col_name,...)
  | FULLTEXT [INDEX] [index_name] (index_col_name,...)
      [WITH PARSER parser_name]
  | SPATIAL [INDEX] [index_name] (index_col_name,...)
  | [CONSTRAINT [symbol]] FOREIGN KEY
        [index_name] (index_col_name,...) [reference_definition]
  | CHECK (expr)

column_definition:
    col_name type [NOT NULL | NULL] [DEFAULT default_value]
        [AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY]
        [COMMENT 'string'] [reference_definition]

type:
    TINYINT[(length)] [UNSIGNED] [ZEROFILL]
  | SMALLINT[(length)] [UNSIGNED] [ZEROFILL]
  | MEDIUMINT[(length)] [UNSIGNED] [ZEROFILL]
  | INT[(length)] [UNSIGNED] [ZEROFILL]
  | INTEGER[(length)] [UNSIGNED] [ZEROFILL]
  | BIGINT[(length)] [UNSIGNED] [ZEROFILL]
  | REAL[(length,decimals)] [UNSIGNED] [ZEROFILL]
  | DOUBLE[(length,decimals)] [UNSIGNED] [ZEROFILL]
  | FLOAT[(length,decimals)] [UNSIGNED] [ZEROFILL]
  | DECIMAL(length,decimals) [UNSIGNED] [ZEROFILL]
  | NUMERIC(length,decimals) [UNSIGNED] [ZEROFILL]
  | DATE
  | TIME
  | TIMESTAMP
  | DATETIME
  | YEAR
  | CHAR(length) [BINARY | ASCII | UNICODE]
  | VARCHAR(length) [BINARY]
  | BINARY(length)
  | VARBINARY(length)
  | TINYBLOB
  | BLOB
  | MEDIUMBLOB
  | LONGBLOB
  | TINYTEXT [BINARY]
  | TEXT [BINARY]
  | MEDIUMTEXT [BINARY]
  | LONGTEXT [BINARY]
  | ENUM(value1,value2,value3,...)
  | SET(value1,value2,value3,...)
  | spatial_type

index_col_name:
    col_name [(length)] [ASC | DESC]

reference_definition:
    REFERENCES tbl_name [(index_col_name,...)]
               [MATCH FULL | MATCH PARTIAL | MATCH SIMPLE]
               [ON DELETE reference_option]
               [ON UPDATE reference_option]

reference_option:
    RESTRICT | CASCADE | SET NULL | NO ACTION

table_options: table_option [table_option] ...

table_option:
    {ENGINE|TYPE} [=] engine_name
  | AUTO_INCREMENT [=] value
  | AVG_ROW_LENGTH [=] value
  | [DEFAULT] CHARACTER SET charset_name [COLLATE collation_name]
  | CHECKSUM [=] {0 | 1}
  | COMMENT [=] 'string'
  | CONNECTION [=] 'connect_string'
  | MAX_ROWS [=] value
  | MIN_ROWS [=] value
  | PACK_KEYS [=] {0 | 1 | DEFAULT}
  | PASSWORD [=] 'string'
  | DELAY_KEY_WRITE [=] {0 | 1}
  | ROW_FORMAT [=] {DEFAULT|DYNAMIC|FIXED|COMPRESSED|REDUNDANT|COMPACT}
  | UNION [=] (tbl_name[,tbl_name]...)
  | INSERT_METHOD [=] { NO | FIRST | LAST }
  | DATA DIRECTORY [=] 'absolute path to directory'
  | INDEX DIRECTORY [=] 'absolute path to directory'

partition_options:
    PARTITION BY
           [LINEAR] HASH(expr)
        |  [LINEAR] KEY(column_list)
        |  RANGE(expr)
        |  LIST(expr)
    [PARTITIONS num]
    [  SUBPARTITION BY
           [LINEAR] HASH(expr)
         | [LINEAR] KEY(column_list)
      [SUBPARTITIONS num]
    ]
    [(partition_definition) [, (partition_definition)] ...]

partition_definition:
    PARTITION partition_name
        [VALUES {LESS THAN (expr) | MAXVALUE | IN (value_list)}]
        [[STORAGE] ENGINE [=] engine-name]
        [COMMENT [=] 'comment_text' ]
        [DATA DIRECTORY [=] 'data_dir']
        [INDEX DIRECTORY [=] 'index_dir']
        [MAX_ROWS [=] max_number_of_rows]
        [MIN_ROWS [=] min_number_of_rows]
        [TABLESPACE [=] (tablespace_name)]
        [NODEGROUP [=] node_group_id]
        [(subpartition_definition) [, (subpartition_definition)] ...]

subpartition_definition:
    SUBPARTITION logical_name
        [[STORAGE] ENGINE [=] engine-name]
        [COMMENT [=] 'comment_text' ]
        [DATA DIRECTORY [=] 'data_dir']
        [INDEX DIRECTORY [=] 'index_dir']
        [MAX_ROWS [=] max_number_of_rows]
        [MIN_ROWS [=] min_number_of_rows]
        [TABLESPACE [=] (tablespace_name)]
        [NODEGROUP [=] node_group_id]

select_statement:
    [IGNORE | REPLACE] [AS] SELECT ...   (Some legal select statement)

CREATE TABLE erstellt eine Tabelle des angegebenen Namens. Für die Tabelle benötigen Sie die Berechtigung CREATE.

Die Regeln für zulässige Tabellennamen sind in Abschnitt 9.2, „Datenbank-, Tabellen-, Index-, Spalten- und Aliasnamen“, beschrieben. Standardmäßig wird die Tabelle in der Standarddatenbank erstellt. Wenn die Tabelle bereits vorhanden ist oder weder die Standarddatenbank noch die angegebene Datenbank vorhanden sind, wird ein Fehler erstellt.

Der Tabellenname kann als db_name.tbl_name angegeben werden, um die Tabelle in einer bestimmten Datenbank zu erstellen. Das funktioniert unabhängig vom Vorhandensein einer Standarddatenbank, sofern die angegebene Datenbank vorhanden ist. Wenn Sie Bezeichner in Anführungszeichen verwenden, setzen Sie Datenbank- und Tabellennamen separat in Anführungszeichen. So ist etwa `mydb`.`mytbl` zulässig – anders als `mydb.mytbl`.

Sie können das Schlüsselwort TEMPORARY bei der Erstellung einer Tabelle verwenden. Eine TEMPORARY-Tabelle ist nur für die aktuelle Verbindung sichtbar und wird beim Beenden dieser Verbindung automatisch gelöscht. Das bedeutet, dass zwei verschiedene Verbindungen Temporärtabellen desselben Namens verwenden können, ohne dass es zu Konflikten mit der jeweils anderen Tabelle oder einer nichttemporären Tabelle gleichen Namens kommt. (Die vorhandene Tabelle wird verborgen, bis die Temporärtabelle gelöscht wird.) Zur Erstellung von Temporärtabellen benötigen Sie die Berechtigung CREATE TEMPORARY TABLES.

Die Schlüsselwörter IF NOT EXISTS verhindern, dass ein Fehler auftritt, wenn die Tabelle vorhanden ist. Allerdings wird nicht überprüft, ob die vorhandene Tabelle die gleiche Struktur wie die in der CREATE TABLE-Anweisung angegebene Tabelle hat. Hinweis: Wenn Sie IF NOT EXISTS in einer CREATE TABLE ... SELECT-Anweisung verwenden, werden alle durch den SELECT-Teil gewählten Datensätze ohne Berücksichtigung der Frage eingefügt, ob die Tabelle bereits vorhanden ist.

MySQL stellt jede Tabelle durch eine .frm-Tabellenformatdatei (Definitionsdatei) im Datenbankverzeichnis dar. Die Speicher-Engine der Tabelle erstellt unter Umständen auch andere Dateien. Im Falle von MyISAM-Tabellen erstellt die Speicher-Engine Daten- und Indexdateien. Es gibt also für jede MyISAM-Tabelle tbl_name drei Dateien auf der Festplatte:

Kapitel 14, Speicher-Engines und Tabellentypen, beschreibt, welche Dateien die einzelnen Speicher-Engines zur Darstellung von Tabellen erstellen.

type stellt den Datentyp einer Spaltendefinition dar. spatial_type ist ein raumbezogener Datentyp. Allgemeine Informationen zu den Eigenschaften von Datentypen (außer raumbezogenen Datentypen) finden Sie in Kapitel 11, Datentypen. Informationen zu raumbezogenen Datentypen finden Sie in Kapitel 18, Raumbezogene Erweiterungen in MySQL.

Die Tabellenoption ENGINE gibt die Speicher-Engine für die Tabelle an. TYPE ist ein Synonym hierzu, wobei ENGINE der bevorzugte Name ist.

Die Tabellenoption ENGINE akzeptiert die in der folgenden Tabelle aufgeführten Namen für Speicher-Engines.

Wird eine Speicher-Engine angegeben, die nicht verfügbar ist, dann verwendet MySQL stattdessen die Standard-Engine. Im Normalfall ist dies MyISAM. Wenn beispielsweise eine Tabellendefinition die Option ENGINE=BDB enthält, aber der MySQL Server BDB-Tabellen nicht unterstützt, dann wird die Tabelle als MyISAM-Tabelle erstellt. Auf diese Weise ist eine Replikationskonfiguration realisierbar, bei der Sie transaktionssichere Tabellen auf dem Master haben, während die auf dem Slave erstellten Tabellen nicht transaktionssicher sind (was eine Erhöhung der Verarbeitungsgeschwindigkeit nach sich zieht). In MySQL 5.1 erscheint eine Warnung, wenn die Spezifikation der Speicher-Engine nicht berücksichtigt werden kann.

Die übrigen Tabellenoptionen werden zur Optimierung des Verhaltens der Tabelle verwendet. In den meisten Fällen müssen Sie sie überhaupt nicht angeben. Diese Optionen gelten für alle Speicher-Engines, soweit nichts anderes angegeben ist:

Sie können eine Tabelle aus einer anderen Tabelle erstellen, indem Sie am Ende der CREATE TABLE-Anweisung eine SELECT-Anweisung hinzufügen:

CREATE TABLE new_tbl SELECT * FROM orig_tbl;

MySQL erstellt neue Spalten für alle Elemente in der SELECT-Anweisung. Zum Beispiel:

mysql> CREATE TABLE test (a INT NOT NULL AUTO_INCREMENT,
    ->        PRIMARY KEY (a), KEY(b))
    ->        ENGINE=MyISAM SELECT b,c FROM test2;

Hierbei wird eine MyISAM-Tabelle mit drei Spalten a, b und c erstellt. Beachten Sie, dass die Spalten aus der SELECT-Anweisung rechts an die Tabelle angehängt und nicht in diese eingesetzt werden. Betrachten Sie einmal das folgende Beispiel:

mysql> SELECT * FROM foo;
+---+
| n |
+---+
| 1 |
+---+

mysql> CREATE TABLE bar (m INT) SELECT n FROM foo;
Query OK, 1 row affected (0.02 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM bar;
+------+---+
| m    | n |
+------+---+
| NULL | 1 |
+------+---+
1 row in set (0.00 sec)

Für jeden Datensatz in der Tabelle foo wird ein Datensatz in bar mit den Werten aus foo und den Standardwerten bei neuen Spalten eingefügt.

In einer Tabelle, die mit CREATE TABLE ... SELECT erstellt wurde, erscheinen die Spalten, die im CREATE TABLE-Teil genannt werden, zuerst. Die in beiden Teilen oder nur im SELECT-Teil genannten Spalten folgen nach. Der Datentyp der SELECT-Spalten kann außer Kraft gesetzt werden, indem die Spalte zusätzlich im CREATE TABLE-Teil genannt wird.

Wenn während des Kopierens der Daten in die Tabelle Fehler auftreten, wird diese automatisch gelöscht und nicht erstellt.

CREATE TABLE ... SELECT erstellt keine automatischen Indizes. Dies wurde bewusst so eingerichtet, um die Anweisung so flexibel wie möglich zu halten. Wenn Sie wollen, dass Indizes in der Tabelle erstellt werden, müssen Sie diese vor der SELECT-Anweisung spezifizieren:

mysql> CREATE TABLE bar (UNIQUE (n)) SELECT n FROM foo;

Es kann dadurch zu Fällen von Datentypkonvertierung kommen. So wird z. B. das AUTO_INCREMENT-Attribut nicht beibehalten, und VARCHAR-Spalten können in CHAR-Spalten umgewandelt werden.

Wenn Sie eine Tabelle mit CREATE ... SELECT erstellen, müssen Sie in jedem Fall Aliase für Funktionsaufrufe oder Ausdrücke in der Abfrage verwenden. Andernfalls kann die CREATE-Anweisung fehlschlagen, oder es können unerwünschte Spaltennamen entstehen.

CREATE TABLE artists_and_works
  SELECT artist.name, COUNT(work.artist_id) AS number_of_works
  FROM artist LEFT JOIN work ON artist.id = work.artist_id
  GROUP BY artist.id;

Sie können den Typ einer erzeugten Spalte auch explizit angeben:

CREATE TABLE foo (a TINYINT NOT NULL) SELECT b+1 AS a FROM bar;

Mit LIKE können Sie eine leere Tabelle basierend auf der Definition einer anderen Tabelle einschließlich aller Spaltenattribute und Indizes erstellen, die auch in der Ursprungstabelle vorhanden sind:

CREATE TABLE new_tbl LIKE orig_tbl;

CREATE TABLE ... LIKE behält weder für die Ursprungstabelle angegebene DATA DIRECTORY- und INDEX DIRECTORY-Optionen noch Fremdschlüsseldefinitionen bei.

Sie können dem SELECT ein IGNORE oder REPLACE voranstellen, um anzugeben, wie mit Datensätzen zu verfahren ist, in denen Dubletten für eindeutige Schlüssel auftreten. Bei IGNORE werden neue Datensätze, die eine Dublette eines eindeutigen Schlüssels für einen bereits vorhandenen Datensatz enthalten, einfach verworfen. Wenn Sie REPLACE angeben, ersetzen die neuen die vorhandenen Datensätze, die denselben eindeutigen Schlüsselwert aufweisen. Werden weder IGNORE noch REPLACE angegeben, dann führt das Auftreten von Dubletten eindeutiger Schlüssel zu einem Fehler.

Um sicherzustellen, dass das Binärlog zur Wiederherstellung der Originaltabellen verwendet werden kann, gestattet MySQL keine gleichzeitigen Einfügeoperationen während der Ausführung einer CREATE TABLE ... SELECT-Anweisung.

DROP {DATABASE | SCHEMA} [IF EXISTS] db_name

DROP DATABASE löscht alle Tabellen in der Datenbank und nachfolgend auch die Datenbank. Seien Sie im Umgang mit dieser Anweisung extrem sorgfältig! Um DROP DATABASE verwenden zu können, benötigen Sie die Berechtigung DROP für die Datenbank. DROP SCHEMA ist ein Synonym für DROP DATABASE.

IF EXISTS wird verwendet, um einen Fehler abzufangen, wenn die Datenbank nicht vorhanden ist.

Wenn Sie DROP DATABASE für eine symbolisch verknüpfte Datenbank verwenden, werden sowohl die Verknüpfung als auch die Originaldatenbank gelöscht.

DROP DATABASE gibt die Anzahl der entfernten Tabellen zurück. Diese entspricht der Anzahl der gelöschten .frm-Dateien.

Die DROP DATABASE-Anweisung entfernt jene Dateien und Verzeichnisse, die MySQL im normalen Betrieb selbst erstellt hat, aus dem angegebenen Datenbankverzeichnis:

Wenn andere Dateien oder Verzeichnisse im Datenbankverzeichnis zurückbleiben, nachdem MySQL die genannten Elemente entfernt hat, kann das Datenbankverzeichnis nicht gelöscht werden. In diesem Fall müssen Sie alle verbleibenden Dateien oder Verzeichnisse manuell entfernen und die DROP DATABASE-Anweisung nachfolgend erneut absetzen.

Sie können Datenbanken auch mit mysqladmin löschen. Siehe auch Abschnitt 8.7, „mysqladmin — Client für die Verwaltung eines MySQL Servers“.

DROP INDEX index_name ON tbl_name

DROP INDEX löscht den Index namens index_name aus der Tabelle tbl_name. Diese Anweisung wird einer ALTER TABLE-Anweisung zugewiesen, die den Index löscht. Siehe auch Abschnitt 13.1.2, „ALTER TABLE“.

DROP [TEMPORARY] TABLE [IF EXISTS]
    tbl_name [, tbl_name] ...
    [RESTRICT | CASCADE]

DROP TABLE löscht eine oder mehrere Tabellen. Für jede Tabelle benötigen Sie die Berechtigung DROP. Alle Tabellendaten und die Tabellendefinition werden entfernt! Seien Sie also auch im Umgang mit dieser Anweisung sehr vorsichtig!

Beachten Sie, dass DROP TABLE bei einer partitionierten Tabelle die Tabellendefinition, alle Partitionen und alle in diesen Partitionen gespeicherten Daten permanent entfernt. Ebenso gelöscht wird die Partitionierungsdefinitiondatei (.par) der gelöschten Tabelle.

Verwenden Sie IF EXISTS, um einen Fehler bei Tabellen abzufangen, die nicht vorhanden sind. Für jede nicht vorhandene Tabelle wird ein Hinweis erzeugt, wenn Sie IF EXISTS benutzen. Siehe auch Abschnitt 13.5.4.25, „SHOW WARNINGS“.

RESTRICT und CASCADE erlauben eine leichtere Portierung. Derzeit haben sie aber keine Funktion.

Hinweis: DROP TABLE übergibt die derzeit aktive Transaktion sofort, sofern Sie nicht das Schlüsselwort TEMPORARY benutzen.

Das Schlüsselwort TEMPORARY hat die folgenden Auswirkungen:

Die Verwendung von TEMPORARY ist eine gute Möglichkeit, sicherzustellen, dass Sie nicht versehentlich eine nichttemporäre Tabelle löschen.

RENAME TABLE tbl_name TO new_tbl_name
    [, tbl_name2 TO new_tbl_name2] ...

Mit dieser Anweisung werden eine oder mehrere Tabellen umbenannt.

Der Umbenennungsvorgang erfolgt automatisch, d. h., kein anderer Thread kann währenddessen auf die umzubenennende(n) Tabelle(n) zugreifen. Wenn Sie beispielsweise eine Tabelle old_table haben, können Sie eine andere Tabelle new_table erstellen, die dieselbe Struktur hat, aber leer ist, und die vorhandene Tabelle dann wie folgt durch die leere Tabelle ersetzen (sofern backup_table nicht bereits vorhanden ist):

CREATE TABLE new_table (...);
RENAME TABLE old_table TO backup_table, new_table TO old_table;

Wenn die Anweisung mehrere Tabellen umbenennt, erfolgen die Umbenennungen von links nach rechts. Wollen Sie zwei Tabellennamen gegeneinander austauschen, dann können Sie dies wie folgt tun (sofern tmp_table nicht bereits vorhanden ist):

RENAME TABLE old_table TO tmp_table,
             new_table TO old_table,
             tmp_table TO new_table;

Solange zwei Datenbanken sich im selben Dateisystem befinden, können Sie mit RENAME TABLE auch eine Tabelle von einer Datenbank in eine andere verschieben:

RENAME TABLE current_db.tbl_name TO other_db.tbl_name;

RENAME TABLE funktioniert auch bei Views, solange Sie nicht versuchen, einen View in einer anderen Datenbank umzubenennen.

Wenn Sie RENAME ausführen, dürfen keine Tabellen gesperrt und keine Transaktionen aktiv sein. Sie benötigen für die Ursprungstabelle ferner die Berechtigungen ALTER und DROP sowie die Berechtigungen CREATE und INSERT für die neue Tabelle.

Wenn MySQL in einer tabellenübergreifenden Umbenennungsaktion auf Fehler trifft, wird für alle umbenannten Tabellen eine umgekehrte Umbenennung durchgeführt, um den ursprünglichen Zustand wiederherzustellen.

Syntax für eine Tabelle:

DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name
    [WHERE where_condition]
    [ORDER BY ...]
    [LIMIT row_count]

Syntax für mehrere Tabellen:

DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
    tbl_name[.*] [, tbl_name[.*]] ...
    FROM table_references
    [WHERE where_condition]

Oder:

DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
    FROM tbl_name[.*] [, tbl_name[.*]] ...
    USING table_references
    [WHERE where_condition]

Bei der Ein-Tabellen-Syntax löscht die DELETE-Anweisung Datensätze aus tbl_name und gibt die Anzahl gelöschter Datensätze zurück. Sofern angegeben, bestimmt die WHERE-Klausel die Bedingungen dafür, welche Datensätze gelöscht werden. Ohne WHERE-Klausel werden alle Datensätze gelöscht. Wenn die ORDER BY-Klausel vorhanden ist, werden die Datensätze in der angegebenen Reihenfolge gelöscht. Die LIMIT-Klausel kann die Anzahl der zu löschenden Datensätze beschränken.

Bei der Mehrtabellensyntax löscht DELETE aus jeder Tabelle tbl_name die Datensätze, die die Bedingungen erfüllen. In diesem Fall können ORDER BY und LIMIT nicht verwendet werden.

where_condition ist ein Ausdruck, der für jeden zu löschenden Datensatz wahr ist. Er wird wie in Abschnitt 13.2.7, „SELECT“, beschrieben angegeben.

Wie bereits gesagt, löscht eine DELETE-Anweisung ohne WHERE-Klausel alle Datensätze. Wenn Sie die Anzahl der gelöschten Datensätze nicht kennen müssen, können Sie dies schneller mit TRUNCATE TABLE erledigen. Siehe auch Abschnitt 13.2.9, „TRUNCATE“.

Wenn Sie den Datensatz entfernen, der den höchsten Wert für eine AUTO_INCREMENT-Spalte hat, dann wird der Wert bei BDB-Tabellen später wiederverwendet, nicht jedoch bei MyISAM- oder InnoDB-Tabellen. Wenn Sie im AUTOCOMMIT-Modus alle Datensätze in der Tabelle mit DELETE FROM tbl_name (ohne WHERE-Klausel) löschen, beginnt die Sequenz für alle Speicher-Engines mit Ausnahme von InnoDB und MyISAM neu. Bei InnoDB-Tabellen gibt es bezüglich dieses Verhaltens ein paar Ausnahmen, die in Abschnitt 14.2.6.3, „Wie eine Auto-Increment-Spalte in InnoDB funktioniert“, beschrieben sind.

Bei MyISAM- und BDB-Tabellen können Sie AUTO_INCREMENT in einer Sekundärspalte eines mehrspaltigen Schlüssels angeben. In diesem Fall werden Werte, die am Anfang der Sequenz gelöscht wurden, auch bei MyISAM-Tabellen wiederverwendet. Siehe auch Abschnitt 3.6.9, „Verwendung von AUTO_INCREMENT“.

Die DELETE-Anweisung unterstützt die folgenden Modifizierer:

Die Geschwindigkeit von Löschoperationen kann auch von den Faktoren beeinflusst werden, die in Abschnitt 7.2.18, „Geschwindigkeit von DELETE-Anfragen“, beschrieben werden.

Bei MyISAM-Tabellen werden gelöschte Datensätze zu einer verknüpften Liste hinzugefügt. Nachfolgende INSERT-Operationen verwenden die alten Datensatzpositionen neu. Um ungenutzten Speicher wieder freizugeben und die Dateigrößen zu verringern, verwenden Sie die OPTIMIZE TABLE-Anweisung oder das Hilfsprogramm myisamchk zur Reorganisierung der Tabellen. OPTIMIZE TABLE ist einfacher, myisamchk hingegen schneller. Siehe auch Abschnitt 13.5.2.5, „OPTIMIZE TABLE“, und Abschnitt 8.2, „myisamchk — Hilfsprogramm für die Tabellenwartung von MyISAM“.

Der Modifizierer QUICK bestimmt, ob Indexzweige bei Löschoperationen zusammengefasst werden. DELETE QUICK ist am nützlichsten bei Anwendungen, bei denen Indexwerte für gelöschte Datensätze durch ähnliche Indexwerte von später eingefügten Datensätzen ersetzt werden. In diesem Fall werden die von den gelöschten Werten zurückgelassenen Löcher wiederverwendet.

DELETE QUICK ist hingegen nicht nützlich, wenn gelöschte Werte nicht aufgefüllte Indexblöcke zur Folge haben, die einen Bereich von Indexwerten umfassen, bei denen neue Einfügeoperationen auftreten. In diesem Fall kann die Verwendung von QUICK zur Verschwendung von Speicher im Index führen, da dieser Speicher nicht mehr freigegeben wird. Hier ein Beispiel für ein solches Szenario:

In diesem Szenario werden die Indexblöcke, die mit den gelöschten Indexwerten verknüpft sind, nicht aufgefüllt, aber aufgrund der Verwendung von QUICK auch nicht mit anderen Indexblöcken zusammengefasst. Sie bleiben also unaufgefüllt, wenn weitere Einfügeoperationen auftreten, weil die neuen Datensätze keine Indexwerte im gelöschten Bereich haben. Außerdem bleiben sie auch dann unaufgefüllt, wenn Sie später DELETE ohne QUICK verwenden, sofern nicht zufällig einige der gelöschten Indexwerte in Indexblöcken innerhalb oder neben den nicht aufgefüllten Blöcken liegen. Um ungenutzten Indexspeicher unter diesen Umständen wieder freizugeben, verwenden Sie OPTIMIZE TABLE.

Wenn Sie viele Datensätze aus einer Tabelle zu löschen beabsichtigen, kann DELETE QUICK gefolgt von OPTIMIZE TABLE die schnellere Lösung sein. Hierbei wird der Index neu erstellt, statt zahlreiche Zusammenfassungsoperationen für Indexblöcke auszuführen.

Die MySQL-spezifische Option LIMIT row_count für DELETE nennt dem Server die maximale Anzahl Datensätze, die gelöscht werden, bevor die Kontrolle an den Client zurückgegeben wird. Hiermit können Sie gewährleisten, dass eine gegebene DELETE-Anweisung nicht zu viel Zeit in Anspruch nimmt. Sie können die DELETE-Anweisung dann einfach wiederholen, bis die Gesamtzahl betroffener Datensätze kleiner ist als der LIMIT-Wert.

Wenn die DELETE-Anweisung eine ORDER BY-Klausel enthält, werden die Datensätze in der in der Klausel angegebenen Reihenfolge gelöscht. Dies ist nur in Verbindung mit LIMIT wirklich nützlich. Die folgende Anweisung beispielsweise findet Datensätze, die der WHERE-Klausel entsprechen, sortiert sie nach timestamp_column und löscht den ersten (d. h. ältesten) Datensatz:

DELETE FROM somelog WHERE user = 'jcole'
ORDER BY timestamp_column LIMIT 1;

Sie können in einer DELETE-Anweisung mehrere Tabellen angeben, aus denen je nach Bedingung der WHERE-Klausel Datensätze gelöscht werden. Allerdings können Sie ORDER BY oder LIMIT nicht in einer DELETE-Anweisung für mehrere Tabellen verwenden. Die Klausel table_references listet die Tabellen auf, die im Join berücksichtigt werden. Die Syntax ist in Abschnitt 13.2.7.1, „JOIN“, beschrieben.

Bei der ersten Mehrtabellensyntax werden nur passende Datensätze aus den Tabellen gelöscht, die vor der FROM-Klausel aufgelistet sind. Bei der zweiten Mehrtabellensyntax werden nur passende Datensätze aus den Tabellen gelöscht, die in der FROM-Klausel (vor der USING-Klausel) aufgelistet sind. Die Folge ist, dass Sie Datensätze aus vielen Tabellen gleichzeitig löschen und gleichzeitig in weiteren Tabellen nur suchen lassen können:

DELETE t1, t2 FROM t1, t2, t3 WHERE t1.id=t2.id AND t2.id=t3.id;

Oder:

DELETE FROM t1, t2 USING t1, t2, t3 WHERE t1.id=t2.id AND t2.id=t3.id;

Diese Anweisungen verwenden auf der Suche nach zu löschenden Datensätzen alle drei Tabellen, aber gelöscht werden passende Datensätze tatsächlich nur aus den Tabellen t1 und t2.

Die obigen Beispiele zeigen innere Joins, die den Kommaoperator verwenden. DELETE-Anweisungen für mehrere Tabellen können jedoch einen beliebigen Join-Typ verwenden, der für SELECT-Anweisungen zulässig ist, also etwa LEFT JOIN.

Die Syntax erlaubt .* nach den Tabellennamen, um die Kompatibilität mit Access zu gewährleisten.

Wenn Sie eine DELETE-Anweisung für mehrere Tabellen verwenden, die auch InnoDB-Tabellen einbezieht, für die Fremdschlüssel-Constraints vorhanden sind, dann verarbeitet der MySQL-Optimierer die Tabellen unter Umständen in einer Reihenfolge, die sich von der ihrer Parent/Child-Beziehung unterscheidet. In diesem Fall schlägt die Anweisung fehl, und es wird ein Rollback durchgeführt. Stattdessen sollten Sie die Löschoperation dann aus nur einer Tabelle durchführen und sich auf die ON DELETE-Funktionalität verlassen, die InnoDB bietet, um andere Tabellen entsprechend zu ändern.

Hinweis: Wenn Sie einen Alias für eine Tabelle angegeben haben, müssen Sie diesen bei der Referenzierung der Tabelle verwenden:

DELETE t1 FROM test AS t1, test2 WHERE ...

Datenbankübergreifende Löschvorgänge werden von Löschvorgängen für mehrere Tabellen unterstützt. In diesem Fall müssen Sie die Tabellen aber ohne Verwendung von Aliasen referenzieren. Zum Beispiel:

DELETE test1.tmp1, test2.tmp2 FROM test1.tmp1, test2.tmp2 WHERE ...

Zurzeit können Sie in einer Unterabfrage keine Löschoperation in einer Tabelle durchführen und gleichzeitig eine Auswahl in einer anderen Tabelle treffen.

DO expr [, expr] ...

DO führt Ausdrücke aus, gibt aber keine Ergebnisse zurück. In den meisten Fällen ist DO eine Kurzform von SELECT expr, ..., hat aber den Vorteil, dass es etwas schneller ist, sofern das Ergebnis für Sie keine Rolle spielt. Allerdings meldet DO keine Fehler. Stattdessen werden Fehler als Warnungen ausgegeben.

DO ist in erster Linie bei Funktionen praktisch, die Begleiterscheinungen haben (z. B. RELEASE_LOCK()).

HANDLER tbl_name OPEN [ AS alias ]
HANDLER tbl_name READ index_name { = | >= | <= | < } (value1,value2,...)
    [ WHERE where_condition ] [LIMIT ... ]
HANDLER tbl_name READ index_name { FIRST | NEXT | PREV | LAST }
    [ WHERE where_condition ] [LIMIT ... ]
HANDLER tbl_name READ { FIRST | NEXT }
    [ WHERE where_condition ] [LIMIT ... ]
HANDLER tbl_name CLOSE

Die Anweisung HANDLER bietet direkten Zugriff auf die Schnittstellen der Speicher-Engine einer Tabelle. Sie steht für MyISAM- und InnoDB-Tabellen zur Verfügung.

Die Anweisung HANDLER ... OPEN öffnet eine Tabelle und gestattet den Zugriff über nachfolgende HANDLER ... READ-Anweisungen. Dieses Tabellenobjekt wird nicht mit anderen Threads gemeinsam genutzt und wird erst geschlossen, wenn der Thread HANDLER ... CLOSE aufruft oder beendet wird. Wenn Sie die Tabelle unter Verwendung eines Alias öffnen, müssen nachfolgende Referenzierungen durch andere HANDLER-Anweisungen den Alias statt des Tabellennamens verwenden.

Die erste HANDLER ... READ-Syntax holt einen Datensatz, bei dem der angegebene Index den übergebenen Werten entspricht und die WHERE-Bedingung erfüllt ist. Wenn Sie einen mehrspaltigen Index haben, geben Sie die Indexspaltenwerte als kommagetrennte Liste an. Entweder geben Sie Werte für alle Spalten im Index oder aber Werte für das links stehende Präfix der Indexspalten an. Angenommen, ein Index my_idx enthält drei Spalten namens col_a, col_b und col_c in genau dieser Reihenfolge. Die Anweisung HANDLER kann Werte für alle drei Spalten im Index oder für die Spalten in einem links stehenden Präfix angeben. Zum Beispiel:

HANDLER ... READ my_idx = (col_a_val,col_b_val,col_c_val) ...
HANDLER ... READ my_idx = (col_a_val,col_b_val) ...
HANDLER ... READ my_idx = (col_a_val) ...

Um die HANDLER-Schnittstelle für die Referenzierung des Primärschlüssels einer Tabelle zu verwenden, setzen Sie den Bezeichner in Anführungszeichen (`PRIMARY`).

HANDLER tbl_name READ `PRIMARY` ...

Die zweite HANDLER ... READ-Syntax holt aus der Tabelle einen Datensatz in der Indexreihenfolge, die der WHERE-Bedingung entspricht.

Die dritte HANDLER ... READ-Syntax holt aus der Tabelle einen Datensatz in der natürlichen Datensatzreihenfolge, die der WHERE-Bedingung entspricht. Sie ist schneller als HANDLER tbl_name READ index_name, wenn ein vollständiger Tabellenscan gewünscht wird. Die natürliche Datensatzreihenfolge ist die Reihenfolge, in der die Datensätze in der Datendatei einer MyISAM-Tabelle gespeichert sind. Diese Anweisung funktioniert auch bei InnoDB-Tabellen, aber das Konzept greift hier in Ermangelung einer separaten Datendatei nicht.

Ohne eine LIMIT-Klausel holen alle Formen von HANDLER ... READ einen einzelnen Datensatz (sofern vorhanden). Um eine bestimmte Anzahl Datensätze zurückzugeben, fügen Sie eine LIMIT-Klausel ein. Sie hat dieselbe Syntax wie bei der SELECT-Anweisung. Siehe auch Abschnitt 13.2.7, „SELECT“.

HANDLER ... CLOSE schließt eine Tabelle, die mit HANDLER ... OPEN geöffnet worden war.

HANDLER ist eine etwas maschinennahe Anweisung. Sie bietet z. B. keine Konsistenz: HANDLER ... OPEN fertigt keine Momentaufnahme der Tabelle an und sperrt sie nicht. Das bedeutet, dass, nachdem eine HANDLER ... OPEN-Anweisung abgesetzt wurde, die Tabellendaten (vom aktuellen, aber auch von anderen Threads) modifiziert werden können und diese Änderungen dann für HANDLER ... NEXT- oder HANDLER ... PREV-Scans unter Umständen nur teilweise sichtbar sind.

Es gibt eine Reihe von Gründen zur Verwendung der HANDLER-Schnittstelle anstelle einer normalen SELECT-Anweisung:

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name [(col_name,...)]
    VALUES ({expr | DEFAULT},...),(...),...
    [ ON DUPLICATE KEY UPDATE col_name=expr, ... ]

Oder:

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name
    SET col_name={expr | DEFAULT}, ...
    [ ON DUPLICATE KEY UPDATE col_name=expr, ... ]

Oder:

INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name [(col_name,...)]
    SELECT ...
    [ ON DUPLICATE KEY UPDATE col_name=expr, ... ]

INSERT fügt neue Datensätze in eine vorhandene Tabelle ein. Die Formen INSERT ... VALUES und INSERT ... SET der Anweisung fügen Datensätze basierend auf explizit angegebenen Werten ein. Die Form INSERT ... SELECT fügt Datensätze ein, die in einer oder mehreren anderen Tabellen ausgewählt wurden. INSERT ... SELECT wird in Abschnitt 13.2.4.1, „INSERT ... SELECT“, näher erläutert.

Sie können REPLACE anstelle von INSERT verwenden, um alte Datensätze zu überschreiben. REPLACE ist das Gegenstück zu INSERT IGNORE bei der Behandlung neuer Datensätze, deren eindeutige Schlüsselwerte Dubletten bereits vorhandener Datensätze sind: Die neuen Datensätze ersetzen die alten, statt verworfen zu werden. Siehe auch Abschnitt 13.2.6, „REPLACE“.

tbl_name ist die Tabelle, in die die Datensätze eingefügt werden sollen. Die Spalten, für die die Anweisung Werte angibt, lassen sich wie folgt angeben:

Spaltenwerte lassen sich auf mehrerlei Weise übergeben:

INSERT-Anweisungen, die die VALUES-Syntax verwenden, können mehrere Datensätze einfügen. Zu diesem Zweck fügen Sie mehrere Listen mit Spaltenwerten ein, die jeweils in Klammern gesetzt und durch Kommata getrennt sind. Beispiel:

INSERT INTO tbl_name (a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9);

Die Werteliste für jeden Datensatz muss in Klammern gesetzt werden. Die folgende Anweisung ist unzulässig, weil die Anzahl der Werte in der Liste nicht der Anzahl der Spaltennamen entspricht:

INSERT INTO tbl_name (a,b,c) VALUES(1,2,3,4,5,6,7,8,9);

Der Wert der von einer INSERT-Anweisung betroffenen Datensätze kann mit der C-API-Funktion mysql_affected_rows() ermittelt werden. Siehe auch Abschnitt 24.2.3.1, „mysql_affected_rows()“.

Wenn Sie eine INSERT ... VALUES-Anweisung mit mehreren Wertelisten oder INSERT ... SELECT verwenden, gibt die Anweisung einen Informations-String im folgenden Format zurück:

Records: 100 Duplicates: 0 Warnings: 0

Records gibt die Anzahl der von der Anweisung verarbeiteten Datensätze zurück. (Dies entspricht nicht unbedingt der Anzahl der tatsächlich eingefügten Datensätze, weil Duplicates auch ungleich null sein kann.) Duplicates gibt die Anzahl der Datensätze an, die nicht eingefügt wurden, weil eindeutige Schlüsselwerte andernfalls dupliziert worden wären. Warnings gibt die Anzahl der Spalteneinfügeversuche an, die aus irgendeinem Grund problematisch waren. Warnungen können unter den folgenden Umständen auftreten:

Wenn Sie die C-API verwenden, kann der Informations-String durch Aufruf der Funktion mysql_info() ermittelt werden. Siehe auch Abschnitt 24.2.3.34, „mysql_info()“.

Wenn INSERT einen Datensatz in eine Tabelle einfügt, die eine AUTO_INCREMENT-Spalte enthält, dann können Sie den Wert dieser Spalte mithilfe der SQL-Funktion LAST_INSERT_ID() abfragen. Aus der C-API heraus verwenden Sie die Funktion mysql_insert_id(). Sie sollten allerdings beachten, dass die beiden Funktionen sich nicht immer identisch verhalten. Das Verhalten von INSERT-Anweisungen in Bezug auf AUTO_INCREMENT-Spalten wird in Abschnitt 12.10.3, „Informationsfunktionen“, und Abschnitt 24.2.3.36, „mysql_insert_id()“, ausführlicher beschrieben.

Die INSERT-Anweisung unterstützt die folgenden Modifizierer:

INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name [(col_name,...)]
    SELECT ...
    [ ON DUPLICATE KEY UPDATE col_name=expr, ... ]

INSERT INTO tbl_temp2 (fld_id)
  SELECT tbl_temp1.fld_order_id
  FROM tbl_temp1 WHERE tbl_temp1.fld_order_id > 100;

INSERT DELAYED ...

INSERT INTO table (a,b,c) VALUES (1,2,3)
  ON DUPLICATE KEY UPDATE c=c+1;

UPDATE table SET c=c+1 WHERE a=1;

UPDATE table SET c=c+1 WHERE a=1 OR b=2 LIMIT 1;

INSERT INTO table (a,b,c) VALUES (1,2,3),(4,5,6)
  ON DUPLICATE KEY UPDATE c=VALUES(a)+VALUES(b);

INSERT INTO table (a,b,c) VALUES (1,2,3)
  ON DUPLICATE KEY UPDATE c=3;
INSERT INTO table (a,b,c) VALUES (4,5,6)
  ON DUPLICATE KEY UPDATE c=9;

LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name'
    [REPLACE | IGNORE]
    INTO TABLE tbl_name
    [FIELDS
        [TERMINATED BY 'string']
        [[OPTIONALLY] ENCLOSED BY 'char']
        [ESCAPED BY 'char']
    ]
    [LINES
        [STARTING BY 'string']
        [TERMINATED BY 'string']
    ]
    [IGNORE number LINES]
    [(col_name_or_user_var,...)]
    [SET col_name = expr,...)]

Die Anweisung LOAD DATA INFILE liest Datensätze mit extrem hoher Geschwindigkeit aus einer Textdatei in eine Tabelle ein. Der Dateiname muss als literaler String übergeben werden.

Die Syntax für die FIELDS- und LINES-Klauseln gilt auch für die SELECT ... INTO OUTFILE-Anweisung, die im weiteren Verlauf dieses Abschnitts beschrieben wird. (Siehe auch Abschnitt 13.2.7, „SELECT“.)

Weitere Informationen zur Effektivität von INSERT im Vergleich zu LOAD DATA INFILE und zur Beschleunigung von LOAD DATA INFILE finden Sie in Abschnitt 7.2.16, „Geschwindigkeit von INSERT-Anweisungen“.

Der von der Systemvariablen character_set_database angegebene Zeichensatz wird zur Interpretation der Daten in der Datei verwendet. SET NAMES und die Einstellung von character_set_client wirken sich hingegen nicht auf die Interpretation der Eingabe aus.

Beachten Sie, dass es derzeit nicht möglich ist, Datendateien zu laden, die den Zeichensatz ucs2 verwenden.

Sie können Datendateien auch mithilfe des Hilfsprogramms mysqlimport laden; dieses sendet dann eine LOAD DATA INFILE-Anweisung an den Server. Die Option --local bewirkt, dass mysqlimport Datendateien vom Clienthost liest. Sie können die Option --compress angeben, um bei langsamen Netzwerken eine bessere Leistung zu erzielen, sofern Client und Server das Komprimierungsprotokoll unterstützen. Siehe auch Abschnitt 8.12, „mysqlimport — Programm zum Datenimport“.

Wenn Sie das Schlüsselwort LOW_PRIORITY angeben, wird die Ausführung von LOAD DATA verzögert, bis kein Client mehr aus der Tabelle liest.

Geben Sie CONCURRENT bei einer MyISAM-Tabelle an, die die Bedingungen für gleichzeitige Einfügeoperationen erfüllt (d. h. keine freien Blöcke in der Mitte enthält), dann können andere Threads Daten aus der Tabelle abrufen, während LOAD DATA ausgeführt wird. Die Verwendung dieser Option beeinflusst die Leistungsfähigkeit von LOAD DATA auch dann geringfügig, wenn gleichzeitig kein anderer Thread die Tabelle verwendet.

Das Schlüsselwort LOCAL wird, sofern angegeben, in Bezug auf die Clientseite der Verbindung interpretiert:

Beachten Sie, dass, sofern keine LOCAL-Option angegeben wurde, die Regeln zur Folge haben, dass eine Datei namens ./myfile.txt im Datenverzeichnis des Servers gesucht wird, während die Datei myfile.txt aus dem Datenbankverzeichnis der Standarddatenbank gelesen wird. Ist also beispielsweise db1 die Standarddatenbank, dann liest die folgende LOAD DATA-Anweisung die Datei data.txt aus dem Datenbankverzeichnis für db1 aus, obwohl die Anweisung die Datei explizit in die Datenbank db2 lädt:

LOAD DATA INFILE 'data.txt' INTO TABLE db2.my_table;

Windows-Pfadnamen müssen unter Verwendung von Schrägstrichen (statt Backslashs) angegeben werden. Wenn Sie Backslashs (umgekehrte Schrägstriche) verwenden, müssen Sie sie doppelt angeben.

Aus Sicherheitsgründen müssen Textdateien, die auf dem Server ausgelesen werden, entweder im Datenbankverzeichnis liegen oder von allen gelesen werden können. Außerdem benötigen Sie zur Verwendung von LOAD DATA INFILE für Serverdateien die Berechtigung FILE. Siehe auch Abschnitt 5.8.3, „Von MySQL zur Verfügung gestellte Berechtigungen“.

Die Verwendung von LOCAL ist ein wenig langsamer als der Direktzugriff des Servers auf die Dateien, weil der Inhalt der Datei vom Client über die Verbindung an den Server gesendet werden muss. Die Berechtigung FILE benötigen Sie hingegen zum Laden lokaler Dateien nicht.

Die Option LOCAL funktioniert nur, wenn ihre Verwendung sowohl am Client als auch am Server aktiviert wurde. Wenn beispielsweise mysqld mit --local-infile=0 gestartet wurde, funktioniert LOCAL nicht. Siehe auch Abschnitt 5.7.4, „Sicherheitsprobleme mit LOAD DATA LOCAL“.

Unter Unix können Sie, wenn LOAD DATA über eine Pipe lesen soll, die folgende Methode verwenden (hierbei laden wir das Listing des Verzeichnisses / in eine Tabelle):

mkfifo /mysql/db/x/x
chmod 666 /mysql/db/x/x
find / -ls > /mysql/db/x/x
mysql -e "LOAD DATA INFILE 'x' INTO TABLE x" x

Die Schlüsselwörter REPLACE und IGNORE steuern den Umgang mit eingegebenen Datensätzen, die Duplikate vorhandener Datensätze mit eindeutigen Schlüsselwerten darstellen.

Wenn Sie REPLACE angeben, ersetzt die Eingabe vorhandene Datensätze. Dies betrifft Datensätze, die denselben Wert bei einem Primärschlüssel oder einem eindeutigen Index wie die neuen Datensätze haben. Siehe auch Abschnitt 13.2.6, „REPLACE“.

Bei Angabe von IGNORE hingegen werden neue Datensätze, deren eindeutiger Schlüsselwert bereits einem vorhandenen Datensatz zugewiesen ist, ignoriert. Wenn Sie keine Option angeben, hängt das Verhalten davon ab, ob das Schlüsselwort LOCAL angegeben wird. Ohne LOCAL tritt ein Fehler auf, wenn ein doppelter Schlüsselwert gefunden wird; in diesem Fall wird der Rest der Textdatei ignoriert. Mit LOCAL ist das Standardverhalten so, als ob IGNORE angegeben worden wäre. Das liegt daran, dass der Server keine Möglichkeit hat, die Übertragung der Datei während des Vorgangs zu beenden.

Wenn Sie Fremdschlüssel-Constraints während des Ladevorgangs ignorieren wollen, können Sie eine SET FOREIGN_KEY_CHECKS=0-Anweisung vor der Ausführung von LOAD DATA absetzen.

Wenn Sie LOAD DATA INFILE auf eine leere MyISAM-Tabelle anwenden, werden alle nichteindeutigen Indizes in einer separaten Stapeloperation erstellt (wie bei REPAIR TABLE). Dies sollte LOAD DATA INFILE erheblich beschleunigen, wenn Sie viele Indizes haben. In einigen Extremfällen können Sie die Indizes noch schneller erstellen, indem Sie sie vor dem Einladen der Datei in die Tabelle mit ALTER TABLE ... DISABLE KEYS abschalten und sie nach Abschluss des Ladevorgangs mit ALTER TABLE ... ENABLE KEYS neu erstellen. Siehe auch Abschnitt 7.2.16, „Geschwindigkeit von INSERT-Anweisungen“.

LOAD DATA INFILE ist das Gegenstück zu SELECT ... INTO OUTFILE. (Siehe auch Abschnitt 13.2.7, „SELECT“.) Um Daten aus einer Tabelle in eine Datei zu schreiben, benutzen Sie SELECT ... INTO OUTFILE; wollen Sie die Datei wieder in eine Tabelle einlesen, dann verwenden Sie LOAD DATA INFILE. Die Syntax der Klauseln FIELDS und LINES ist bei beiden Anweisungen dieselbe. Beide sind optional, aber FIELDS muss LINES vorangehen, sofern beide angegeben sind.

Wenn Sie eine FIELDS-Klausel festlegen, ist auch jede einzelne Unterklausel (TERMINATED BY, [OPTIONALLY] ENCLOSED BY und ESCAPED BY) ebenfalls optional – Sie müssen lediglich mindestens eine dieser Unterklauseln angeben.

Wenn Sie keine FIELDS-Klausel angeben, entsprechen die Vorgabeeinstellungen dem Folgenden:

FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\'

Wenn Sie keine LINES-Klausel angeben, entsprechen die Vorgabeeinstellungen dem Folgenden:

LINES TERMINATED BY '\n' STARTING BY ''

Anders gesagt, die Standardwerte bewirken, dass LOAD DATA INFILE sich beim Einlesen wie folgt verhält:

Umgekehrt bewirken die Voreinstellungen, dass sich SELECT ... INTO OUTFILE beim Schreiben einer Ausgabe wie folgt verhält:

Der Backslash wird bei MySQL als Escape-Zeichen in Strings verwendet. Insofern müssen Sie, um FIELDS ESCAPED BY '\\' zu schreiben, zwei Backslashes angeben, damit der Wert als ein Backslash interpretiert wird.

Hinweis: Wenn Sie die Textdatei auf einem Windows-System erzeugt haben, müssen Sie unter Umständen LINES TERMINATED BY '\r\n' verwenden, um die Datei korrekt einzulesen, weil Windows-Programme normalerweise zwei Zeichen als Zeilenendzeichen verwenden. Einige Programme wie WordPad verwenden beim Schreiben von Dateien unter Umständen sogar \r als Zeilenendzeichen. Um solche Dateien zu lesen, verwenden Sie LINES TERMINATED BY '\r'.

Wenn alle Zeilen, die Sie einlesen wollen, ein gemeinsames Präfix aufweisen, das Sie ignorieren wollen, können Sie mit LINES STARTING BY 'prefix_string' das Präfix und alles, was davor steht, überspringen. Enthält eine Zeile das Präfix gar nicht, dann wird die gesamte Zeile übersprungen. Nehmen wir an, dass Sie folgende Anweisung absetzen:

LOAD DATA INFILE '/tmp/test.txt' INTO TABLE test
  FIELDS TERMINATED BY ','  LINES STARTING BY 'xxx';

Nehmen wir ferner an, dass die Datendatei wie folgt aussieht:

xxx"abc",1
something xxx"def",2
"ghi",3

In diesem Fall werden als Ergebnis die Datensätze ("abc",1) und ("def",2) erzeugt. Der dritte Datensatz in der Datei wird übersprungen, weil er das Präfix nicht enthält.

Die Option IGNORE number LINES kann verwendet werden, um Zeilen am Anfang der Datei zu ignorieren. So können Sie etwa IGNORE 1 LINES angeben, um eine Kopfzeile zu überspringen, die etwa die Spaltennamen enthält:

LOAD DATA INFILE '/tmp/test.txt' INTO TABLE test IGNORE 1 LINES;

Wenn Sie SELECT ... INTO OUTFILE gemeinsam mit LOAD DATA INFILE verwenden, um Daten aus einer Datenbank in eine Datei zu schreiben und die Datei später wieder in die Datenbank einzulesen, dann müssen die Optionen der beiden Anweisungen zur Behandlung von Feldern und Zeilen einander entsprechen. Andernfalls kann LOAD DATA INFILE den Inhalt der Datei nicht korrekt einlesen. Angenommen, Sie schreiben mit SELECT ... INTO OUTFILE eine Datei mit Feldern, die kommagetrennt sind:

SELECT * INTO OUTFILE 'data.txt'
  FIELDS TERMINATED BY ','
  FROM table2;

Um diese kommagetrennte Datei wieder einzulesen, müssen Sie folgende Anweisung verwenden:

LOAD DATA INFILE 'data.txt' INTO TABLE table2
  FIELDS TERMINATED BY ',';

Haben Sie stattdessen probiert, die Datei mit der folgenden Anweisung einzulesen, dann ist dies nicht gelungen, da LOAD DATA INFILE angewiesen wird, nach Tabulatoren zwischen den Feldern zu suchen:

LOAD DATA INFILE 'data.txt' INTO TABLE table2
  FIELDS TERMINATED BY '\t';

Infolgedessen wird jede Eingabezeile wohl als separates Feld interpretiert werden.

Mit LOAD DATA INFILE können Sie Dateien lesen, die Sie aus externen Quellen erhalten haben. Beispielsweise exportieren viele Programme Daten als kommagetrennte Werte im so genannten CSV-Format (Comma-Separate Values). Hierbei werden die Felder durch Kommata voneinander getrennt und in doppelte Anführungszeichen gesetzt. Wenn Zeilen in einer solchen Datei durch Zeilenumbrüche abgeschlossen werden, veranschaulicht die folgende Anweisung die Optionen zur Feld- und Zeilenbehandlung, wie Sie sie zum Laden der Datei verwenden würden:

LOAD DATA INFILE 'data.txt' INTO TABLE tbl_name
  FIELDS TERMINATED BY ',' ENCLOSED BY '"'
  LINES TERMINATED BY '\n';

Alle Optionen zur Feld- und Zeilenbehandlung können einen Leer-String ('') angeben. Wenn der String nicht leer ist, müssen die Werte FIELDS [OPTIONALLY] ENCLOSED BY und FIELDS ESCAPED BY genau ein Zeichen umfassen. Die Werte FIELDS TERMINATED BY, LINES STARTING BY und LINES TERMINATED BY können hingegen auch mehrere Zeichen enthalten. Um etwa Zeilen zu schreiben, die jeweils aus einem aus einer Absatzschaltung und einem Zeilenvorschub bestehenden Zeichenpaar bestehen, geben Sie die Klausel LINES TERMINATED BY '\r\n' an.

Um eine Datei mit Witzen zu lesen, die durch aus %% bestehenden Zeilen getrennt sind, können Sie Folgendes tun:

CREATE TABLE jokes
  (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  joke TEXT NOT NULL);
LOAD DATA INFILE '/tmp/jokes.txt' INTO TABLE jokes
  FIELDS TERMINATED BY ''
  LINES TERMINATED BY '\n%%\n' (joke);

FIELDS [OPTIONALLY] ENCLOSED BY steuert die Anführungszeichen um die Felder. Bei der Ausgabe (SELECT ... INTO OUTFILE) werden, wenn Sie das Wort OPTIONALLY weglassen, alle Felder vom durch ENCLOSED BY angegebenen Zeichen umschlossen. Hier ein Beispiel für eine solche Ausgabe (mit dem Komma als Feldtrennzeichen):

"1","a string","100.20"
"2","a string containing a , comma","102.20"
"3","a string containing a \" quote","102.20"
"4","a string containing a \", quote and comma","102.20"

Wenn Sie OPTIONALLY angeben, wird das ENCLOSED BY-Zeichen nur zum Umschließen von Werten in Spalten verwendet, die einen String-Datentyp haben (z. B. CHAR, BINARY, TEXT oder ENUM):

1,"a string",100.20
2,"a string containing a , comma",102.20
3,"a string containing a \" quote",102.20
4,"a string containing a \", quote and comma",102.20

Beachten Sie, dass das Auftreten des ENCLOSED BY-Zeichens in einem Feldwert gekennzeichnet wird, indem man ihm das ESCAPED BY-Zeichen voranstellt. Ferner ist wichtig, dass es, wenn Sie einen leeren ESCAPED BY-Wert angeben, möglich ist, versehentlich eine Ausgabe zu erzeugen, die von LOAD DATA INFILE nicht korrekt eingelesen werden kann. Die obige Ausgabe beispielsweise würde wie folgt angezeigt werden, wenn das Escape-Zeichen leer wäre. Sie werden bemerken, dass das zweite Feld in der vierten Zeile ein auf das Anführungszeichen folgendes Komma enthält, welches das Feld abzuschließen scheint (was nicht zutrifft).

1,"a string",100.20
2,"a string containing a , comma",102.20
3,"a string containing a " quote",102.20
4,"a string containing a ", quote and comma",102.20

Bei der Eingabe wird das ENCLOSED BY-Zeichen – sofern vorhanden – am Ende der jeweiligen Feldwerte entfernt. (Dies erfolgt unabhängig davon, ob OPTIONALLY angegeben ist oder nicht – OPTIONALLY hat keine Auswirkung auf die Eingabeinterpretation.) Instanzen des ENCLOSED BY-Zeichens, denen das ESCAPED BY-Zeichen vorangestellt ist, werden als Teil des aktuellen Feldwerts interpretiert.

Beginnt das Feld mit dem ENCLOSED BY-Zeichen, dann werden Instanzen dieses Zeichens nur dann als Feldtrennzeichen ausgewertet, wenn ihnen jeweils die TERMINATED BY-Sequenz für Feld oder Zeile folgt. Um Mehrdeutigkeiten zu vermeiden, können Fälle des ENCLOSED BY-Zeichens innerhalb eines Feldwerts verdoppelt werden; sie werden dann als einzelne Instanz des Zeichens ausgewertet. Wird also etwa ENCLOSED BY '"' angegeben, dann wird mit Anführungszeichen wie hier gezeigt verfahren:

"The ""BIG"" boss"  -> The "BIG" boss
The "BIG" boss      -> The "BIG" boss
The ""BIG"" boss    -> The ""BIG"" boss

FIELDS ESCAPED BY steuert, wie Sonderzeichen geschrieben oder gelesen werden. Wenn das FIELDS ESCAPED BY-Zeichen nicht leer ist, dann wird es bei der Ausgabe als Präfix für die folgenden Zeichen verwendet:

Ist das FIELDS ESCAPED BY-Zeichen leer, dann werden keinerlei Zeichen gekennzeichnet und NULL wird als NULL (und nicht als \N) ausgegeben. Es ist wahrscheinlich nicht angeraten, ein leeres Escape-Zeichen anzugeben; dies gilt insbesondere dann, wenn Feldwerte in Ihren Daten eines der in der obigen Liste enthaltenen Zeichen enthalten.

Wenn das FIELDS ESCAPED BY-Zeichen nicht leer ist, werden Instanzen dieses Zeichens bei der Eingabe entfernt, und das nachfolgende Zeichen wird literal als Teil des Feldwerts verwendet. Ausnahmen sind die gekennzeichneten Zeichen ‘0’ oder ‘N’ (z. B. \0 oder \N, wenn das Escape-Zeichen ‘\’ ist). Diese Sequenzen werden als ASCII-NUL (Nullwertbyte) bzw. als NULL interpretiert. Die Regeln für den Umgang mit NULL werden im weiteren Verlauf dieses Abschnitts beschrieben.

Weitere Informationen zur ‘\’-Escape-Syntax finden Sie in Abschnitt 9.1, „Literale: wie Strings und Zahlen geschrieben werden“.

In bestimmten Fällen interagieren die Optionen zur Feld- und Zeilenbehandlung:

Die Handhabung von NULL-Werten variiert entsprechend den verwendeten FIELDS- und LINES-Optionen:

Manche Fälle werden von LOAD DATA INFILE nicht unterstützt:

Das folgende Beispiel lädt alle Spalten der Tabelle persondata:

LOAD DATA INFILE 'persondata.txt' INTO TABLE persondata;

Standardmäßig wird, wenn keine Spaltenliste am Ende der LOAD DATA INFILE-Anweisung vorhanden ist, erwartet, dass Eingabezeilen ein Feld für jede Tabellenspalte enthalten. Wenn Sie nur einen Teil der Spalten einer Tabelle laden wollen, müssen Sie eine Spaltenliste angeben:

LOAD DATA INFILE 'persondata.txt' INTO TABLE persondata (col1,col2,...);

Ferner müssen Sie eine Spaltenliste festlegen, wenn die Reihenfolge der Felder in der Eingabedatei sich von der Reihenfolge der Spalten in der Tabelle unterscheidet. Andernfalls kann MySQL nicht ermitteln, wie die Eingabefelder den Tabellenspalten zuzuordnen sind.

Die Spaltenliste kann entweder Spaltennamen oder Benutzervariablen enthalten. Bei Benutzervariablen gestattet Ihnen die SET-Klausel die Durchführung einer Werteumwandlung vor der Zuweisung des Ergebnisses zu den Spalten.

Benutzervariablen in der SET-Klausel können auf unterschiedliche Weise verwendet werden. Die folgenden Werte benutzen die erste Eingabespalte direkt für den Wert von t1.column1 und weisen die zweite Eingabespalte einer Benutzervariablen zu, an der eine Division durchgeführt wird, bevor sie als Wert für t1.column2 verwendet wird:

LOAD DATA INFILE 'file.txt'
  INTO TABLE t1
  (column1, @var1)
  SET column2 = @var1/100;

Die SET-Klausel kann zur Übermittlung von Werten benutzt werden, die nicht der Eingabedatei entnommen wurden. Die folgende Anweisung weist column3 das aktuelle Datum und die Uhrzeit zu:

LOAD DATA INFILE 'file.txt'
  INTO TABLE t1
  (column1, column2)
  SET column3 = CURRENT_TIMESTAMP;

Sie können einen Eingabewert auch verwerfen, indem Sie ihn einer Benutzervariablen zuweisen, der Variablen aber keine Tabellenspalte zuordnen:

LOAD DATA INFILE 'file.txt'
  INTO TABLE t1
  (column1, @dummy, column2, @dummy, column3);

Die Verwendung der Spalten-/Variablenliste und der SET-Klausel ist den folgenden Einschränkungen unterworfen:

Wenn eine Eingabezeile verarbeitet wird, unterteilt LOAD DATA sie in Felder und verwendet die Werte entsprechend der Spalten-/Variablenliste und der SET-Klausel, sofern diese vorhanden sind. Der Ergebnisdatensatz wird dann in die Tabelle eingefügt. Sind BEFORE INSERT- oder AFTER INSERT-Trigger für die Tabelle vorhanden, so werden diese vor bzw. nach dem Einfügen des Datensatzes aktiviert.

Hat eine Eingabezeile zu viele Felder, dann werden die überzähligen Felder ignoriert und die Anzahl der Warnungen wird erhöht.

Hat eine Eingabezeile zu wenig Felder, dann werden die Tabellenspalten, deren Eingabefelder fehlen, auf ihre Standardwerte gesetzt. Die Zuweisung von Standardwerten ist in Abschnitt 11.1.4, „Vorgabewerte von Datentypen“, beschrieben.

Ein leerer Feldwert wird anders interpretiert als ein fehlender Feldwert:

Dies sind dieselben Werte, die als Ergebnis ausgegeben werden, wenn Sie einem String, einem numerischen oder einem zeitbezogenen Wert in einer INSERT- oder UPDATE-Anweisung explizit den Leer-String zuweisen.

TIMESTAMP-Spalten werden nur dann auf die aktuellen Werte für Datum und Uhrzeit gesetzt, wenn ein NULL-Wert (d. h. \N) für die Spalte vorhanden ist oder der Standardwert der TIMESTAMP-Spalte der aktuelle Zeitstempel ist und in der Feldliste (sofern vorhanden) weggelassen wurde.

LOAD DATA INFILE betrachtet alle Eingaben als Strings, d. h., Sie können für ENUM- oder SET-Spalten numerische Werte nicht so eingeben wie von INSERT-Anweisungen her gewohnt. Alle ENUM- und SET-Werte müssen als Strings angegeben werden.

Wenn die LOAD DATA INFILE-Anweisung abgeschlossen ist, gibt sie einen Informations-String im folgenden Format zurück:

Records: 1  Deleted: 0  Skipped: 0  Warnings: 0

Wenn Sie die C-API verwenden, können Sie Informationen zur Anweisung durch einen Aufruf der Funktion mysql_info() ermitteln. Siehe auch Abschnitt 24.2.3.34, „mysql_info()“.

Warnungen treten unter denselben Umständen auf wie beim Einfügen von Werten mithilfe der INSERT-Anweisung (siehe Abschnitt 13.2.4, „INSERT“); der einzige Unterschied besteht darin, dass LOAD DATA INFILE auch dann Warnungen erzeugt, wenn zu wenig oder zu viel Felder im Eingabedatensatz vorhanden sind. Die Warnungen werden nirgends gespeichert; Sie können ihrer Anzahl lediglich entnehmen, ob alles geklappt hat oder nicht.

Mit SHOW WARNINGS erhalten Sie eine Liste der ersten max_error_count Warnungen mit Informationen dazu, was unter Umständen nicht geklappt hat. Siehe auch Abschnitt 13.5.4.25, „SHOW WARNINGS“.

REPLACE [LOW_PRIORITY | DELAYED]
    [INTO] tbl_name [(col_name,...)]
    VALUES ({expr | DEFAULT},...),(...),...

Oder:

REPLACE [LOW_PRIORITY | DELAYED]
    [INTO] tbl_name
    SET col_name={expr | DEFAULT}, ...

Oder:

REPLACE [LOW_PRIORITY | DELAYED]
    [INTO] tbl_name [(col_name,...)]
    SELECT ...

REPLACE funktioniert auf exakt gleiche Weise wie INSERT. Der Unterschied besteht darin, dass, wenn ein alter Datensatz denselben Wert wie ein neuer Datensatz für einen Primärschlüssel oder einen eindeutigen Index hat, der alte Datensatz gelöscht wird, bevor der neue eingefügt wird. Siehe auch Abschnitt 13.2.4, „INSERT“.

REPLACE ist eine MySQL-Erweiterung zum SQL-Standard. Es kann Datensätze einfügen, oder es kann Datensätze löschen und einfügen. Wenn Sie eine Anweisung suchen, die dem SQL-Standard entspricht und die entweder einfügt oder aktualisiert, dann benutzen Sie die INSERT ... ON DUPLICATE KEY UPDATE-Anweisung (siehe auch Abschnitt 13.2.4.3, „INSERT ... ON DUPLICATE KEY UPDATE“).

Beachten Sie, dass, sofern die Tabelle nicht einen Primärschlüssel oder einen eindeutigen Index hat, die Verwendung von REPLACE unsinnig ist. Die Anweisung wäre dann mit INSERT identisch, weil kein Index vorhanden ist, anhand dessen bestimmt werden könnte, ob ein neuer Datensatz ein Duplikat eines bereits vorhandenen darstellt.

Werte für alle Spalten werden den Werten entnommen, die in der REPLACE-Anweisung angegeben sind. Fehlende Spalten werden – wie bei INSERT – auf ihre Standardwerte gesetzt. Es ist nicht möglich, Werte aus dem aktuellen Datensatz zu referenzieren und diese dann im neuen Datensatz zu verwenden. Wenn Sie eine Zuordnung wie SET col_name = col_name + 1 verwenden, wird die Referenzierung des Spaltennamens auf der rechten Seite wie DEFAULT(col_name) behandelt, d. h., die Zuordnung ist äquivalent zu SET col_name = DEFAULT(col_name) + 1.

Um REPLACE zu verwenden, benötigen Sie die Berechtigungen INSERT und DELETE für die Tabelle.

Die REPLACE-Anweisung gibt einen Wert zurück, der die Anzahl der betroffenen Datensätze angibt. Konkret ist dies die Summe der gelöschten und eingefügten Datensätze. Wenn der Wert bei einer REPLACE-Anweisung für einen Datensatz 1 beträgt, dann wurde ein Datensatz eingefügt und keiner gelöscht. Ist der Wert größer als 1, dann wurden ein oder mehrere alte Datensätze gelöscht, bevor der neue Datensatz eingefügt wurde. Ein einzelner Datensatz kann durchaus mehr als nur einen alten Datensatz ersetzen, sofern die Tabelle mehrere eindeutige Indizes hat und der neue Datensatz Werte verschiedener alter Datensätze in verschiedenen eindeutigen Indizes dupliziert.

Die Anzahl der betroffenen Datensätze erlaubt also die einfache Bestimmung, ob REPLACE nur einen Datensatz hinzugefügt hat oder ob auch Datensätze ersetzt wurden: Überprüfen Sie, ob der Wert 1 (Hinzufügung) oder größer ist (Ersetzung).

Wenn Sie die C-API verwenden, kann die Anzahl der betroffenen Datensätze durch die Funktion mysql_affected_rows() ermittelt werden.

Zurzeit können Sie in einer Unterabfrage keine Ersetzungsoperation in einer Tabelle durchführen und gleichzeitig eine Auswahl in einer anderen Tabelle treffen.

MySQL verwendet für REPLACE (und LOAD DATA ... REPLACE) den folgenden Algorithmus:

SELECT
    [ALL | DISTINCT | DISTINCTROW ]
      [HIGH_PRIORITY]
      [STRAIGHT_JOIN]
      [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
      [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
    select_expr, ...
    [FROM table_references
    [WHERE where_condition]
    [GROUP BY {col_name | expr | position}
      [ASC | DESC], ... [WITH ROLLUP]]
    [HAVING where_condition]
    [ORDER BY {col_name | expr | position}
      [ASC | DESC], ...]
    [LIMIT {[offset,] row_count | row_count OFFSET offset}]
    [PROCEDURE procedure_name(argument_list)]
    [INTO OUTFILE 'file_name' export_options
      | INTO DUMPFILE 'file_name']
    [FOR UPDATE | LOCK IN SHARE MODE]]

SELECT wird zum Abrufen von Datensätzen verwendet, die aus einer oder mehreren Tabellen ausgewählt wurden, und kann UNION-Anweisungen und Unterabfragen enthalten. Siehe auch Abschnitt 13.2.7.2, „UNION“, und Abschnitt 13.2.8, „Syntax von Unterabfragen“.

Die meistverwendeten Klauseln für SELECT sind die folgenden:

SELECT kann auch verwendet werden, um Datensätze abzurufen, die ohne Referenzierung einer Tabelle berechnet wurden.

Zum Beispiel:

mysql> SELECT 1 + 1;
        -> 2

Sie können DUAL als Pseudotabellenname in Situationen angeben, in denen keine Tabellen referenziert werden:

mysql> SELECT 1 + 1 FROM DUAL;
        -> 2

DUAL ist ausschließlich zum Zweck der Kompatibilität mit anderen Datenbankservern vorhanden, die eine FROM-Klausel erfordern. MySQL benötigt keine solche Klausel, wenn keine Tabellen referenziert werden.

Generell müssen Klauseln in genau der Reihenfolge angegeben werden, die in der Syntaxbeschreibung erscheint. So muss etwa eine HAVING-Klausel hinter den GROUP BY-Klauseln und vor den ORDER BY-Klauseln stehen. Die einzige Ausnahme ist die INTO-Klausel, die entweder wie in der Syntaxbeschreibung gezeigt oder unmittelbar vor der FROM-Klausel stehen kann.

Sie können auf das Schlüsselwort SELECT folgend eine Reihe von Optionen angeben, die den Betrieb der Anweisung beeinflusst.

Die Optionen ALL, DISTINCT und DISTINCTROW geben an, ob doppelte Datensätze zurückgegeben werden sollen. Wird keine dieser Optionen angegeben, dann wird ALL als Vorgabe vorausgesetzt (d. h., alle passenden Datensätze werden zurückgegeben). DISTINCT und DISTINCTROW sind Synonyme; sie legen fest, dass doppelte Datensätze aus der Ergebnismenge entfernt werden.

HIGH_PRIORITY, STRAIGHT_JOIN und Optionen, die mit SQL_ beginnen, sind MySQL-Erweiterungen zum SQL-Standard.

table_references:
    table_reference [, table_reference] ...

table_reference:
    table_factor
  | join_table

table_factor:
    tbl_name [[AS] alias]
        [{USE|IGNORE|FORCE} INDEX (key_list)]
  | ( table_references )
  | { OJ table_reference LEFT OUTER JOIN table_reference
        ON conditional_expr }

join_table:
    table_reference [INNER | CROSS] JOIN table_factor [join_condition]
  | table_reference STRAIGHT_JOIN table_factor
  | table_reference STRAIGHT_JOIN table_factor ON condition
  | table_reference LEFT [OUTER] JOIN table_reference join_condition
  | table_reference NATURAL [LEFT [OUTER]] JOIN table_factor
  | table_reference RIGHT [OUTER] JOIN table_reference join_condition
  | table_reference NATURAL [RIGHT [OUTER]] JOIN table_factor

join_condition:
    ON conditional_expr
  | USING (column_list)

SELECT * FROM t1 LEFT JOIN (t2, t3, t4)
                 ON (t2.a=t1.a AND t3.b=t1.b AND t4.c=t1.c)

SELECT * FROM t1 LEFT JOIN (t2 CROSS JOIN t3 CROSS JOIN t4)
                 ON (t2.a=t1.a AND t3.b=t1.b AND t4.c=t1.c)

SELECT * FROM table1,table2 WHERE table1.id=table2.id;

SELECT * FROM table1 LEFT JOIN table2 ON table1.id=table2.id;

SELECT * FROM table1 LEFT JOIN table2 USING (id);

SELECT * FROM table1 LEFT JOIN table2 ON table1.id=table2.id
  LEFT JOIN table3 ON table2.id=table3.id;

SELECT * FROM table1 USE INDEX (key1,key2)
  WHERE key1=1 AND key2=2 AND key3=3;

SELECT * FROM table1 IGNORE INDEX (key3)
  WHERE key1=1 AND key2=2 AND key3=3;

SELECT ...
UNION [ALL | DISTINCT] SELECT ...
[UNION [ALL | DISTINCT] SELECT ...]

(SELECT a FROM t1 WHERE a=10 AND B=1)
UNION
(SELECT a FROM t2 WHERE a=11 AND B=2)
ORDER BY a LIMIT 10;

(SELECT a AS b FROM t) UNION (SELECT ...) ORDER BY b;
(SELECT a AS b FROM t) UNION (SELECT ...) ORDER BY a;

(SELECT a FROM tbl_name WHERE a=10 AND B=1 ORDER BY a LIMIT 10)
UNION
(SELECT a FROM tbl_name WHERE a=11 AND B=2 ORDER BY a LIMIT 10);

mysql> SELECT REPEAT('a',1) UNION SELECT REPEAT('b',10);
+---------------+
| REPEAT('a',1) |
+---------------+
| a             |
| bbbbbbbbbb    |
+---------------+

Eine Unterabfrage ist eine SELECT-Anweisung innerhalb einer anderen Anweisung.

Seit MySQL 4.1 werden alle Unterabfrageformen und -operationen, die der SQL-Standard vorsieht, ebenso unterstützt wie einige weitere Funktionen, die MySQL-spezifisch sind.

Hier ein Beispiel für eine Unterabfrage:

SELECT * FROM t1 WHERE column1 = (SELECT column1 FROM t2);

In diesem Beispiel ist SELECT * FROM t1 ... die äußere Abfrage (oder äußere Anweisung), und (SELECT column1 FROM t2) ist die Unterabfrage. Man sagt, die Unterabfrage ist in der äußeren Abfrage verschachtelt, und tatsächlich ist es möglich, Unterabfragen bis zu einer beträchtlichen Tiefe zu verschachteln. Eine Unterabfrage muss immer in Klammern stehen.

Unterabfragen haben die folgenden Vorteile:

Hier eine Beispielanweisung, die die wichtigsten Aspekte der Unterabfragensyntax zeigt, wie sie im SQL-Standard spezifiziert ist und von MySQL unterstützt wird:

DELETE FROM t1
WHERE s11 > ANY
(SELECT COUNT(*) /* no hint */ FROM t2
WHERE NOT EXISTS
(SELECT * FROM t3
WHERE ROW(5*t2.s1,77)=
(SELECT 50,11*s1 FROM t4 UNION SELECT 50,77 FROM
(SELECT * FROM t5) AS t5)));

Eine Unterabfrage kann einen Skalar (einen einzelnen Wert), einen einzelnen Datensatz, eine einzelne Spalte oder eine Tabelle (d. h. eine oder mehrere Spalten von einem oder mehreren Datensätzen) zurückgeben. Man bezeichnet diese als Skalar-, Spalten-, Datensatz- und Tabellenunterabfragen. Unterabfragen, die einen bestimmten Ergebnistyp zurückgeben, können häufig nur in bestimmten Kontexten verwendet werden, die in den folgenden Abschnitten beschrieben werden.

Es gibt ein paar wenige Einschränkungen bezüglich des Typs der Anweisungen, in denen Unterabfragen verwendet werden können. Eine Unterabfrage kann die gleichen Schlüsselwörter oder Klauseln enthalten wie eine gewöhnliche SELECT-Anweisung: DISTINCT, GROUP BY, ORDER BY, LIMIT, Joins, Indexhinweise, UNION-Konstrukte, Kommentare, Funktionen usw.

Eine Einschränkung besteht darin, dass die äußere Anweisung einer Unterabfrage einen der folgenden Typen aufweisen muss: SELECT, INSERT, UPDATE, DELETE, SET oder DO. Eine weitere Einschränkung ist die Tatsache, dass Sie eine Tabelle derzeit nicht ändern und gleichzeitig in einer Unterabfrage eine Auswahl aus dieser Tabelle treffen können. Dies gilt für Anweisungen wie DELETE, INSERT, REPLACE, UPDATE und (da Unterabfragen in der SET-Klausel verwendet werden können) LOAD DATA INFILE. Eine umfassendere Beschreibung der Einschränkungen bei Unterabfragen finden Sie in Abschnitt I.3, „Beschränkungen von Unterabfragen“.

CREATE TABLE t1 (s1 INT, s2 CHAR(5) NOT NULL);
INSERT INTO t1 VALUES(100, 'abcde');
SELECT (SELECT s2 FROM t1);

CREATE TABLE t1 (s1 INT);
INSERT INTO t1 VALUES (1);
CREATE TABLE t2 (s1 INT);
INSERT INTO t2 VALUES (2);

SELECT (SELECT s1 FROM t2) FROM t1;

SELECT UPPER((SELECT s1 FROM t1)) FROM t2;

non_subquery_operand comparison_operator (subquery)

=  >  <  >=  <=  <>

  ... 'a' = (SELECT column1 FROM t1)

SELECT column1 FROM t1
WHERE column1 = (SELECT MAX(column2) FROM t2);

SELECT * FROM t1 AS t
WHERE 2 = (SELECT COUNT(*) FROM t1 WHERE t1.id = t.id);

operand comparison_operator ANY (subquery)
operand IN (subquery)
operand comparison_operator SOME (subquery)

SELECT s1 FROM t1 WHERE s1 > ANY (SELECT s1 FROM t2);

SELECT s1 FROM t1 WHERE s1 = ANY (SELECT s1 FROM t2);
SELECT s1 FROM t1 WHERE s1 IN    (SELECT s1 FROM t2);

SELECT s1 FROM t1 WHERE s1 <> ANY  (SELECT s1 FROM t2);
SELECT s1 FROM t1 WHERE s1 <> SOME (SELECT s1 FROM t2);

operand comparison_operator ALL (subquery)

SELECT s1 FROM t1 WHERE s1 > ALL (SELECT s1 FROM t2);

SELECT * FROM t1 WHERE 1 > ALL (SELECT s1 FROM t2);

SELECT * FROM t1 WHERE 1 > (SELECT s1 FROM t2);

SELECT * FROM t1 WHERE 1 > ALL (SELECT MAX(s1) FROM t2);

SELECT s1 FROM t1 WHERE s1 <> ALL (SELECT s1 FROM t2);
SELECT s1 FROM t1 WHERE s1 NOT IN (SELECT s1 FROM t2);

SELECT * FROM t1 WHERE (1,2) = (SELECT column1, column2 FROM t2);
SELECT * FROM t1 WHERE ROW(1,2) = (SELECT column1, column2 FROM t2);

  SELECT * FROM t1 WHERE (column1,column2) = (1,1);
  SELECT * FROM t1 WHERE column1 = 1 AND column2 = 1;

SELECT column1,column2,column3
FROM t1
WHERE (column1,column2,column3) IN
(SELECT column1,column2,column3 FROM t2);

SELECT column1 FROM t1 WHERE EXISTS (SELECT * FROM t2);

SELECT * FROM t1 WHERE column1 = ANY
(SELECT column1 FROM t2 WHERE t2.column2 = t1.column2);

SELECT column1 FROM t1 AS x
WHERE x.column1 = (SELECT column1 FROM t2 AS x
WHERE x.column1 = (SELECT column1 FROM t3
WHERE x.column2 = t3.column1));

val IN (SELECT key_val FROM tbl_name WHERE correlated_condition)

SELECT ... FROM (subquery) [AS] name ...

CREATE TABLE t1 (s1 INT, s2 CHAR(5), s3 FLOAT);

INSERT INTO t1 VALUES (1,'1',1.0);
INSERT INTO t1 VALUES (2,'2',2.0);
SELECT sb1,sb2,sb3
FROM (SELECT s1 AS sb1, s2 AS sb2, s3*2 AS sb3 FROM t1) AS sb
WHERE sb1 > 1;

SELECT AVG(SUM(column1)) FROM t1 GROUP BY column1;

SELECT AVG(sum_column1)
FROM (SELECT SUM(column1) AS sum_column1
FROM t1 GROUP BY column1) AS t1;

SELECT * FROM t1 WHERE id IN (SELECT id FROM t2);

SELECT DISTINCT t1.* FROM t1, t2 WHERE t1.id=t2.id;

SELECT * FROM t1 WHERE id NOT IN (SELECT id FROM t2);
SELECT * FROM t1 WHERE NOT EXISTS (SELECT id FROM t2 WHERE t1.id=t2.id);

SELECT table1.* FROM table1 LEFT JOIN table2 ON table1.id=table2.id
WHERE table2.id IS NULL;

TRUNCATE [TABLE] tbl_name

TRUNCATE TABLE leert eine Tabelle vollständig. Logisch gesehen ist dies äquivalent zu einer DELETE-Anweisung, die alle Datensätze löscht, aber es gibt unter bestimmten Umständen praktische Unterschiede.

Bei InnoDB-Tabellen wird TRUNCATE TABLE auf DELETE umgesetzt, sofern keine Fremdschlüssel-Constraints vorhanden sind, die die Tabelle referenzieren; andernfalls wird das schnelle Leeren (durch Löschen und Neuerstellen der Tabelle) verwendet. Der AUTO_INCREMENT-Zähler wird von TRUNCATE TABLE unabhängig vom Vorhandensein eines Fremdschlüssel-Constraints zurückgesetzt.

Bei anderen Speicher-Engines unterscheidet sich TRUNCATE TABLE in MySQL 5.1 wie folgt von DELETE:

TRUNCATE TABLE ist eine Oracle SQL-Erweiterung, die in MySQL übernommen wurde.

Syntax für eine Tabelle:

UPDATE [LOW_PRIORITY] [IGNORE] tbl_name
    SET col_name1=expr1 [, col_name2=expr2 ...]
    [WHERE where_condition]
    [ORDER BY ...]
    [LIMIT row_count]

Syntax für mehrere Tabellen:

UPDATE [LOW_PRIORITY] [IGNORE] table_references
    SET col_name1=expr1 [, col_name2=expr2 ...]
    [WHERE where_condition]

Bei der Ein-Tabellen-Syntax aktualisiert die UPDATE-Anweisung Spalten in vorhandenen Datensätzen in tbl_name mit neuen Werten. Die SET-Klausel gibt an, welche Spalten geändert werden und welche Werte diese erhalten sollen. Sofern angegeben, bestimmt die WHERE-Klausel die Bedingungen dafür, welche Datensätze geändert werden. Ohne WHERE-Klausel werden alle Datensätze aktualisiert. Wenn die ORDER BY-Klausel vorhanden ist, werden die Datensätze in der angegebenen Reihenfolge aktualisiert. Die LIMIT-Klausel kann die Anzahl der zu aktualisierenden Datensätze beschränken.

Bei der Mehrtabellensyntax aktualisiert UPDATE in jeder Tabelle table_references die Datensätze, die die Bedingungen erfüllen. In diesem Fall können ORDER BY und LIMIT nicht verwendet werden.

where_condition ist ein Ausdruck, der für jeden zu aktualisierenden Datensatz wahr ist. Er wird wie in Abschnitt 13.2.7, „SELECT“, beschrieben angegeben.

Die UPDATE-Anweisung unterstützt die folgenden Modifizierer:

Wenn Sie in einem Ausdruck auf eine Spalte in der Tabelle tbl_name zugreifen, verwendet UPDATE den aktuellen Wert der Spalte. So erhöht die folgende Anweisung beispielsweise den aktuellen Wert in der Spalte age um 1:

UPDATE persondata SET age=age+1;

UPDATE-Zuordnungen werden von links nach rechts ausgewertet. Die folgende Anweisung etwa verdoppelt die Spalte age und erhöht sie dann um 1:

UPDATE persondata SET age=age*2, age=age+1;

Wenn Sie eine Spalte auf den Wert aktualisieren, den sie ohnehin gerade hat, dann bemerkt MySQL dies und führt keine Aktualisierung durch.

Wenn Sie eine als NOT NULL deklarierte Spalte aktualisieren, indem Sie sie auf NULL setzen, wird die Spalte stattdessen auf den für den jeweiligen Datentyp vorgesehenen Standardwert gesetzt und der Warnungszähler erhöht. Der Vorgabewert ist 0 bei numerischen Typen, der Leer-String ('') bei String-Typen und der „Nullwert“ für Datums- und Uhrzeittypen.

UPDATE gibt die Anzahl der tatsächlich geänderten Datensätze zurück. Die C-API-Funktion mysql_info() hingegen gibt die Anzahl der Datensätze, die gefunden und aktualisiert wurden, sowie die Anzahl der Warnungen zurück, die während der UPDATE-Operation aufgetreten sind.

Sie können LIMIT row_count verwenden, um den Geltungsbereich von UPDATE einzuschränken. Eine LIMIT-Klausel ist eine Beschränkung für Datensätze. Die Anweisung endet, sobald eine Anzahl von row_count Datensätzen gefunden wurde, die der WHERE-Klausel entsprechen – unabhängig davon, ob sie dann auch geändert wurden oder nicht.

Wenn eine UPDATE-Anweisung eine ORDER BY-Klausel enthält, werden die Datensätze in der in der Klausel angegebenen Reihenfolge aktualisiert.

Sie können auch UPDATE-Operationen durchführen, die sich auf mehrere Tabellen beziehen. Allerdings können Sie ORDER BY oder LIMIT nicht in einer UPDATE-Anweisung für mehrere Tabellen verwenden. Die Klausel table_references listet die Tabellen auf, die im Join berücksichtigt werden. Die Syntax ist in Abschnitt 13.2.7.1, „JOIN“, beschrieben. Hier ein Beispiel:

UPDATE items,month SET items.price=month.price
WHERE items.id=month.id;

Das obige Beispiel zeigt einen inneren Join, der den Kommaoperator verwendet. UPDATE-Anweisungen für mehrere Tabellen können jedoch einen beliebigen Join-Typ verwenden, der für SELECT-Anweisungen zulässig ist, also etwa LEFT JOIN.

Sie benötigen die Berechtigung UPDATE nur für diejenigen in einer UPDATE-Anweisung für mehrere Tabellen referenzierten Spalten, die tatsächlich geändert werden. Die Berechtigung SELECT hingegen benötigen Sie für alle Spalten, die gelesen, aber nicht modifiziert werden.

Wenn Sie eine UPDATE-Anweisung für mehrere Tabellen verwenden, die auch InnoDB-Tabellen einbezieht, für die Fremdschlüssel-Constraints vorhanden sind, dann verarbeitet der MySQL-Optimierer die Tabellen unter Umständen in einer Reihenfolge, die sich von der ihrer Parent/Child-Beziehung unterscheidet. In diesem Fall schlägt die Anweisung fehl, und es wird ein Rollback durchgeführt. Stattdessen sollten Sie die Änderung dann in nur einer Tabelle durchführen und sich auf die ON UPDATE-Funktionalität verlassen, die InnoDB bietet, um andere Tabellen entsprechend zu ändern. Siehe auch Abschnitt 14.2.6.4, „Fremdschlüssel-Beschränkungen“.

Zurzeit können Sie in einer Unterabfrage keine Aktualisierungsoperation in einer Tabelle durchführen und gleichzeitig eine Auswahl in einer anderen Tabelle treffen.

{DESCRIBE | DESC} tbl_name [col_name | wild]

DESCRIBE vermittelt Informationen zu Spalten in einer Tabelle. Es handelt sich um ein Synonym für SHOW COLUMNS FROM. Diese Anweisungen erlauben auch die Anzeige von Informationen zu Views. (Siehe auch Abschnitt 13.5.4.3, „SHOW COLUMNS“.)

col_name kann ein Spaltenname oder aber ein String sein, der die SQL-Jokerzeichen ‘%’ und ‘_’ enthält, um eine Ausgabe nur zu Spalten zu erhalten, die dem String entsprechen. Sie müssen den String nicht in Anführungszeichen setzen, sofern er nicht Leerzeichen oder andere Sonderzeichen enthält.

mysql> DESCRIBE city;
+------------+----------+------+-----+---------+----------------+
| Field      | Type     | Null | Key | Default | Extra          |
+------------+----------+------+-----+---------+----------------+
| Id         | int(11)  | NO   | PRI | NULL    | auto_increment |
| Name       | char(35) | NO   |     |         |                |
| Country    | char(3)  | NO   | UNI |         |                |
| District   | char(20) | YES  | MUL |         |                |
| Population | int(11)  | NO   |     | 0       |                |
+------------+----------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

Field gibt den Spaltennamen an.

Das Feld Null zeigt an, ob NULL-Werte in der Spalte gespeichert werden können.

Das Feld Key gibt an, ob die Spalte indiziert ist. Mit PRI wird angegeben, dass die Spalte Teil des Primärschlüssels der Tabelle ist, mit UNI hingegen, dass sie Teil eines eindeutigen Indexes ist. Der Wert MUL schließlich signalisiert, dass mehrere Instanzen eines gegebenen Werts innerhalb der Spalte zulässig sind.

Ein möglicher Grund für die Anzeige von MUL in einem eindeutigen Index besteht darin, dass mehrere Spalten einen zusammengesetzten eindeutigen Index bilden. Dann ist die Kombination der Spalten eindeutig, während die einzelnen Spalten durchaus mehrere Instanzen eines gegebenen Werts enthalten können. Beachten Sie, dass in einem zusammengesetzten Index nur die links stehende Spalte einen Eintrag im Feld Key hat.

Das Feld Default gibt den Standardwert der Spalte an.

Das Feld Extra enthält zusätzliche Angaben, die zu einer gegebenen Spalte verfügbar sind. Im gezeigten Beispiel gibt Extra an, dass die Spalte Id mit dem Schlüsselwort AUTO_INCREMENT erstellt wurde.

Die Anweisung DESCRIBE ist aus Gründen der Kompatibilität mit Oracle vorhanden.

Auch die Anweisungen SHOW CREATE TABLE und SHOW TABLE STATUS bieten Informationen zu Tabellen. Siehe auch Abschnitt 13.5.4, „SHOW“.

USE db_name

Die Anweisung USE db_name weist MySQL an, die Datenbank db_name als Standarddatenbank (d. h. als aktuelle Datenbank) zu benutzen, auf die sich nachfolgende Anweisungen beziehen. Die Datenbank wird bis zum Ende der Sitzung oder aber so lange als Vorgabe verwendet, bis eine andere USE-Anweisung abgesetzt wird:

USE db1;
SELECT COUNT(*) FROM mytable;   # selects from db1.mytable
USE db2;
SELECT COUNT(*) FROM mytable;   # selects from db2.mytable

Wenn Sie eine bestimmte Datenbank mit USE zur Standarddatenbank machen, können Sie trotzdem auf Tabellen in anderen Datenbanken zugreifen. Im folgenden Beispiel wird auf die Tabelle author in der Datenbank db1 und die Tabelle editor in der Datenbank db2 zugegriffen:

USE db1;
SELECT author_name,editor_name FROM author,db2.editor
  WHERE author.editor_id = db2.editor.editor_id;

Die Anweisung USE ist aus Gründen der Kompatibilität mit Sybase vorhanden.

START TRANSACTION | BEGIN [WORK]
COMMIT [WORK] [AND [NO] CHAIN] [[NO] RELEASE]
ROLLBACK [WORK] [AND [NO] CHAIN] [[NO] RELEASE]
SET AUTOCOMMIT = {0 | 1}

Die Anweisungen START TRANSACTION und BEGIN starten eine neue Transaktion. COMMIT übergibt die aktuelle Transaktion, macht also die entsprechenden Änderungen permanent. Mit ROLLBACK machen Sie die laufende Transaktion rückgängig, d. h., alle Änderungen werden zurückgenommen. Die Anweisung SET AUTOCOMMIT schließlich aktiviert oder deaktiviert den standardmäßigen Autocommit-Modus für die aktuelle Verbindung.

Das optionale Schlüsselwort WORK wird für COMMIT und RELEASE unterstützt, ebenso die CHAIN- und RELEASE-Klauseln. CHAIN und RELEASE können verwendet werden, um den Transaktionsabschluss besser steuern zu können. Der Wert der Systemvariablen completion_type bestimmt das Standardverhalten beim Transaktionsabschluss. Siehe auch Abschnitt 5.2.2, „Server-Systemvariablen“.

Die AND CHAIN-Klausel bewirkt, dass eine neue Transaktion beginnt, sobald die aktuelle endet. Die neue Transaktion hat dabei dieselbe Isolierungsstufe wie die zuvor beendete Transaktion. Die RELEASE-Klausel bewirkt, dass der Server die aktuelle Clientverbindung nach Abschluss der aktuellen Transaktion beendet. Das Schlüsselwort NO unterdrückt den Abschluss von CHAIN oder RELEASE. Dies kann nützlich sein, wenn aufgrund der Einstellung der Systemvariablen completion_type der Abschluss von CHAIN oder RELEASE standardmäßig erzwungen wird.

Standardmäßig läuft MySQL im Autocommit-Modus. Das bedeutet, dass, sobald eine Anweisung ausgeführt wird, die eine Tabelle aktualisiert (also ändert), MySQL diese Änderung auf Festplatte speichert.

Verwenden Sie eine transaktionssichere Speicher-Engine (wie InnoDB, BDB oder NDB Cluster), dann können Sie den Autocommit-Modus mit der folgenden Anweisung deaktivieren:

SET AUTOCOMMIT=0;

Nach der Deaktivierung des Autocommit-Modus durch Setzen der AUTOCOMMIT-Variablen auf Null müssen Sie Ihre Änderungen mit COMMIT auf Festplatte speichern oder mit ROLLBACK rückgängig machen, wenn Sie die seit Beginn der Transaktion vorgenommenen Änderungen nicht übernehmen wollen.

Um den Autocommit-Modus für eine einzelne Abfolge von Anweisungen zu deaktivieren, verwenden Sie die Anweisung START TRANSACTION:

START TRANSACTION;
SELECT @A:=SUM(salary) FROM table1 WHERE type=1;
UPDATE table2 SET summary=@A WHERE type=1;
COMMIT;

Bei START TRANSACTION bleibt Autocommit deaktiviert, bis Sie die Transaktion mit COMMIT oder ROLLBACK beenden. Der Autocommit-Modus kehrt dann in seinen vorherigen Status zurück.

BEGIN und BEGIN WORK werden als Aliase für START TRANSACTION benutzt, um eine Transaktion zu starten. START TRANSACTION ist die SQL-Standardsyntax und wird als Möglichkeit zur Durchführung spontaner Transaktionen empfohlen.

Die BEGIN-Anweisung unterscheidet sich von der Verwendung des Schlüsselworts BEGIN, welches eine mehrteilige BEGIN ... END-Anweisung einleitet. Mit Letzterem wird keine Transaktion begonnen. Siehe auch Abschnitt 19.2.5, „BEGIN ... END-Syntax für komplexe Anweisungen“.

Sie können eine Transaktion auch wie folgt einleiten:

START TRANSACTION WITH CONSISTENT SNAPSHOT;

Die Klausel WITH CONSISTENT SNAPSHOT startet eine konsistente Leseoperation für Speicher-Engines, die diese unterstützen. Derzeit betrifft dies nur InnoDB. Der Effekt ist derselbe wie beim Absetzen einer START TRANSACTION-Anweisung gefolgt von einer SELECT-Anweisung für eine beliebige InnoDB-Tabelle. Siehe auch Abschnitt 14.2.10.4, „Konsistentes Lesen“.

Die Klausel WITH CONSISTENT SNAPSHOT ändert die aktuelle Isolationsstufe der Transaktion nicht, d. h., eine konsistente Momentaufnahme wird nur dann erstellt, wenn die aktuelle Isolationsstufe konsistente Leseoperationen gestattet (REPEATABLE READ oder SERIALIZABLE).

Bei Beginn einer Transaktion wird implizit UNLOCK TABLES ausgeführt.

Beste Ergebnisse erhalten Sie, wenn Sie Transaktionen nur bei Tabellen durchführen, die mit einer einzelnen transaktionssicheren Speicher-Engine verwaltet werden. Andernfalls können die folgenden Probleme auftreten:

Jede Transaktion wird nach Absetzen von COMMIT am Stück in das Binärlog geschrieben. Transaktionen, die per Rollback rückgängig gemacht wurden, werden nicht geloggt. (Ausnahme: Für Modifikationen an nicht transaktionssicheren Tabellen kann kein Rollback durchgeführt werden. Wenn eine Transaktion, für die ein Rollback erfolgt, Änderungen an nicht transaktionssicheren Tabellen enthält, dann wird die gesamte Transaktion am Ende mit einer ROLLBACK-Anweisung geloggt, um sicherzustellen, dass die Modifikationen an diesen Tabellen repliziert werden.) Siehe auch Abschnitt 5.12.3, „Die binäre Update-Logdatei“.

Sie können die Isolationsstufe für Transaktionen mit SET TRANSACTION ISOLATION LEVEL ändern. Siehe auch Abschnitt 13.4.6, „SET TRANSACTION“.

Ein Rollback kann ein relativ langsamer Vorgang sein, der zudem stattfinden kann, ohne dass der Benutzer dies explizit angefordert hat (z. B. wenn ein Fehler aufgetreten ist). Aufgrund dieser Tatsache zeigt SHOW PROCESSLIST in der Spalte State für die Verbindung Rolling back an, solange implizite oder (durch die SQL-Anweisung ROLLBACK) explizite Rollbacks durchgeführt werden.

Es gibt Anweisungen, für die kein Rollback möglich ist. Hierzu gehören DDL-Anweisungen (Data Definition Language), z. B. solche, mit denen Datenbanken erstellt oder gelöscht oder Tabellen oder gespeicherte Routinen erstellt, gelöscht oder geändert werden.

Sie sollten Ihre Transaktionen so entwickeln, dass solche Anweisungen nicht vorhanden sind. Wenn Sie eine Anweisung früh in einer Transaktion absetzen, für die kein Rollback möglich ist, und dann später eine andere Anweisung fehlschlägt, dann können durch Absetzen der ROLLBACK-Anweisung nicht alle Auswirkungen der Transaktion rückgängig gemacht werden.

Alle folgenden Anweisungen (und ggf. auch ihre Synonyme) beenden eine Transaktion implizit – so als ob Sie vor Ausführung der Anweisung eine COMMIT-Anweisung abgesetzt hätten:

Transaktionen können nicht verschachtelt werden. Dies ist eine Folge des impliziten COMMIT, das für jede laufende Transaktion ausgeführt wird, wenn Sie eine START TRANSACTION-Anweisung (oder ein entsprechendes Synonym) absetzen.

SAVEPOINT identifier
ROLLBACK [WORK] TO SAVEPOINT identifier
RELEASE SAVEPOINT identifier

InnoDB unterstützt die SQL-Anweisungen SAVEPOINT, ROLLBACK TO SAVEPOINT, RELEASE SAVEPOINT und das optionale Schlüsselwort WORK für ROLLBACK.

Die SAVEPOINT-Anweisung setzt einen benannten Transaktionsspeicherpunkt mit dem Namen identifier. Hat die laufende Transaktion einen Speicherpunkt gleichen Namens, dann wird der alte Speicherpunkt gelöscht und ein neuer gesetzt.

Die Anweisung ROLLBACK TO SAVEPOINT macht eine Transaktion bis zum benannten Speicherpunkt rückgängig. Änderungen, die die aktuelle Transaktion an Datensätzen nach dem Speicherpunkt vorgenommen hat, werden im Rollback rückgängig gemacht; allerdings hebt InnoDB Datensatzsperren, die nach dem Speicherpunkt im Speicher abgelegt wurden, nicht auf. (Beachten Sie, dass bei einem neu eingefügten Datensatz die Sperrinformation in dem im Datensatz gespeicherten Transaktionsbezeichner enthalten ist, d. h., die Sperre wird nicht separat im Speicher abgelegt. In diesem Fall wird die Datensatzsperre beim Rückgängigmachen aufgehoben.) Speicherpunkte, die zeitlich nach dem genannten Speicherpunkt liegen, werden gelöscht.

Wenn die ROLLBACK TO SAVEPOINT-Anweisung den folgenden Fehler zurückgibt, heißt das, dass es keinen Speicherpunkt des angegebenen Namens gibt:

ERROR 1181: Got error 153 during ROLLBACK

Die Anweisung RELEASE SAVEPOINT entfernt den genannten Speicherpunkt aus der Menge der Speicherpunkte für die laufende Transaktion. Es findet weder eine Übergabe noch ein Rollback statt. Existiert der Speicherpunkt nicht, dann tritt ein Fehler auf.

Alle Speicherpunkte der aktuellen Transaktion werden gelöscht, wenn Sie eine COMMIT- oder ROLLBACK-Anweisung absetzen, die keinen Speicherpunkt benennt.

Seit MySQL 5.0.17 wird eine neue Speicherpunktstufe erstellt, wenn eine gespeicherte Funktion aufgerufen oder ein Trigger aktiviert wird. Die Speicherpunkte der vorherigen Stufen sind dann nicht mehr verfügbar, damit Konflikte mit Speicherpunkten der neuen Stufe vermieden werden. Wenn die Funktion oder der Trigger beendet wird, werden alle hierdurch erstellten Speicherpunkte gelöscht und die vorherige Speicherpunktstufe wird wiederhergestellt.

LOCK TABLES
    tbl_name [AS alias] {READ [LOCAL] | [LOW_PRIORITY] WRITE}
    [, tbl_name [AS alias] {READ [LOCAL] | [LOW_PRIORITY] WRITE}] ...
UNLOCK TABLES

LOCK TABLES sperrt Tabellen für den aktuellen Thread. Wenn eine Tabelle von anderen Threads gesperrt ist, wird der laufende Vorgang angehalten, bis alle Sperren erwirkt werden können. UNLOCK TABLES hebt Sperren auf, die vom aktuellen Thread gehalten werden. Die Sperren aller Tabellen, die vom aktuellen Thread gesperrt werden, werden implizit aufgehoben, wenn der Thread eine andere LOCK TABLES-Anweisung absetzt oder die Verbindung zum Server geschlossen wird.

Eine Tabellensperre schützt nur gegen unangemessene Lese- und Schreiboperationen durch andere Clients. Der Client, der die Sperre (auch eine Lesesperre) hält, kann Operationen auf Tabellenebene – wie etwa DROP TABLE – durchführen.

Beachten Sie die folgenden Angaben zur Verwendung von LOCK TABLES bei transaktionssicheren Tabellen:

Um LOCK TABLES zu verwenden, benötigen Sie die Berechtigungen LOCK TABLES und SELECT für die betreffenden Tabellen.

Die wichtigsten Gründe zur Verwendung von LOCK TABLES sind die Emulation von Transaktionen oder die Beschleunigung der Aktualisierung von Tabellen. Wir werden dies weiter unten ausführlicher erläutern.

Wenn ein Thread eine Lesesperre für eine Tabelle erwirkt, kann dieser Thread (wie auch alle übrigen Threads) aus der Tabelle nur lesen. Wenn ein Thread eine Schreibsperre für eine Tabelle erwirkt, dann kann nur der Thread, der die Sperre hält, in die Tabelle schreiben. Anderen Threads wird dies untersagt, bis die Sperre aufgehoben ist.

Der Unterschied zwischen READ LOCAL und READ besteht darin, dass READ LOCAL bei aktiver Sperre die Ausführung von INSERT-Anweisungen (also nebenläufigen Einfügeoperationen) zulässt, sofern sie keine Konflikte auslösen. Allerdings kann diese Funktion nicht verwendet werden, um die Datenbankdateien bei aktiver Sperre außerhalb von MySQL zu manipulieren. Bei InnoDB-Tabellen entspricht READ LOCAL READ.

Wenn Sie LOCK TABLES einsetzen, müssen Sie alle Tabellen sperren, die Sie in Ihren Abfragen zu benutzen beabsichtigen. Solange die mit einer LOCK TABLES-Anweisung erwirkten Sperren gültig sind, können Sie nicht auf Tabellen zugreifen, die nicht durch die Anweisung gesperrt wurden. Ferner können Sie eine gesperrte Tabelle auch nicht mehrfach in derselben Abfrage verwenden. Benutzen Sie stattdessen Aliase. In diesem Fall müssen Sie allerdings für jeden Alias separat eine Sperre erwirken.

mysql> LOCK TABLE t WRITE, t AS t1 WRITE;
mysql> INSERT INTO t SELECT * FROM t;
ERROR 1100: Table 't' was not locked with LOCK TABLES
mysql> INSERT INTO t SELECT * FROM t AS t1;

Wenn Ihre Abfragen eine Tabelle unter Verwendung eines Alias referenzieren, müssen Sie die Tabelle sperren, die denselben Alias benutzt. Die Tabelle zu sperren, ohne den Alias anzugeben, funktioniert nicht:

mysql> LOCK TABLE t READ;
mysql> SELECT * FROM t AS myalias;
ERROR 1100: Table 'myalias' was not locked with LOCK TABLES

Umgekehrt müssen Sie, wenn Sie eine Tabelle unter Verwendung eines Alias sperren, diese Tabelle unter Verwendung dieses Alias in Ihren Abfragen referenzieren:

mysql> LOCK TABLE t AS myalias READ;
mysql> SELECT * FROM t;
ERROR 1100: Table 't' was not locked with LOCK TABLES
mysql> SELECT * FROM t AS myalias;

WRITE-Sperren haben in der Regel Vorrang vor READ-Sperren. Hierdurch soll sichergestellt werden, dass Aktualisierungen so schnell wie möglich verarbeitet werden. Das bedeutet, dass, wenn ein Thread eine READ-Sperre erwirkt und dann ein anderer Thread eine WRITE-Sperre anfordert, nachfolgende READ-Sperranforderungen warten müssen, bis der WRITE-Thread die Sperre wieder aufgehoben hat. Sie können mit LOW_PRIORITY WRITE-Sperren anderen Threads das Erwirken von READ-Sperren gestatten, während der Thread auf die WRITE-Sperre wartet. LOW_PRIORITY WRITE-Sperren sollten Sie nur dann verwenden, wenn Sie sicher sind, dass zu einem nachfolgenden Zeitpunkt keine Threads mehr eine READ-Sperre halten werden.

LOCK TABLES funktioniert wie folgt:

Diese Vorgehensweise gewährleistet, dass ein Aussperren ausgeschlossen ist. Es gibt jedoch ein paar andere Aspekte, die Sie bezüglich der Vorgehensweise beachten sollten:

Wenn Sie eine LOW_PRIORITY WRITE-Sperre für eine Tabelle verwenden, bedeutet dies lediglich, dass MySQL auf diese bestimmte Sperre wartet, bis keine Threads mehr vorhanden sind, die eine READ-Sperre benötigen. Wenn der Thread die WRITE-Sperre erwirkt hat und darauf wartet, die Sperre für die nächste Tabelle in der Sperrliste zu erwirken, warten alle anderen Threads darauf, dass die WRITE-Sperre aufgehoben wird. Wenn dies in Bezug auf Ihre Anwendung ein schwerwiegendes Problem darstellen sollte, dann sollten Sie in Betracht ziehen, einige Ihrer Tabellen in transaktionssichere Tabellen umzuwandeln.

Sie können einen Thread, der auf eine Tabellensperre wartet, problemlos mit KILL terminieren. Siehe auch Abschnitt 13.5.5.3, „KILL“.

Beachten Sie, dass Sie keine Tabellen sperren sollen, die Sie mit INSERT DELAYED verwenden, weil die Einfügeoperation in diesem Fall durch einen separaten Thread durchgeführt wird.

Normalerweise müssen Sie Tabellen nicht sperren, weil alle UPDATE-Anweisungen für sich atomisch sind: Kein anderer Thread kann eine andere derzeit ausgeführte SQL-Anweisung unterbrechen. Allerdings gibt es einige wenige Fälle, in denen das Sperren von Tabellen von Vorteil sein kann:

Sie können die Verwendung von LOCK TABLES in vielen Fällen umgehen, indem Sie relative Updates (UPDATE customer SET value=value+new_value) oder die Funktion LAST_INSERT_ID() verwenden. Siehe auch Abschnitt 1.9.5.3, „Transaktionen“.

Sie können das Sperren von Tabellen in manchen Fällen auch umgehen, indem Sie Funktionen für beratende Sperrungen auf Benutzerebene (GET_LOCK() und RELEASE_LOCK()) verwenden. Diese Sperren werden in einer Hash-Tabelle auf dem Server gespeichert und mit pthread_mutex_lock() und pthread_mutex_unlock() mit dem Ziel einer hohen Geschwindigkeit implementiert. Siehe auch Abschnitt 12.10.4, „Verschiedene Funktionen“.

Weitere Informationen zu Methoden beim Sperren finden Sie in Abschnitt 7.3.1, „Wie MySQL Tabellen sperrt“.

Sie können mit der Anweisung FLUSH TABLES WITH READ LOCK Lesesperren für alle Tabellen in allen Datenbanken setzen. Siehe auch Abschnitt 13.5.5.2, „FLUSH“. Dies ist eine recht praktische Möglichkeit der Datensicherung, wenn Sie ein Dateisystem wie Veritas einsetzen, das rechtzeitig Schnappschüsse erstellt.

Hinweis: Wenn Sie ALTER TABLE für eine gesperrte Tabelle ausführen, kann die Sperre unter Umständen aufgehoben werden. Siehe auch Abschnitt A.7.1, „Probleme mit ALTER TABLE“.

SET [GLOBAL | SESSION] TRANSACTION ISOLATION LEVEL
{ READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE }

Diese Anweisung bestimmt die Transaktionsisolationsstufe für die nächste Transaktion, global oder für die aktuelle Sitzung.

Das Standardverhalten von SET TRANSACTION besteht darin, die Isolationsstufe für die nächste (d. h. noch nicht gestartete) Transaktion einzustellen. Wenn Sie das Schlüsselwort GLOBAL verwenden, setzt die Anweisung die Standardtransaktionsstufe global für alle neuen Verbindungen, die nachfolgend hergestellt werden. Vorhandene Verbindungen werden hiervon nicht berührt. Für diesen Zweck benötigen Sie die Berechtigung SUPER. Bei Verwendung des Schlüsselworts SESSION wird die standardmäßige Transaktionsstufe für alle zukünftigen Transaktionen gesetzt, die über die aktuelle Verbindung durchgeführt werden.

Beschreibungen aller InnoDB-Transaktionsisolationsstufen finden Sie in Abschnitt 14.2.10.3, „InnoDB und TRANSACTION ISOLATION LEVEL“. InnoDB unterstützt in MySQL 5.1 alle Stufen. Die Standardstufe ist REPEATABLE READ.

Um die globale Standardisolationsstufe für mysqld festzulegen, verwenden Sie die Option --transaction-isolation. Siehe auch Abschnitt 5.2.1, „Befehlsoptionen für mysqld“.

Die Unterstützung von XA-Transaktionen ist für die InnoDB-Speicher-Engine verfügbar. Die XA-Implementierung bei MySQL basiert auf dem X/Open CAE-Dokument Distributed Transaction Processing: The XA Specification („Verteilte Transaktionsverarbeitung: Die XA-Spezifikation“). Dieses Dokument wurde von The Open Group veröffentlicht und ist unter http://www.opengroup.org/public/pubs/catalog/c193.htm erhältlich. Die Grenzen der aktuellen XA-Implementierung sind in Abschnitt I.5, „Beschränkungen bei XA-Transaktionen“ beschrieben.

Clientseitig gibt es keine besonderen Anforderungen. Die XA-Schnittstelle zu einem MySQL Server besteht aus SQL-Anweisungen, die mit dem Schlüsselwort XA beginnen. MySQL-Clientprogramme müssen SQL-Anweisungen senden und die Semantik der XA-Anweisungsschnittstelle verstehen können. Sie müssen nicht mit einer aktuellen Clientbibliothek verknüpft sein: Es können auch ältere Clientbibliotheken verwendet werden.

Derzeit unterstützt MySQL Connector/J 5.0.0 XA direkt (mithilfe einer Klassenschnittstelle, die die XA-SQL-Anweisungsschnittstelle für Sie verwaltet).

XA unterstützt verteilte Transaktionen, d. h., mehrere separate transaktionssichere Ressourcen können an einer globalen Transaktion teilnehmen. Transaktionssichere Ressourcen sind häufig relationale Datenbanksysteme, es gibt aber auch andere Ressourcentypen.

Eine globale Transaktion umfasst mehrere Aktionen, die für sich transaktionssicher sind; sie alle aber müssen entweder als Gruppe erfolgreich abgeschlossen werden, oder sie werden im Rahmen eines Rollbacks gemeinsam rückgängig gemacht. Im Endeffekt erweitert dies die ACID-Eigenschaften um eine „Ebene nach oben“, sodass mehrere ACID-Transaktionen konzertiert als Komponenten einer globalen Operation ausgeführt werden, die ebenfalls ACID-Eigenschaften hat. (Allerdings müssen Sie bei einer verteilten Transaktion die Isolationsstufe SERIALIZABLE benutzen, um ACID-Eigenschaften zu erhalten. Bei nichtverteilten Transaktionen ist REPEATABLE READ ausreichend, nicht aber bei verteilten Transaktionen.)

Es folgen einige Beispiele für verteilte Transaktionen:

Anwendungen, die globale Transaktionen verwenden, umfassen einen oder mehrere Ressourcenmanager und einen Transaktionsmanager:

Die MySQL-Implementierung von XA erlaubt es einem MySQL Server, als RM zu agieren, der XA-Transaktionen innerhalb einer globalen Transaktion verwaltet. Ein Clientprogramm, das eine Verbindung mit dem MySQL Server herstellt, agiert als TM.

Um eine globale Transaktion auszuführen, ist es erforderlich zu wissen, welche Komponenten betroffen sind, und jede dieser Komponenten an einen Punkt zu bringen, an dem sie übergeben oder rückgängig gemacht werden kann. Abhängig davon, was jede Komponente bezüglich ihres Erfolgs oder Fehlschlags meldet, müssen sie alle als atomische Gruppe übergeben oder rückgängig gemacht werden: Es müssen also entweder alle Komponenten geschrieben werden oder alle Komponenten werden im Zuge eines Rollbacks ungeschehen gemacht. Um eine globale Transaktion verwalten zu können, muss berücksichtigt werden, dass jede Komponente fehlschlagen oder auch das sie verbindende Netzwerk ausfallen kann.

Der Prozess zur Ausführung einer globalen Transaktion basiert auf 2PC (2-Phase Commit, Übergabe in zwei Phasen). Dies findet statt, nachdem die von den Verzweigungen der globalen Transaktion durchgeführten Vorgänge erledigt sind.

In manchen Fällen verwendet eine globale Transaktion unter Umständen nur eine einphasige Übergabe (1PC). Stellt beispielsweise ein TM fest, dass eine globale Transaktion nur eine einzige transaktionssichere Ressource (also nur eine Verzweigung) umfasst, dann kann diese Ressource zur selben Zeit zur Vorbereitung und Übergabe angewiesen werden.

XA {START|BEGIN} xid [JOIN|RESUME]

XA END xid [SUSPEND [FOR MIGRATE]]

XA PREPARE xid

XA COMMIT xid [ONE PHASE]

XA ROLLBACK xid

XA RECOVER

xid: gtrid [, bqual [, formatID ]]

mysql> XA RECOVER;
+----------+--------------+--------------+--------+
| formatID | gtrid_length | bqual_length | data   |
+----------+--------------+--------------+--------+
|        7 |            3 |            3 | abcdef |
+----------+--------------+--------------+--------+

mysql> XA START 'xatest';
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO mytable (i) VALUES(10);
Query OK, 1 row affected (0.04 sec)

mysql> XA END 'xatest';
Query OK, 0 rows affected (0.00 sec)

mysql> XA PREPARE 'xatest';
Query OK, 0 rows affected (0.00 sec)

mysql> XA COMMIT 'xatest';
Query OK, 0 rows affected (0.00 sec)

MySQL-Kontendaten sind in Tabellen der Datenbank mysql gespeichert. Diese Datenbank und das Zugriffssteuerungssystem sind in Kapitel 5, Datenbankverwaltung, ausführlich beschrieben. Sie finden dort weitere Informationen.

Wichtig: Einige Releases von MySQL enthalten Änderungen an der Struktur der Grant-Tabellen, damit neue Berechtigungen oder Funktionen hinzugefügt werden können. Wenn Sie ein Upgrade auf eine neue MySQL-Version durchführen, sollten Sie Ihre Grant-Tabellen aktualisieren, damit sichergestellt ist, dass diese auf der aktuellen Struktur basieren und auf diese Weise neue Funktionalitäten nutzen können. Siehe auch Abschnitt 5.6, „mysql_fix_privilege_tables — Upgrade von MySQL-Systemtabellen“.

CREATE USER user [IDENTIFIED BY [PASSWORD] 'password']
    [, user [IDENTIFIED BY [PASSWORD] 'password']] ...

DROP USER user [, user] ...

DROP USER user;

GRANT priv_type [(column_list)] [, priv_type [(column_list)]] ...
    ON [object_type] {tbl_name | * | *.* | db_name.*}
    TO user [IDENTIFIED BY [PASSWORD] 'password']
        [, user [IDENTIFIED BY [PASSWORD] 'password']] ...
    [REQUIRE
        NONE |
        [{SSL| X509}]
        [CIPHER 'cipher' [AND]]
        [ISSUER 'issuer' [AND]]
        [SUBJECT 'subject']]
    [WITH with_option [with_option] ...]

object_type =
    TABLE
  | FUNCTION
  | PROCEDURE

with_option =
    GRANT OPTION
  | MAX_QUERIES_PER_HOUR count
  | MAX_UPDATES_PER_HOUR count
  | MAX_CONNECTIONS_PER_HOUR count
  | MAX_USER_CONNECTIONS count

GRANT ALL ON test.* TO ''@'localhost' ...

SELECT Host, User FROM mysql.user WHERE User='';

DELETE FROM mysql.user WHERE Host='localhost' AND User='';
FLUSH PRIVILEGES;

global privileges
OR (database privileges AND host privileges)
OR table privileges
OR column privileges
OR routine privileges

GRANT ... IDENTIFIED BY 'mypass';

GRANT ...
IDENTIFIED BY PASSWORD '*6C8989366EAF75BB670AD8EA7A7FC1176A95CEF4';

GRANT ALL PRIVILEGES ON test.* TO 'root'@'localhost'
  IDENTIFIED BY 'goodsecret'
  REQUIRE SUBJECT '/C=EE/ST=Some-State/L=Tallinn/
    O=MySQL demo client certificate/
    CN=Tonu Samuel/Email=tonu@example.com'
  AND ISSUER '/C=FI/ST=Some-State/L=Helsinki/
    O=MySQL Finland AB/CN=Tonu Samuel/Email=tonu@example.com'
  AND CIPHER 'EDH-RSA-DES-CBC3-SHA';

RENAME USER old_user TO new_user
    [, old_user TO new_user] ...

REVOKE priv_type [(column_list)] [, priv_type [(column_list)]] ...
    ON [object_type] {tbl_name | * | *.* | db_name.*}
    FROM user [, user] ...

REVOKE ALL PRIVILEGES, GRANT OPTION FROM user [, user] ...

REVOKE ALL PRIVILEGES, GRANT OPTION FROM user [, user] ...

SET PASSWORD = PASSWORD('some password')
SET PASSWORD FOR user = PASSWORD('some password')

SET PASSWORD FOR 'bob'@'%.loc.gov' = PASSWORD('newpass');

UPDATE mysql.user SET Password=PASSWORD('newpass')
  WHERE User='bob' AND Host='%.loc.gov';
FLUSH PRIVILEGES;