MySQL lernen, 2. Auflage

Kapitel 4. Arbeiten mit Datenbankstrukturen

Diese Arbeit wurde mithilfe von KI übersetzt. Wir freuen uns über dein Feedback und deine Kommentare: [e-mail]

In diesem Kapitel erfährst du, wie du deine eigenen Datenbanken erstellst, Strukturen wie Tabellen und Indizes hinzufügst und entfernst und welche Spaltentypen du in deinen Tabellen verwenden kannst. Es konzentriert sich auf die Syntax und die Funktionen von SQL und nicht auf die Semantik des Entwerfens, Spezifizierens und Verfeinerns eines Datenbankentwurfs; eine einführende Beschreibung der Datenbankentwurfstechniken findest du in Kapitel 2. Um dieses Kapitel durchzuarbeiten, musst du wissen, wie du mit einer bestehenden Datenbank und ihren Tabellen arbeitest, wie in Kapitel 3 beschrieben.

In diesem Kapitel werden die Strukturen der Beispieldatenbank sakila aufgelistet. Wenn du die Anweisungen zum Laden der Datenbank in "Beispiele für die Modellierung von Entity-Relationships" befolgt hast , hast du die Datenbank bereits zur Verfügung und weißt, wie du sie wiederherstellen kannst, nachdem du ihre Strukturen geändert hast.

Wenn du dieses Kapitel abgeschlossen hast, verfügst du über alle Grundlagen, um Datenbankstrukturen zu erstellen, zu ändern und zu löschen. Zusammen mit den Techniken, die du in Kapitel 3 gelernt hast, bist du in der Lage, eine Vielzahl von grundlegenden Operationen auszuführen. In den Kapiteln 5 und 7 geht es um die Fähigkeiten, mit denen du fortgeschrittenere Operationen mit MySQL durchführen kannst.

Datenbanken erstellen und verwenden

Wenn du den Entwurf einer Datenbank abgeschlossen hast, ist der erste praktische Schritt, den du mit MySQL machen musst, sie zu erstellen. Das tust du mit der Anweisung CREATE DATABASE. Angenommen, du möchtest eine Datenbank mit dem Namen lucy erstellen. Hier ist die Anweisung, die du eingeben würdest:

mysql> CREATE DATABASE lucy;

Query OK, 1 row affected (0.10 sec)

Wir gehen hier davon aus, dass du weißt, wie du dich mit dem MySQL-Client verbinden kannst, wie in Kapitel 1 beschrieben. Außerdem gehen wir davon aus, dass du dich als Root-Benutzer oder als ein anderer Benutzer, der Strukturen erstellen, löschen und ändern kann, einloggen kannst (eine ausführliche Diskussion über Benutzerrechte findest du in Kapitel 8). Wenn du die Datenbank erstellst, sagt MySQL, dass eine Zeile betroffen ist. Dabei handelt es sich nicht um eine normale Zeile in einer bestimmten Datenbank, sondern um einen neuen Eintrag, der der Liste hinzugefügt wurde, die du unter mit dem Befehl SHOW DATABASES einsehen kannst.

Wenn du die Datenbank erstellt hast, musst du sie im nächsten Schritt mit als Datenbank auswählen, mit der du arbeitest. Das machst du mit dem Befehl MySQL USE:

mysql> USE lucy;

Database changed

Dieser Befehl muss in einer Zeile eingegeben werden und braucht nicht mit einem Semikolon abgeschlossen zu werden, obwohl wir das aus Gewohnheit normalerweise automatisch tun. Sobald du die Datenbank verwendet (ausgewählt) hast, kannst du mit den im nächsten Abschnitt beschriebenen Schritten Tabellen, Indizes und andere Strukturen erstellen.

Bevor wir mit dem Erstellen anderer Strukturen fortfahren, wollen wir einige Funktionen und Einschränkungen beim Erstellen von Datenbanken besprechen. Sehen wir uns zunächst an, was passiert, wenn du versuchst, eine Datenbank zu erstellen, die bereits existiert:

mysql> CREATE DATABASE lucy;

ERROR 1007 (HY000): Can't create database 'lucy'; database exists

Du kannst diesen Fehler vermeiden, indem du die Schlüsselwortphrase IF NOT EXISTS in dieAnweisung einfügst:

mysql> CREATE DATABASE IF NOT EXISTS lucy;

Query OK, 0 rows affected (0.00 sec)

Du kannst sehen, dass MySQL sich nicht beschwert hat, aber auch nichts unternommen hat: Die Meldung 0 rows affected zeigt an, dass keine Daten geändert wurden. Dieser Zusatz ist nützlich, wenn du SQL-Anweisungen zu einem Skript hinzufügst: Er verhindert, dass das Skript bei einem Fehler abbricht.

Schauen wir uns an, wie man Datenbanknamen wählt und Groß- und Kleinschreibung verwendet. Datenbanknamen definieren physische Verzeichnisnamen (oder Ordnernamen) auf der Festplatte. Auf einigen Betriebssystemen wird bei Verzeichnisnamen zwischen Groß- und Kleinschreibung unterschieden, auf anderen spielt die Großschreibung keine Rolle. Bei Unix-ähnlichen Systemen wie Linux und macOS wird in der Regel zwischen Groß- und Kleinschreibung unterschieden, bei Windows hingegen nicht. Das hat zur Folge, dass für Datenbanknamen die gleichen Einschränkungen gelten: Wenn die Groß- und Kleinschreibung für das Betriebssystem wichtig ist, ist sie es auch für MySQL. Auf einem Linux-Rechner sind zum Beispiel LUCY, lucy und Lucy verschiedene Datenbanknamen; unter Windows beziehen sie sich auf eine einzige Datenbank. Wenn du unter Linux oder macOS eine falsche Großschreibung verwendest, wird sich MySQL beschweren:

mysql> SELECT SaKilA.AcTor_id FROM ACTor;

ERROR 1146 (42S02): Table 'sakila.ACTor' doesn't exist

Aber unter Windows funktioniert das normalerweise.

Tipp

Um dein SQL maschinenunabhängig zu machen, empfehlen wir , dass du für Datenbanken (und für Tabellen, Spalten, Aliase und Indizes) konsequent Kleinbuchstaben verwendest. Das ist jedoch keine Voraussetzung, und wie die Beispiele in diesem Buch gezeigt haben, kannst du jede Namenskonvention verwenden, mit der du dich wohl fühlst. Sei einfach konsequent und erinnere dich daran, wie sich MySQL auf verschiedenen Betriebssystemen verhält.

Dieses Verhalten wird durch den Parameter lower_case_table_names gesteuert. Wenn er auf 0 gesetzt ist, werden die Tabellennamen wie angegeben gespeichert, und bei Vergleichen wird die Groß-/Kleinschreibung beachtet. Wenn er auf 1 gesetzt ist, werden die Tabellennamen in Kleinbuchstaben auf der Festplatte gespeichert, und bei Vergleichen wird nicht auf Groß- und Kleinschreibung geachtet. Wenn dieser Parameter auf 2 gesetzt ist, werden die Tabellennamen wie angegeben gespeichert, aber in Kleinbuchstaben verglichen. Unter Windows ist der Standardwert 1. Unter macOS ist der Standardwert 2. Unter Linux wird ein Wert von 2 nicht unterstützt; der Server erzwingt stattdessen den Wert 0.

Es gibt weitere Einschränkungen für Datenbanknamen. Sie dürfen höchstens 64 Zeichen lang sein. Außerdem solltest du keine reservierten MySQL-Wörter wie SELECT, FROM und USE als Namen für Strukturen verwenden; diese können den MySQL-Parser verwirren und es ihm unmöglich machen, die Bedeutung deiner Anweisungen zu interpretieren. Du kannst diese Einschränkung umgehen, indem du das reservierte Wort in Backticks einschließt (`), aber es macht mehr Mühe, sich daran zu erinnern, als es wert ist. Außerdem darfst du bestimmte Zeichen in den Namen nicht verwenden - insbesondere den Schrägstrich, den umgekehrten Schrägstrich, das Semikolon und den Punkt - und ein Datenbankname darf nicht mit einem Leerzeichen enden. Auch hier verwirrt die Verwendung dieser Zeichen den MySQL-Parser und kann zu unvorhersehbarem Verhalten führen. Das passiert zum Beispiel, wenn du ein Semikolon in einen Datenbanknamen einfügst:

mysql> CREATE DATABASE IF NOT EXISTS lu;cy;

Query OK, 1 row affected (0.00 sec)

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual
that corresponds to your MySQL server version for the right syntax to use
near 'cy' at line 1

Da mehr als eine SQL-Anweisung in einer einzigen Zeile stehen kann, führt das dazu, dass eine Datenbank lu erstellt wird, und dann ein Fehler durch die sehr kurze, unerwartete SQL-Anweisung cy; erzeugt wird. Wenn du wirklich eine Datenbank mit einem Semikolon im Namen erstellen willst, kannst du das mit Backticks tun:

mysql> CREATE DATABASE IF NOT EXISTS `lu;cy`;

Query OK, 1 row affected (0.01 sec)

Du kannst sehen, dass du jetzt zwei neue Datenbanken hast:

mysql> SHOW DATABASES LIKE `lu%`;

+----------------+
| Database (lu%) |
+----------------+
| lu             |
| lu;cy          |
+----------------+
2 rows in set (0.01 sec)

Tabellen erstellen

Dieser Abschnitt behandelt Themen zu Tabellenstrukturen. Wir zeigen dir, wie man:

• Erstellen von Tabellen, anhand von einführenden Beispielen.

• Wähle Namen für Tabellen und tabellenbezogene Strukturen.

• Verstehe und wähle Spaltentypen aus.

• Verstehe und wähle Schlüssel und Indizes.

• Verwende die MySQL-eigene Funktion AUTO_INCREMENT.

Wenn du diesen Abschnitt beendest, hast du alle Grundlagen zum Erstellen von Datenbankstrukturen kennengelernt. Der Rest dieses Kapitels behandelt die Beispieldatenbank sakila und wie du bestehende Strukturen ändern und entfernen kannst.

Grundlagen

Für die Beispiele in diesem Abschnitt gehen wir davon aus, dass die Datenbank sakila noch nicht erstellt wurde. Wenn du den Beispielen folgen willst und die Datenbank bereits geladen hast, kannst du sie für diesen Abschnitt löschen und später wieder laden. Wenn du sie löschst, werden die Datenbank, ihre Tabellen und alle Daten gelöscht, aber das Original lässt sich leicht wiederherstellen, indem du die Schritte in "Beispiele für die Modellierung von Entity Relationships" befolgst . So kannst du die Datenbank vorübergehend löschen:

mysql> DROP DATABASE sakila;

Query OK, 23 rows affected (0.06 sec)

Die Anweisung DROP wird am Ende dieses Kapitels unter "Löschen von Strukturen" näher erläutert .

Um zu beginnen, erstelle die Datenbank sakila mit der Anweisung:

mysql> CREATE DATABASE sakila;

Query OK, 1 row affected (0.00 sec)

Wähle dann die Datenbank mit aus:

mysql> USE sakila;

Database changed

Jetzt können wir damit beginnen, die Tabellen zu erstellen, die unsere Daten enthalten werden. Erstellen wir eine Tabelle für die Details der Schauspieler. Für den Anfang nehmen wir eine vereinfachte Struktur, die wir später noch komplexer gestalten werden. Hier ist die Anweisung, die wir verwenden:

mysql> CREATE TABLE actor (
    -> actor_id SMALLINT UNSIGNED NOT NULL DEFAULT 0,
    -> first_name VARCHAR(45) DEFAULT NULL,
    -> last_name VARCHAR(45),
    -> last_update TIMESTAMP,
    -> PRIMARY KEY (actor_id)
    -> );

Query OK, 0 rows affected (0.01 sec)

Keine Panik - auch wenn MySQL meldet, dass keine Zeilen betroffen sind, hat es die Tabelle erstellt:

mysql> SHOW tables;

+------------------+
| Tables_in_sakila |
+------------------+
| actor            |
+------------------+
1 row in set (0.01 sec)

Schauen wir uns das alles im Detail an. Der Befehl CREATE TABLE hat drei große Abschnitte:

1. Die Anweisung CREATE TABLE, auf die der Name der zu erstellenden Tabelle folgt. In diesem Beispiel ist es actor.

2. Eine Liste mit einer oder mehreren Spalten, die der Tabelle hinzugefügt werden sollen. In diesem Beispiel haben wir eine ganze Reihe von Spalten hinzugefügt: actor_id SMALLINT UNSIGNED NOT NULL DEFAULT 0, first_name VARCHAR(45) DEFAULT NULL, last_name VARCHAR(45) und last_update TIMESTAMP. Wir werden sie gleich besprechen.

3. Optionale Schlüsseldefinitionen. In diesem Beispiel haben wir einen einzigen Schlüssel definiert: PRIMARY KEY (actor_id). Wir werden Schlüssel und Indizes später in diesem Kapitel ausführlich besprechen.

Beachte, dass auf die Komponente CREATE TABLE eine öffnende Klammer folgt, die von einer schließenden Klammer am Ende der Anweisung ergänzt wird. Beachte auch, dass die anderen Komponenten durch Kommas getrennt sind. Es gibt noch weitere Elemente, die du zu einer CREATE TABLE Anweisung hinzufügen kannst.

Kommen wir nun zu den Spaltenangaben. Die grundlegende Syntax lautet wie folgt: name type [NOT NULL | NULL] [DEFAULT value]. Das name ist der Spaltenname und unterliegt denselben Einschränkungen wie Datenbanknamen, die im vorherigen Abschnitt erläutert wurden. Er darf höchstens 64 Zeichen lang sein, Schrägstriche und Punkte sind nicht erlaubt, er darf nicht mit einem Leerzeichen enden und die Groß- und Kleinschreibung hängt vom zugrunde liegenden Betriebssystem ab. Das Feld type Feld legt fest, wie und was in der Spalte gespeichert wird. Wir haben zum Beispiel gesehen, dass es für Zeichenketten auf VARCHAR, für Zahlen auf SMALLINT und für Datum und Uhrzeit auf TIMESTAMP eingestellt werden kann.

Wenn du NOT NULL angibst, ist eine Zeile ohne einen Wert für die Spalte nicht gültig; wenn du NULL angibst oder diese Klausel auslässt, kann eine Zeile ohne einen Wert für die Spalte existieren. Wenn du eine value mit der DEFAULT Klausel angibst, wird die Spalte damit aufgefüllt, wenn du sonst keine Daten angibst; das ist besonders nützlich, wenn du häufig einen Standardwert wie z. B. einen Ländernamen wieder verwendest. Die value muss eine Konstante sein (z. B. 0, "cat" oder 20060812045623), außer wenn die Spalte vom Typ TIMESTAMP ist. Die Typen werden im Abschnitt "Spaltentypen" ausführlich behandelt .

Die Funktionen NOT NULL und DEFAULT können zusammen verwendet werden. Wenn du NOT NULL angibst und einen DEFAULT Wert hinzufügst, wird der Standardwert verwendet, wenn du keinen Wert für die Spalte angibst. Manchmal funktioniert das gut:

mysql> INSERT INTO actor(first_name) VALUES ('John');

Query OK, 1 row affected (0.01 sec)

Und manchmal auch nicht:

mysql> INSERT INTO actor(first_name) VALUES ('Elisabeth');

ERROR 1062 (23000): Duplicate entry '0' for key 'actor.PRIMARY'

Ob es funktioniert oder nicht, hängt von den zugrunde liegenden Einschränkungen und Bedingungen der Datenbank ab: In diesem Beispiel hat actor_id den Standardwert 0, aber es ist auch der Primärschlüssel. Es ist nicht erlaubt, zwei Zeilen mit demselben Primärschlüsselwert zu haben. Daher schlägt der zweite Versuch, eine Zeile ohne Werte einzufügen (mit dem resultierenden Primärschlüsselwert 0), fehl. Wir besprechen Primärschlüssel im Detail unter "Schlüssel und Indizes".

Spaltennamen haben weniger Einschränkungen als Datenbank- und Tabellennamen Namen. Außerdem sind die Namen unabhängig von der Groß- und Kleinschreibung und auf allen Plattformen anwendbar. In Spaltennamen sind alle Zeichen erlaubt, aber wenn du sie mit Leerzeichen abschließen oder Punkte oder andere Sonderzeichen wie Semikolon oder Bindestrich einfügen willst, musst du den Namen in Backticks (`) einschließen. Auch hier empfehlen wir, dass du für entwicklungsbedingte Entscheidungen (z. B. Datenbank-, Alias- und Tabellennamen) konsequent Kleinbuchstaben wählst und Zeichen vermeidest, bei denen du dich an die Verwendung von Backticks erinnern musst.

Die Benennung von Spalten und anderen Datenbankobjekten ist eine persönliche Vorliebe, wenn du neu anfängst (du kannst dich von den Beispieldatenbanken inspirieren lassen) oder eine Frage der Einhaltung von Standards, wenn du an einer bestehenden Codebasis arbeitest. Generell solltest du versuchen, Wiederholungen zu vermeiden: Verwende in einer Tabelle mit dem Namen actor den Spaltennamen first_name und nicht actor_first_name, der in einer komplexen Abfrage vor dem Tabellennamen überflüssig erscheinen würde (actor.actor_first_name gegenüber actor.first_name). Eine Ausnahme bildet der allgegenwärtige Spaltenname id. Entweder vermeidest du diesen oder du stellst den Tabellennamen vor, um die Übersichtlichkeit zu erhöhen (z. B. actor_id). Es ist gute Praxis, Wörter mit einem Unterstrich zu trennen. Du kannst auch ein anderes Zeichen verwenden, z. B. einen Bindestrich oder Schrägstrich, aber dann musst du dich daran erinnern, die Namen mit Backticks zu umschließen (z. B, actor-id). Du kannst die Worttrennung auch ganz weglassen, aber die "CamelCase"-Formatierung ist wohl schwieriger zu lesen. Wie bei den Datenbank- und Tabellennamen darf auch der Spaltenname höchstens 64 Zeichen lang sein.

Sortierung und Zeichensätze

Wenn du Zeichenketten vergleichst oder sortierst, hängt es vom Zeichensatz und der verwendeten Sortierung ab, wie MySQL das Ergebnis auswertet. Zeichensätze legen fest, welche Zeichen gespeichert werden können; zum Beispiel musst du vielleicht nicht-englische Zeichen wie ю oder ü speichern. Eine Sortierreihenfolge legt fest, wie Zeichenketten geordnet werden, und für verschiedene Sprachen gibt es unterschiedliche Sortierreihenfolgen: Die Position des Zeichens ü im Alphabet ist zum Beispiel in zwei deutschen Sortierreihenfolgen unterschiedlich und in schwedischen und finnischen wiederum unterschiedlich. Da nicht jeder englische Zeichenketten speichern möchte, ist es wichtig, dass ein Datenbankserver in der Lage ist, nicht-englische Zeichen und verschiedene Arten der Sortierung vonZeichen zu verwalten.

Wir verstehen, dass dir die Diskussion über Sortierungen und Zeichensätze zu fortgeschritten vorkommen kann, wenn du gerade erst anfängst, MySQL zu lernen. Wir sind jedoch der Meinung, dass es sich lohnt, diese Themen zu behandeln, da nicht passende Zeichensätze und Sortierungen zu unerwarteten Situationen wie Datenverlust und falschen Abfrageergebnissen führen können. Wenn du es vorziehst, kannst du diesen Abschnitt und einen Teil der späteren Diskussion in diesem Kapitel überspringen und auf diese Themen zurückkommen, wenn du sie genauer kennenlernen möchtest. Das hat keinen Einfluss auf dein Verständnis der anderen Inhalte in diesem Buch.

In unseren vorherigen Beispielen für String-Vergleiche haben wir das Problem der Sortierung und des Zeichensatzes ignoriert und MySQL einfach seine Standardeinstellungen verwenden lassen. In den MySQL-Versionen vor 8.0 ist der Standard-Zeichensatz latin1 und die Standard-Sortierreihenfolge ist latin1_swedish_ci. Mit MySQL 8.0 wurden die Standardeinstellungen geändert, so dass der Standardzeichensatz nun utf8mb4 und die Standardsortierreihenfolge utf8mb4_0900_ai_ci ist. MySQL kann so konfiguriert werden, dass verschiedene Zeichensätze und Sortierreihenfolgen auf Verbindungs-, Datenbank-, Tabellen- und Spaltenebene verwendet werden. Die hier gezeigten Ausgaben stammen aus MySQL 8.0.

Du kannst die Zeichensätze, die auf deinem Server verfügbar sind, mit dem Befehl SHOW CHARACTER SET auflisten. Dieser zeigt eine kurze Beschreibung jedes Zeichensatzes, seine Standardsortierreihenfolge und die maximale Anzahl von Bytes, die für jedes Zeichen in diesem Zeichensatz verwendet wird:

mysql> SHOW CHARACTER SET;

+----------+---------------------------------+---------------------+--------+
| Charset  | Description                     | Default collation   | Maxlen |
+----------+---------------------------------+---------------------+--------+
| armscii8 | ARMSCII-8 Armenian              | armscii8_general_ci |      1 |
| ascii    | US ASCII                        | ascii_general_ci    |      1 |
| big5     | Big5 Traditional Chinese        | big5_chinese_ci     |      2 |
| binary   | Binary pseudo charset           | binary              |      1 |
| cp1250   | Windows Central European        | cp1250_general_ci   |      1 |
| cp1251   | Windows Cyrillic                | cp1251_general_ci   |      1 |
| ...                                                                       |
| ujis     | EUC-JP Japanese                 | ujis_japanese_ci    |      3 |
| utf16    | UTF-16 Unicode                  | utf16_general_ci    |      4 |
| utf16le  | UTF-16LE Unicode                | utf16le_general_ci  |      4 |
| utf32    | UTF-32 Unicode                  | utf32_general_ci    |      4 |
| utf8     | UTF-8 Unicode                   | utf8_general_ci     |      3 |
| utf8mb4  | UTF-8 Unicode                   | utf8mb4_0900_ai_ci  |      4 |
+----------+---------------------------------+---------------------+--------+
41 rows in set (0.00 sec)

Der Zeichensatz latin1 zum Beispiel ist eigentlich, der Zeichensatz der Windows Codepage 1252, der westeuropäische Sprachen unterstützt. Die Standard-Sortierreihenfolge für diesen Zeichensatz ist latin1_swedish_ci, die den schwedischen Konventionen für die Sortierung von Zeichen mit Akzent folgt (Englisch wird wie erwartet behandelt). Bei dieser Sortierung wird die Groß- und Kleinschreibung nicht berücksichtigt, was durch die Buchstaben ci angezeigt wird. Schließlich benötigt jedes Zeichen 1 Byte. Zum Vergleich: Wenn du den Standardzeichensatz utf8mb4 verwendest, benötigt jedes Zeichen bis zu 4 Byte an Speicherung. Manchmal ist es sinnvoll, den Standard zu ändern. Es gibt zum Beispiel keinen Grund, Base64-kodierte Daten (die per Definition ASCII sind) in utf8mb4 zu speichern.

Ebenso kannst du die Sortierreihenfolge und die Zeichensätze auflisten, für die sie gilt:

mysql> SHOW COLLATION;

+---------------------+----------+-----+---------+...+---------------+
| Collation           | Charset  | Id  | Default |...| Pad_attribute |
+---------------------+----------+-----+---------+...+---------------+
| armscii8_bin        | armscii8 |  64 |         |...| PAD SPACE     |
| armscii8_general_ci | armscii8 |  32 | Yes     |...| PAD SPACE     |
| ascii_bin           | ascii    |  65 |         |...| PAD SPACE     |
| ascii_general_ci    | ascii    |  11 | Yes     |...| PAD SPACE     |
| ...                                            |...|               |
| utf8mb4_0900_ai_ci  | utf8mb4  | 255 | Yes     |...| NO PAD        |
| utf8mb4_0900_as_ci  | utf8mb4  | 305 |         |...| NO PAD        |
| utf8mb4_0900_as_cs  | utf8mb4  | 278 |         |...| NO PAD        |
| utf8mb4_0900_bin    | utf8mb4  | 309 |         |...| NO PAD        |
| ...                                            |...|               |
| utf8_unicode_ci     | utf8     | 192 |         |...| PAD SPACE     |
| utf8_vietnamese_ci  | utf8     | 215 |         |...| PAD SPACE     |
+---------------------+----------+-----+---------+...+---------------+
272 rows in set (0.02 sec)

Hinweis

Die Anzahl der verfügbaren Zeichensätze und Sortierungen hängt davon ab, wie der MySQL-Server gebaut und verpackt wurde. Die Beispiele, die wir zeigen, stammen von einer Standardinstallation von MySQL 8.0, und die gleichen Zahlen sind auch unter Linux und Windows zu sehen. MariaDB 10.5 hingegen hat 322 Sortierreihen, aber nur 40 Zeichensätze.

Du kannst die aktuellen Standardeinstellungen auf deinem Server wie folgt sehen:

mysql> SHOW VARIABLES LIKE 'c%';

+--------------------------+--------------------------------+
| Variable_name            | Value                          |
+--------------------------+--------------------------------+
| ...                                                       |
| character_set_client     | utf8mb4                        |
| character_set_connection | utf8mb4                        |
| character_set_database   | utf8mb4                        |
| character_set_filesystem | binary                         |
| character_set_results    | utf8mb4                        |
| character_set_server     | utf8mb4                        |
| character_set_system     | utf8                           |
| character_sets_dir       | /usr/share/mysql-8.0/charsets/ |
| ...                                                       |
| collation_connection     | utf8mb4_0900_ai_ci             |
| collation_database       | utf8mb4_0900_ai_ci             |
| collation_server         | utf8mb4_0900_ai_ci             |
| ...                                                       |
+--------------------------+--------------------------------+
21 rows in set (0.00 sec)

Wenn du eine Datenbank erstellst, kannst du den Standardzeichensatz und die Sortierreihenfolge für die Datenbank und ihre Tabellen festlegen. Wenn du zum Beispiel den Zeichensatz utf8mb4 und die Sortierreihenfolge utf8mb4_ru_0900_as_cs (Groß- und Kleinschreibung beachten) verwenden möchtest, schreibst du:

mysql> CREATE DATABASE rose DEFAULT CHARACTER SET utf8mb4
    -> COLLATE utf8mb4_ru_0900_as_cs;

Query OK, 1 row affected (0.00 sec)

Normalerweise ist das nicht nötig, wenn du MySQL korrekt für deine Sprache und Region installiert hast und wenn du nicht vorhast, deine Anwendung zu internationalisieren. Da utf8mb4 seit MySQL 8.0 die Standardeinstellung ist, gibt es noch weniger Grund, den Zeichensatz zu ändern. Du kannst auch den Zeichensatz und die Sortierreihenfolge für einzelne Tabellen oder Spalten festlegen, aber wir werden hier nicht im Detail darauf eingehen, wie das geht. Wie sich die Sortierreihenfolge auf String-Typen auswirkt, wird im Abschnitt "String-Typen" behandelt .

Andere Merkmale

In diesem Abschnitt werden weitere Funktionen der Anweisung CREATE TABLE kurz beschrieben. Er enthält ein Beispiel, in dem die Funktion IF NOT EXISTS verwendet wird, sowie eine Liste der fortgeschrittenen Funktionen und der Stellen, an denen du in diesem Buch mehr über sie erfährst. Die gezeigte Anweisung ist die vollständige Darstellung der Tabelle aus der Datenbank sakila, im Gegensatz zum vorherigen vereinfachten Beispiel.

Du kannst die Schlüsselwortphrase IF NOT EXISTS verwenden, wenn du eine Tabelle erstellst, und es funktioniert genauso wie bei Datenbanken. Hier ist ein Beispiel, das keinen Fehler meldet, selbst wenn die Tabelle actor existiert:

mysql> CREATE TABLE IF NOT EXISTS actor (
    -> actor_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
    -> first_name VARCHAR(45) NOT NULL,
    -> last_name VARCHAR(45) NOT NULL,
    -> last_update TIMESTAMP NOT NULL DEFAULT
    -> CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    -> PRIMARY KEY  (actor_id),
    -> KEY idx_actor_last_name (last_name));

Query OK, 0 rows affected, 1 warning (0.01 sec)

Du kannst sehen, dass null Zeilen betroffen sind und eine Warnung gemeldet wird. Schauen wir uns das mal an:

mysql> SHOW WARNINGS;

+-------+------+------------------------------+
| Level | Code | Message                      |
+-------+------+------------------------------+
| Note  | 1050 | Table 'actor' already exists |
+-------+------+------------------------------+
1 row in set (0.01 sec)

Es gibt eine ganze Reihe zusätzlicher Funktionen, die du zu einer CREATE TABLE Anweisung hinzufügen kannst, von denen wir in diesem Beispiel nur einige wenige vorstellen. Viele davon sind fortgeschritten und werden in diesem Buch nicht behandelt, aber du findest weitere Informationen im MySQL Reference Manual im Abschnitt über die CREATE TABLE Anweisung. Zu diesen zusätzlichen Funktionen gehören die folgenden:

Die Funktion AUTO_INCREMENT für numerische Spalten

Mit dieser Funktion kannst du automatisch eindeutige Bezeichner für eine Tabelle erstellen. Wir besprechen es ausführlich in "Das AUTO_INCREMENT Feature".

Kommentare zur Kolumne

Du kannst einen Kommentar zu einer Spalte hinzufügen; dieser wird angezeigt, wenn du den Befehl SHOW CREATE TABLE verwendest, den wir später in diesem Abschnitt besprechen.

Fremdschlüssel-Beschränkungen

Du kannst MySQL anweisen, zu prüfen, ob die Daten in einer oder mehreren Spalten mit den Daten in einer anderen Tabelle übereinstimmen. Die Datenbank sakila hat zum Beispiel eine Fremdschlüssel-Beschränkung für die Spalte city_id der Tabelle address, die sich auf die Spalte city_id der Tabelle city bezieht. Das bedeutet, dass es unmöglich ist, eine Adresse in einer Stadt zu haben, die nicht in der Tabelle city enthalten ist. In Kapitel 2 haben wir Fremdschlüssel-Beschränkungen vorgestellt, und in "Alternative Speicher-Engines" werden wir uns ansehen, welche Engines Fremdschlüssel-Beschränkungen unterstützen . Nicht jede Speicher-Engine in MySQL unterstützt Fremdschlüssel.

Temporäre Tabellen erstellen

Wenn du eine Tabelle mit der Schlüsselwortphrase CREATE TEMPORARY TABLE erstellst, wird sie entfernt (gelöscht), wenn die Verbindung geschlossen wird. Das ist beim Kopieren und Neuformatieren von Daten nützlich, weil du dich nicht daran erinnern musst, sie zu bereinigen. Manchmal werden temporäre Tabellen auch zur Optimierung verwendet, um Zwischendaten zu speichern.

Erweiterte Tabellenoptionen

Mit den Tabellenoptionen kannst du eine ganze Reihe von Funktionen der Tabelle steuern. Dazu gehören der Startwert von AUTO_INCREMENT, die Art und Weise, wie Indizes und Zeilen gespeichert werden, und Optionen, mit denen du die Informationen, die der MySQL-Abfrageoptimierer aus der Tabelle sammelt, außer Kraft setzen kannst. Es ist auch möglich, generierte Spalten anzugeben, die Daten wie die Summe zweier anderer Spalten enthalten, sowie Indizes für solche Spalten.

Kontrolle über Indexstrukturen

Bei einigen Speicher-Engines in MySQL kannst du angeben und steuern, welche Art von interner Struktur - z. B. einen B-Baum oder eine Hashtabelle - MySQL für seine Indizes verwendet. Du kannst MySQL auch mitteilen, dass du einen Volltext- oder einen räumlichen Datenindex für eine Spalte haben möchtest, was spezielle Sucharten ermöglicht.

Aufteilung

MySQL unterstützt verschiedene Partitionierungsstrategien, die du bei der Tabellenerstellung oder später auswählen kannst. Wir werden die Partitionierung in diesem Buch nicht behandeln.

Du kannst die Anweisung zum Erstellen einer Tabelle mit der in Kapitel 3 vorgestellten Anweisung SHOW CREATE TABLE sehen. Dies zeigt dir oft eine Ausgabe, die einige der erweiterten Funktionen enthält, die wir gerade besprochen haben; die Ausgabe stimmt nur selten mit dem überein, was du tatsächlich eingegeben hast, um die Tabelle zu erstellen. Hier ist ein Beispiel für die Tabelle actor:

mysql> SHOW CREATE TABLE actor\G

*************************** 1. row ***************************
       Table: actor
Create Table: CREATE TABLE `actor` (
  `actor_id` smallint unsigned NOT NULL AUTO_INCREMENT,
  `first_name` varchar(45) NOT NULL,
  `last_name` varchar(45) NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
        ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`actor_id`),
  KEY `idx_actor_last_name` (`last_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
        COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

Du wirst feststellen, dass die Ausgabe Inhalte enthält, die von MySQL hinzugefügt wurden und nicht in unserem ursprünglichen CREATE TABLE Statement enthalten waren:

• Die Namen der Tabelle und der Spalten werden in Backticks eingeschlossen. Das ist nicht notwendig, aber es vermeidet Parsing-Probleme, die durch die Verwendung von reservierten Wörtern und Sonderzeichen verursacht werden können, wie bereits erwähnt.

• Eine zusätzliche Standardklausel ENGINE ist enthalten, die explizit den Tabellentyp angibt, der verwendet werden soll. Die Einstellung in einer Standardinstallation von MySQL lautet InnoDB und hat daher in diesem Beispiel keine Auswirkungen.

• Es gibt eine zusätzliche DEFAULT CHARSET Klausel, die MySQL mitteilt, welcher Zeichensatz von den Spalten in der Tabelle verwendet wird. Auch dies hat bei einer Standardinstallation keine Auswirkung.

Säulen-Typen

Dieser Abschnitt beschreibt die Spaltentypen, die du in MySQL verwenden kannst. Es wird erklärt, wann jeder Typ verwendet werden sollte und welche Einschränkungen er hat. Die Typen sind nach ihrem Zweck gruppiert. Wir gehen auf die am häufigsten verwendeten Datentypen ein und erwähnen fortgeschrittenere oder weniger verwendete Typen nur am Rande. Das bedeutet nicht, dass sie keine Verwendung haben, aber betrachte das Kennenlernen dieser Typen als eine Übung. Höchstwahrscheinlich wirst du dich nicht an jeden einzelnen Datentyp und seine Besonderheiten erinnern, und das ist auch in Ordnung. Es lohnt sich, dieses Kapitel später noch einmal zu lesen und die MySQL-Dokumentation zu diesem Thema zu konsultieren, um dein Wissen auf dem neuesten Stand zu halten.

Integer-Typen

Wir beginnen mit den numerischen Datentypen, genauer gesagt mit den Ganzzahltypen, also den Typen, die bestimmte ganze Zahlen enthalten. Zunächst die beiden gängigsten Integer-Typen:

INT[(width)] [UNSIGNED] [ZEROFILL]

Dies ist der am häufigsten verwendete numerische Typ; er speichert ganzzahlige Werte im Bereich von -2.147.483.648 bis 2.147.483.647. Wenn das optionale Schlüsselwort UNSIGNED hinzugefügt wird, liegt der Bereich zwischen 0 und 4.294.967.295. Das Schlüsselwort INT ist die Abkürzung für INTEGER und kann austauschbar verwendet werden. Eine INT Spalte benötigt 4 Byte Speicherplatz.

INTwie auch andere Integer-Typen, hat zwei Eigenschaften, die für MySQL spezifisch sind: optionale width und ZEROFILL Argumente. Sie sind nicht Teil eines SQL-Standards und seit MySQL 8.0 veraltet. Dennoch wirst du sie sicherlich in vielen Codebasen finden, deshalb werden wir beide kurz behandeln.

Der width Parameter gibt die Anzeigebreite an, die von Anwendungen als Teil der Spaltenmetadaten gelesen werden kann. Im Gegensatz zu Parametern an ähnlicher Stelle für andere Datentypen hat dieser Parameter keinen Einfluss auf die Speichereigenschaften eines bestimmten Integer-Typs und schränkt den nutzbaren Wertebereich nicht ein. INT(4) und INT(32) sind für die Zwecke der Speicherung von Daten gleich.

ZEROFILL ist ein zusätzliches Argument, das verwendet wird, um die Werte mit Nullen bis zu der durch den Parameter angegebenen Länge zu füllen. width Parameter angegeben ist. Wenn du ZEROFILL verwendest, fügt MySQL automatisch UNSIGNED zur Deklaration hinzu (da das Auffüllen mit Nullen nur im Zusammenhang mit positiven Zahlen Sinn macht).

In einigen wenigen Anwendungen, in denen ZEROFILL und width nützlich sind, kann die Funktion LPAD() verwendet werden, oder die Zahlen können in CHAR Spalten formatiert gespeichert werden.

BIGINT[(width)] [UNSIGNED] [ZEROFILL]

In der Welt der immer größer werdenden Datenmengen kommt es immer häufiger vor, dass Tabellen mit einer Anzahl von Zeilen in dieMilliarden gehen. Selbst einfache Spalten des Typs id benötigen möglicherweise einen größeren Bereich als den, den ein normaler INT bietet. BIGINT löst dieses Problem. Es ist ein großer Ganzzahlentyp mit einem vorzeichenbehafteten Bereich von -9.223.372.036.854.775.808 bis 9.223.372.036.854.775.807. Ein BIGINT ohne Vorzeichen kann Zahlen von 0 bis 18.446.744.073.709.551.615 speichern. Für Spalten dieses Typs werden 8 Byte Speicherung benötigt.

Intern werden alle Berechnungen in MySQL mit vorzeichenbehafteten BIGINT oder DOUBLE Werten durchgeführt. Die wichtigste Konsequenz daraus ist, dass du sehr vorsichtig sein solltest, wenn du mit extrem großen Zahlen arbeitest. Es gibt zwei Probleme, die du beachten solltest. Erstens sollten große Ganzzahlen ohne Vorzeichen, die größer als 9.223.372.036.854.775.807 sind, nur mit Bitfunktionen verwendet werden. Zweitens: Wenn das Ergebnis einer arithmetischen Operation größer als 9.223.372.036.854.775.807 ist, kann es zu unerwarteten Ergebnissen kommen.

Zum Beispiel:

mysql> CREATE TABLE test_bigint (id BIGINT UNSIGNED);

Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO test_bigint VALUES (18446744073709551615);

Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO test_bigint VALUES (18446744073709551615-1);

Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO test_bigint VALUES (184467440737095516*100);

ERROR 1690 (22003): BIGINT value
is out of range in '(184467440737095516 * 100)'

Auch wenn 18.446.744.073.709.551.600 kleiner ist als 18.446.744.073.709.551.615, wird der Fehler außerhalb des Bereichs beobachtet, da intern ein vorzeichenbehaftetes BIGINT für die Multiplikation verwendet wird.

Tipp

Der Datentyp SERIAL kann als Alias für BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE verwendet werden. Wenn du nicht auf Datengröße und Leistung optimieren musst, solltest du SERIAL für deine id-ähnlichen Spalten verwenden. Auch UNSIGNED INT kann viel schneller außer Reichweite geraten, als du denkst, und das oft zum denkbar ungünstigsten Zeitpunkt.

Bedenke, dass es zwar möglich ist, jede ganze Zahl als BIGINT zu speichern, aber das ist eine Verschwendung von Speicherplatz. Außerdem schränkt, wie bereits erwähnt, der width Parameter den Wertebereich nicht einschränken. Um Speicherplatz zu sparen und die gespeicherten Werte einzuschränken, solltest du verschiedene Integer-Typen verwenden:

SMALLINT[(width)] [UNSIGNED] [ZEROFILL]

Speichert kleine Ganzzahlen mit einem Bereich von -32.768 bis 32.767 mit Vorzeichen und von 0 bis 65.535 ohne Vorzeichen. Sie benötigt 2 Byte Speicherung.

TINYINT[(width)] [UNSIGNED] [ZEROFILL]

Der kleinste numerische Datentyp, der noch kleinere ganze Zahlen speichert. Der Bereich dieses Typs ist -128 bis 127 mit Vorzeichen und 0 bis 255 ohne Vorzeichen. Er benötigt nur 1 Byte an Speicherung.

BOOL[(width)]

Kurz für BOOLEAN und ein Synonym für TINYINT(1). Normalerweise akzeptieren boolesche Typen nur zwei Werte: wahr oder falsch. Da BOOL in MySQL jedoch ein Integer-Typ ist, kannst du Werte von -128 bis 127 in einer BOOL speichern. Der Wert 0 wird als falsch und alle Werte ungleich Null als wahr behandelt. Es ist auch möglich, spezielle true und false Aliase für 1 bzw. 0 zu verwenden. Hier sind einige Beispiele:

mysql> CREATE TABLE test_bool (i BOOL);

Query OK, 0 rows affected (0.04 sec)

mysql> INSERT INTO test_bool VALUES (true),(false);

Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> INSERT INTO test_bool VALUES (1),(0),(-128),(127);

Query OK, 4 rows affected (0.02 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> SELECT i, IF(i,'true','false') FROM test_bool;

+------+----------------------+
| i    | IF(i,'true','false') |
+------+----------------------+
|    1 | true                 |
|    0 | false                |
|    1 | true                 |
|    0 | false                |
| -128 | true                 |
|  127 | true                 |
+------+----------------------+
6 rows in set (0.01 sec)

MEDIUMINT[(width)] [UNSIGNED] [ZEROFILL]

Speichert Werte im vorzeichenbehafteten Bereich von -8.388.608 bis 8.388.607 und im vorzeichenlosen Bereich von 0 bis 16.777.215. Sie benötigt 3 Byte Speicherung.

BIT[(M)]

Spezieller Typ, der zum Speichern von Bit-Werten verwendet wird. M gibt die Anzahl der Bits pro Wert an und ist standardmäßig auf 1 eingestellt, wenn sie weggelassen wird. MySQL verwendet eine b'value Syntax für binäre Werte.

Festkomma-Typen

Die Datentypen DECIMAL und NUMERIC in MySQL sind gleich. Obwohl wir hier nur DECIMAL beschreiben, gilt diese Beschreibung auch für NUMERIC. Der Hauptunterschied zwischen Festkomma- und Fließkomma-Typen ist die Genauigkeit. Bei Festkomma-Typen ist der abgefragte Wert identisch mit dem gespeicherten Wert; das ist bei Typen, die Dezimalpunkte enthalten, wie den später beschriebenen Typen FLOAT und DOUBLE, nicht immer der Fall. Das ist die wichtigste Eigenschaft des DECIMAL Datentyps, der in MySQL häufig als numerischer Typ verwendet wird:

DECIMAL[(width[,decimals])] [UNSIGNED] [ZEROFILL]

Speichert eine Festkommazahl, z. B. ein Gehalt oder eine Entfernung, mit einer Gesamtzahl von width Ziffern, von denen einige kleinere Ziffern decimals die auf ein Dezimalkomma folgen. Eine Spalte, die als price DECIMAL(6,2) deklariert ist, kann zum Beispiel dazu verwendet werden, Werte im Bereich von -9.999,99 bis 9.999,99 zu speichern. price DECIMAL(10,4) würde Werte wie 123.456,1234 zulassen.

Wenn du vor MySQL 5.7 versucht hast, einen Wert außerhalb dieses Bereichs zu speichern, wurde er als der nächstgelegene Wert im zulässigen Bereich gespeichert. Zum Beispiel wurde 100 als 99,99 gespeichert und -100 als -99,99. Ab Version 5.7.5 enthält der Standard-SQL-Modus jedoch den Modus STRICT_TRANS_TABLES, der dieses und andere unsichere Verhaltensweisen verbietet. Die Verwendung des alten Verhaltens ist möglich, kann aber zu Datenverlust führen.

SQL-Modi sind spezielle Einstellungen, die das Verhalten von MySQL in Bezug auf Abfragen steuern. Sie können zum Beispiel "unsicheres" Verhalten einschränken oder beeinflussen, wie Abfragen interpretiert werden. Für das Erlernen von MySQL empfehlen wir dir, die Standardeinstellungen beizubehalten, da sie sicher sind. Das Ändern der SQL-Modi kann aus Gründen der Kompatibilität mit älteren Anwendungen in verschiedenen MySQL-Versionen erforderlich sein.

Der width ist optional, und wenn er weggelassen wird, wird der Wert 10 angenommen. Die Anzahl der decimals ist ebenfalls optional, und wenn sie weggelassen wird, wird der Wert 0 angenommen. decimals darf nicht höher sein als der Wert von width. Der Höchstwert von width ist 65, und der maximale Wert von decimals ist 30.

Wenn du nur positive Werte speichern willst, kannst du das Schlüsselwort UNSIGNED verwenden, wie für INT beschrieben. Wenn du Nullen einfügen willst, kannst du das Schlüsselwort ZEROFILL verwenden, um das gleiche Verhalten wie bei INT zu erreichen. Das Schlüsselwort DECIMAL hat drei identische, austauschbare Alternativen: DEC, NUMERIC, und FIXED.

Die Werte in den Spalten von DECIMAL werden in einem binären Format gespeichert. Dieses Format verwendet 4 Bytes für jeweils neun Ziffern.

Fließkomma-Typen

Neben dem Festkomma-Typ DECIMAL, der im vorherigen Abschnitt beschrieben wurde, gibt es zwei weitere Typen, die Dezimalpunkte unterstützen: DOUBLE (auch bekannt als REAL) und FLOAT. Sie wurden entwickelt, um ungefähre numerische Werte zu speichern und nicht die exakten Werte, die von DECIMAL gespeichert werden.

Warum willst du Näherungswerte? Die Antwort ist, dass viele Zahlen mit einem Dezimalpunkt Näherungswerte für reale Größen sind. Nehmen wir zum Beispiel an, du verdienst 50.000 US-Dollar pro Jahr und möchtest dies als Monatslohn speichern. Wenn du dies in einen monatlichen Betrag umrechnest, sind es 4.166 $ plus 66 und 2/3 Cent. Wenn du diesen Betrag als 4.166,67 $ speicherst, ist er nicht genau genug, um ihn in einen Jahreslohn umzurechnen (denn 12 multipliziert mit 4.166,67 $ ergibt 50.000,04 $). Wenn du jedoch 2/3 mit genügend Nachkommastellen speicherst, ist es ein genauerer Näherungswert. Du wirst feststellen, dass es genau genug ist, um in einer hochpräzisen Umgebung wie MySQL korrekt zu multiplizieren und den ursprünglichen Wert zu erhalten, wobei nur ein wenig gerundet werden muss. Hier sind DOUBLE und FLOAT nützlich: Sie ermöglichen es dir, Werte wie 2/3 oder Pi mit einer großen Anzahl von Nachkommastellen zu speichern, so dass exakte Größen als Näherungswerte dargestellt werden können. Später kannst du die Funktion ROUND() verwenden, um die Ergebnisse auf eine bestimmte Genauigkeit zu bringen.

Setzen wir das vorherige Beispiel mit DOUBLE fort. Angenommen, du erstellst eine Tabelle wiefolgt:

mysql> CREATE TABLE wage (monthly DOUBLE);

Query OK, 0 rows affected (0.09 sec)

Jetzt kannst du den Monatslohn mit eingeben:

mysql> INSERT INTO wage VALUES (50000/12);

Query OK, 1 row affected (0.00 sec)

Und schau nach, was gespeichert ist:

mysql> SELECT * FROM wage;

+----------------+
| monthly        |
+----------------+
| 4166.666666666 |
+----------------+
1 row in set (0.00 sec)

Wenn du ihn jedoch multiplizierst, um einen Jahreswert zu erhalten, erhältst du einen hochpräzisen Näherungswert:

mysql> SELECT monthly*12 FROM wage;

+--------------------+
| monthly*12         |
+--------------------+
| 49999.999999992004 |
+--------------------+
1 row in set (0.00 sec)

Um den ursprünglichen Wert zurückzubekommen, musst du noch eine Rundung mit der gewünschten Genauigkeit vornehmen. Zum Beispiel könnte dein Unternehmen eine Genauigkeit von fünf Dezimalstellen benötigen. In diesem Fall könntest du den ursprünglichen Wert mit wiederherstellen:

mysql> SELECT ROUND(monthly*12,5) FROM wage;

+---------------------+
| ROUND(monthly*12,5) |
+---------------------+
|         50000.00000 |
+---------------------+
1 row in set (0.00 sec)

Aber die Genauigkeit auf acht Nachkommastellen würde nicht den ursprünglichen Wert ergeben:

mysql> SELECT ROUND(monthly*12,8) FROM wage;

+---------------------+
| ROUND(monthly*12,8) |
+---------------------+
|      49999.99999999 |
+---------------------+
1 row in set (0.00 sec)

Es ist wichtig zu verstehen, dass Fließkommadatentypen unpräzise und ungefähr sind.

Hier sind die Details zu den Typen FLOAT und DOUBLE:

FLOAT[(width, decimals)] [UNSIGNED] [ZEROFILL] oder FLOAT[(precision)] [UNSIGNED] [ZEROFILL]

Speichert Fließkommazahlen. Sie hat zwei optionale Syntaxen: Die erste erlaubt eine optionale Anzahl von decimals und eine optionale Anzeige width, und die zweite erlaubt eine optionale precision die die Genauigkeit der Annäherung, gemessen in Bits, steuert. Ohne Parameter (die typische Verwendung) speichert der Typ kleine, 4-Byte große, einfach genaue Fließkommazahlen. Wenn precision zwischen 0 und 24 liegt, tritt das Standardverhalten ein. Wenn precision zwischen 25 und 53 liegt, verhält sich der Typ wie DOUBLE. Der width hat keinen Einfluss darauf, was gespeichert wird, sondern nur darauf, was angezeigt wird. Die Optionen UNSIGNED und ZEROFILL verhalten sich wie bei INT.

DOUBLE[(width, decimals)] [UNSIGNED] [ZEROFILL]

Speichert Fließkommazahlen. Sie ermöglicht die Angabe einer optionalen Anzahl von decimals und einer optionalen Anzeige width. Ohne Parameter (die typische Verwendung) speichert der Typ normale 8-Byte-Gleitkommazahlen mit doppelter Genauigkeit. Der width Parameter hat keinen Einfluss darauf, was gespeichert wird, sondern nur darauf, was angezeigt wird. Die Optionen UNSIGNED und ZEROFILL verhalten sich wie bei INT. Der Typ DOUBLE hat zwei identische Synonyme: REAL und DOUBLE PRECISION.

String-Typen

String-Datentypen werden verwendet, um Text und, weniger offensichtlich, binäre Daten zu speichern. MySQL unterstützt die folgenden String-Typen:

[NATIONAL] VARCHAR(width) [CHARACTER SET charset_name] [COLLATE collation_name]

Der wahrscheinlich am häufigsten verwendete Stringtyp, VARCHAR speichert Strings mit variabler Länge bis zu einer maximalen width. Der maximale Wert von width beträgt 65.535 Zeichen. Die meisten Informationen, die für diesen Typ gelten, treffen auch auf andere Stringtypen zu.

Die Typen CHAR und VARCHAR sind sich sehr ähnlich, aber es gibt ein paar wichtige Unterschiede. VARCHAR verursacht ein oder zwei zusätzliche Bytes an Overhead, um den Wert der Zeichenkette zu speichern, je nachdem, ob der Wert kleiner oder größer als 255 Bytes ist. Diese Größe unterscheidet sich von der Länge des Strings in Zeichen, da bestimmte Zeichen bis zu 4 Byte Platz benötigen können. Es mag also offensichtlich sein, dass VARCHAR weniger effizient ist. Das ist jedoch nicht immer der Fall. Da VARCHAR Zeichenketten beliebiger Länge speichern kann (bis zur width definiert), benötigen kürzere Zeichenketten weniger Speicherplatz als eine CHAR mit ähnlicher Länge.

Ein weiterer Unterschied zwischen CHAR und VARCHAR ist die Behandlung von Leerzeichen am Ende der Spalte. VARCHAR behält Leerzeichen am Ende der Spalte bis zur angegebenen Spaltenbreite bei und schneidet den Rest ab, was zu einer Warnung führt. Wie später gezeigt wird, werden die Werte von CHAR rechts auf die Spaltenbreite aufgefüllt und die Leerzeichen am Ende der Spalte werden nicht beibehalten. Bei VARCHAR sind die Leerzeichen am Ende der Spalte wichtig, es sei denn, sie werden abgeschnitten und zählen als eindeutige Werte. Lass es uns demonstrieren:

mysql> CREATE TABLE test_varchar_trailing(d VARCHAR(2) UNIQUE);

Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO test_varchar_trailing VALUES ('a'), ('a ');

Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT d, LENGTH(d) FROM test_varchar_trailing;

+------+-----------+
| d    | LENGTH(d) |
+------+-----------+
| a    |         1 |
| a    |         2 |
+------+-----------+
2 rows in set (0.00 sec)

Die zweite Zeile, die wir eingefügt haben, hat ein Leerzeichen am Ende, und da die width für die Spalte d 2 ist, zählt dieses Leerzeichen für die Eindeutigkeit einer Zeile. Wenn wir jedoch versuchen, eine Zeile mit zwei Leerzeichen am Ende einzufügen:

mysql> INSERT INTO test_varchar_trailing VALUES ('a  ');

ERROR 1062 (23000): Duplicate entry 'a '
for key 'test_varchar_trailing.d'

MySQL weigert sich, die neue Zeile zu akzeptieren. VARCHAR(2) schneidet implizit die Leerzeichen nach dem Satz widthab, so dass sich der gespeicherte Wert von "a " (mit einem doppelten Leerzeichen nach a) in "a " (mit einem einfachen Leerzeichen nach a) ändert. Da es bereits eine Zeile mit einem solchen Wert gibt, wird ein Fehler für einen doppelten Eintrag gemeldet. Dieses Verhalten für VARCHAR und TEXT kann durch Ändern der Spaltensortierung gesteuert werden. Einige Sortierungen, wie latin1_bin, haben das Attribut PAD SPACE, was bedeutet, dass sie beim Abruf mit Leerzeichen aufgefüllt werden. width mit Leerzeichen aufgefüllt. Das hat keinen Einfluss auf die Speicherung, aber auf die Eindeutigkeitsprüfung und die Funktionsweise der Operatoren GROUP BY und DISTINCT, die wir in Kapitel 5 besprechen werden. Du kannst überprüfen, ob eine Sortierung PAD SPACE oder NO PAD ist, indem du den Befehl SHOW COLLATION ausführst, wie wir in "Sortierung und Zeichensätze" gezeigt haben . Wir wollen die Wirkung in Aktion sehen, indem wir eine Tabelle mit der Sortierreihenfolge PAD SPACE erstellen:

mysql> CREATE TABLE test_varchar_pad_collation(
    -> data VARCHAR(5) CHARACTER SET latin1
    -> COLLATE latin1_bin UNIQUE);

Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO test_varchar_pad_collation VALUES ('a');

Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO test_varchar_pad_collation VALUES ('a ');

ERROR 1062 (23000): Duplicate entry 'a '
for key 'test_varchar_pad_collation.data'

Die Kollation NO PAD ist eine Neuerung von MySQL 8.0. In früheren Versionen von MySQL, die du vielleicht noch oft im Einsatz siehst, hat jede Sortierung implizit das Attribut PAD SPACE. Daher ist in MySQL 5.7 und früheren Versionen die einzige Möglichkeit, Leerzeichen am Ende zu erhalten, die Verwendung eines binären Typs: VARBINARY oder BLOB.

Hinweis

Sowohl der CHAR als auch der VARCHAR Datentyp erlauben die Speicherung von Werten, die länger als widthsind, es sei denn, der strenge SQL-Modus ist deaktiviert (d. h. wenn weder STRICT_ALL_TABLES noch STRICT_TRANS_TABLES aktiviert ist). Wenn der Schutz deaktiviert ist, werden Werte, die länger als width sind, abgeschnitten und es wird eine Warnung angezeigt. Wir raten davon ab, das Legacy-Verhalten zu aktivieren, da dies zu Datenverlusten führen kann.

Die Sortierung und der Vergleich der Typen VARCHAR, CHAR und TEXT erfolgt nach der Sortierreihenfolge des zugewiesenen Zeichensatzes. Wie du siehst, ist es möglich, den Zeichensatz und die Sortierreihenfolge für jede einzelne String-Typ-Spalte festzulegen. Es ist auch möglich, den binary Zeichensatz anzugeben, der VARCHAR in VARBINARY umwandelt. Verwechsle den binary Zeichensatz nicht mit dem BINARY Attribut für einen Zeichensatz; letzteres ist eine reine MySQL-Verknüpfung zur Angabe einer binären (_bin) Sortierung.

Darüber hinaus ist es möglich, eine Sortierreihenfolge direkt in der ORDER BY Klausel anzugeben. Die verfügbaren Sortierreihenfolgen hängen vom Zeichensatz der Spalte ab. In der Tabelle test_varchar_pad_collation kannst du ein ä-Symbol speichern und dann sehen, wie sich die Sortierreihenfolge auf die Zeichenketten auswirkt:

mysql> INSERT INTO test_varchar_pad_collation VALUES ('ä'), ('z');

Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM test_varchar_pad_collation
    -> ORDER BY data COLLATE latin1_german1_ci;

+------+
| data |
+------+
| a    |
| ä    |
| z    |
+------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM test_varchar_pad_collation
    -> ORDER BY data COLLATE latin1_swedish_ci;

+------+
| data |
+------+
| a    |
| z    |
| ä    |
+------+
3 rows in set (0.00 sec)

Das Attribut NATIONAL (oder die entsprechende Kurzform NCHAR) ist eine Standard-SQL-Methode, um anzugeben, dass eine Spalte vom Typ String einen vordefinierten Zeichensatz verwenden muss. MySQL verwendet utf8 als diesen Zeichensatz. Es ist jedoch wichtig zu wissen, dass MySQL 5.7 und 8.0 sich nicht einig sind, was genau utf8 ist: Ersterer verwendet es als Alias für utf8mb3, letzterer für utf8mb4. Daher ist es am besten, das Attribut NATIONAL sowie zweideutige Aliasnamen nicht zu verwenden. Die bewährte Methode für alle textbezogenen Spalten und Daten ist es, so eindeutig und spezifisch wie möglich zu sein.

[NATIONAL] CHAR(width) [CHARACTER SET charset_name] [COLLATE collation_name]

CHAR speichert eine Zeichenkette fester Länge (z. B. einen Namen, eine Adresse, oder eine Stadt) der Länge width. Wenn eine width nicht angegeben wird, wird CHAR(1) angenommen. Der maximale Wert von width ist 255. Wie bei VARCHAR werden die Werte in CHAR Spalten immer in der angegebenen Länge gespeichert. Ein einzelner Buchstabe, der in einer CHAR(255) Spalte gespeichert wird, benötigt 255 Bytes (im latin1 Zeichensatz) und wird mit Leerzeichen aufgefüllt. Das Auffüllen wird beim Lesen der Daten entfernt, es sei denn, der PAD_CHAR_TO_FULL_LENGTH SQL-Modus ist aktiviert. Es ist wichtig zu erwähnen, dass dies bedeutet, dass alle Leerzeichen am Ende von Zeichenfolgen, die in CHAR Spalten gespeichert werden, verloren gehen.

In der Vergangenheit wurde die width einer CHAR Spalte oft mit einer Größe in Bytes verbunden. Das ist jetzt nicht immer der Fall, und schon gar nicht standardmäßig. Multibyte-Zeichensätze, wie der Standard utf8mb4 in MySQL 8.0, können zu viel größeren Werten führen. InnoDB kodiert Spalten mit fester Länge als Spalten mit variabler Länge, wenn ihre maximale Größe 768 Byte überschreitet. Daher speichert InnoDB in MySQL 8.0 standardmäßig eine CHAR(255) Spalte wie eine VARCHAR Spalte. Hier ist ein Beispiel:

mysql> CREATE TABLE test_char_length(
    ->   utf8char CHAR(10) CHARACTER SET utf8mb4
    -> , asciichar CHAR(10) CHARACTER SET binary
    -> );

Query OK, 0 rows affected (0.04 sec)

mysql> INSERT INTO test_char_length VALUES ('Plain text', 'Plain text');

Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO test_char_length VALUES ('的開源軟體', 'Plain text');

Query OK, 1 row affected (0.00 sec)

mysql> SELECT LENGTH(utf8char), LENGTH(asciichar) FROM test_char_length;

+------------------+-------------------+
| LENGTH(utf8char) | LENGTH(asciichar) |
+------------------+-------------------+
|               10 |                10 |
|               15 |                10 |
+------------------+-------------------+
2 rows in set (0.00 sec)

Da die Werte linksbündig sind und rechts mit Leerzeichen aufgefüllt werden, und alle nachfolgenden Leerzeichen für CHAR überhaupt nicht berücksichtigt werden, ist es unmöglich, Zeichenketten zu vergleichen, die nur aus Leerzeichen bestehen. Wenn du dich in einer Situation befindest, in der das wichtig ist, ist VARCHAR der richtige Datentyp.

BINARY[(width)] und VARBINARY(width)

Diese Typen sind CHAR und VARCHAR sehr ähnlich, aber speichern binäre Zeichenketten. Binäre Zeichenketten haben den speziellen binary Zeichensatz und die Sortierung, und die Sortierung ist abhängig von den numerischen Werten der Bytes in den gespeicherten Werten. Anstelle von Zeichenketten werden Byte-Zeichenketten gespeichert. In der früheren Diskussion über VARCHAR haben wir den binary Zeichensatz und das BINARY Attribut beschrieben. Nur der binary Zeichensatz "wandelt" eine VARCHAR oder CHAR in die entsprechende BINARY Form um. Die Anwendung des BINARY Attributs auf einen Zeichensatz ändert nichts an der Tatsache, dass Zeichenketten gespeichert werden. Anders als bei VARCHAR und CHAR, width ist hier genau die Anzahl der Bytes. Wenn width für BINARY weggelassen wird, ist es standardmäßig 1.

Wie bei CHAR werden die Daten in der Spalte BINARY auf der rechten Seite aufgefüllt. Da es sich jedoch um binäre Daten handelt, werden sie mit Null-Bytes aufgefüllt, die normalerweise als 0x00 oder \0 geschrieben werden. BINARY behandelt ein Leerzeichen als signifikantes Zeichen, nicht als Auffüllung. Wenn du Daten speichern musst, die mit Null-Bytes enden könnten, die für dich von Bedeutung sind, verwende die Typen VARBINARY oder BLOB.

Bei der Arbeit mit diesen beiden Datentypen ist es wichtig, das Konzept der binären Zeichenketten im Hinterkopf zu behalten. Auch wenn sie Zeichenketten akzeptieren, sind sie keine Synonyme für Datentypen mit Textstrings. Du kannst zum Beispiel die Groß- und Kleinschreibung der gespeicherten Buchstaben nicht ändern, da dieses Konzept auf binäre Daten nicht wirklich zutrifft. Das wird deutlich, wenn du dir die gespeicherten Daten ansiehst. Schauen wir uns ein Beispiel an:

mysql> CREATE TABLE test_binary_data (
    ->   d1 BINARY(16)
    -> , d2 VARBINARY(16)
    -> , d3 CHAR(16)
    -> , d4 VARCHAR(16)
    -> );

Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO test_binary_data VALUES (
    ->   'something'
    -> , 'something'
    -> , 'something'
    -> , 'something');

Query OK, 1 row affected (0.00 sec)

mysql> SELECT d1, d2, d3, d4 FROM test_binary_data;

*************************** 1. row ***************************
d1: 0x736F6D657468696E6700000000000000
d2: 0x736F6D657468696E67
d3: something
d4: something
1 row in set (0.00 sec)

mysql> SELECT UPPER(d2), UPPER(d4) FROM test_binary_data;

*************************** 1. row ***************************
UPPER(d2): 0x736F6D657468696E67
UPPER(d4): SOMETHING
1 row in set (0.01 sec)

Beachte, dass der MySQL-Befehlszeilen-Client die Werte von Binärtypen im Hex-Format anzeigt. Wir glauben, dass dies viel besser ist als die stillen Konvertierungen, die vor MySQL 8.0 durchgeführt wurden und zu Missverständnissen führen konnten. Um die tatsächlichen Textdaten zurückzubekommen, musst du die Binärdaten explizit in Text umwandeln:

mysql> SELECT CAST(d1 AS CHAR) d1t, CAST(d2 AS CHAR) d2t
    -> FROM test_binary_data;

+------------------+-----------+
| d1t              | d2t       |
+------------------+-----------+
| something        | something |
+------------------+-----------+
1 row in set (0.00 sec)

Du kannst auch sehen, dass die Auffüllungen von BINARY beim Casting in Leerzeichen umgewandelt wurden.

BLOB[(width)] und TEXT[(width)] [CHARACTER SET charset_name] [COLLATE collation_name]

BLOB und TEXT sind häufig verwendete Datentypen, um große Daten zu speichern. Du kannst dir BLOB als VARBINARY vorstellen, das so viele Daten speichert, wie du willst, und dasselbe gilt für TEXT und VARCHAR. Die Typen BLOB und TEXT können bis zu 65.535 Bytes bzw. Zeichen speichern. Wie immer gilt: Es gibt auch Multibyte-Zeichensätze. Das width Attribut ist optional. Wenn es angegeben wird, ändert MySQL den Datentyp BLOB oder TEXT in den kleinsten Typ, der diese Datenmenge speichern kann. Die Angabe BLOB(128) führt beispielsweise dazu, dass TINYBLOB verwendet wird:

mysql> CREATE TABLE test_blob(data BLOB(128));

Query OK, 0 rows affected (0.07 sec)

mysql> DESC test_blob;

+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| data  | tinyblob | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
1 row in set (0.00 sec)

Für den Typ BLOB und verwandte Typen werden die Daten genauso behandelt wie im Fall von VARBINARY. Das heißt, es wird kein Zeichensatz angenommen, und Vergleich und Sortierung basieren auf den numerischen Werten der tatsächlich gespeicherten Bytes. Bei TEXT kannst du den gewünschten Zeichensatz und die Sortierung genau angeben. Bei beiden Typen und ihren Varianten wird bei INSERT kein Padding und bei SELECT kein Trimming durchgeführt, so dass sie ideal sind, um Daten genau so zu speichern, wie sie sind. Außerdem ist eine DEFAULT Klausel nicht erlaubt, und wenn ein Index für eine BLOB oder TEXT Spalte erstellt wird, muss ein Präfix definiert werden, das die Länge der indizierten Werte begrenzt. Mehr darüber erfahren Sie unter "Schlüssel und Indizes".

Ein möglicher Unterschied zwischen BLOB und TEXT ist die Behandlung von Leerzeichen am Ende der Zeichenkette. Wie wir bereits gezeigt haben, können VARCHAR und TEXT abhängig von der verwendeten Sortierreihenfolge Zeichenfolgen auffüllen. BLOB und VARBINARY verwenden beide den Zeichensatz binary mit einer einzigen Sortierreihenfolge binary ohne Auffüllen und sind unempfindlich gegenüber Sortierreihenfolgeverwechslungen und ähnlichen Problemen. Manchmal kann es eine gute Wahl sein, diese Typen für zusätzliche Sicherheit zu verwenden. Außerdem waren dies vor MySQL 8.0 die einzigen Typen, die Leerzeichen am Ende der Zeile erhalten haben.

TINYBLOB und TINYTEXT [CHARACTER SET charset_name] [COLLATE collation_name]

Diese sind identisch mit BLOB bzw. TEXT, außer dass maximal 255 Bytes oder Zeichen gespeichert werden können.

MEDIUMBLOB und MEDIUMTEXT [CHARACTER SET charset_name] [COLLATE collation_name]

Diese sind identisch mit BLOB bzw. TEXT, mit dem Unterschied, dass maximal 16.777.215 Bytes oder Zeichen gespeichert werden können. Die Typen LONG und LONG VARCHAR werden aus Kompatibilitätsgründen auf den Datentyp MEDIUMTEXT abgebildet.

LONGBLOB und LONGTEXT [CHARACTER SET charset_name] [COLLATE collation_name]

Diese sind identisch mit BLOB bzw. TEXT, mit dem Unterschied, dass maximal 4 GB an Daten gespeichert werden können. Beachte, dass dies auch im Fall von LONGTEXT eine harte Grenze ist und somit die Anzahl der Zeichen in Multibyte-Zeichensätzen weniger als 4.294.967.295 betragen kann. Die tatsächliche Maximalgröße der Daten, die von einem Client gespeichert werden können, wird durch den verfügbaren Speicher und den Wert der max_packet_size Variable begrenzt, der standardmäßig 64 MiB beträgt.

ENUM(value1[,value2[, …]]) [CHARACTER SET charset_name] [COLLATE collation_name]

Dieser Typ speichert eine Liste oder Aufzählung von String-Werten. Eine Spalte vom Typ ENUM kann auf einen Wert aus der Liste gesetzt werden value1, value2gesetzt werden, und so weiter, bis zu einem Maximum von 65.535 verschiedenen Werten. Während die Werte als Strings gespeichert und abgerufen werden, wird in der Datenbank eine ganzzahlige Darstellung gespeichert. Die Spalte ENUM kann die Werte NULL (gespeichert als NULL), die leere Zeichenkette '' (gespeichert als 0) oder eines der gültigen Elemente (gespeichert als 1, 2, 3, usw.) enthalten. Du kannst verhindern, dass NULL Werte akzeptiert werden, indem du die Spalte bei der Erstellung der Tabelle als NOT NULL deklarierst.

Dieser Typ bietet eine kompakte Möglichkeit, Werte aus einer Liste von vordefinierten Werten zu speichern, z. B. Staats- oder Ländernamen. Der Name kann einer der vordefinierten Werte Apple, Orange oder Pear sein (zusätzlich zu NULL und dem leeren String):

mysql> CREATE TABLE fruits_enum
    -> (fruit_name ENUM('Apple', 'Orange', 'Pear'));

Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO fruits_enum VALUES ('Apple');

Query OK, 1 row affected (0.00 sec)

Wenn du versuchst, einen Wert einzufügen, der nicht in der Liste steht, gibt MySQL eine Fehlermeldung aus, um dir mitzuteilen, dass es die gewünschten Daten nicht gespeichert hat:

mysql> INSERT INTO fruits_enum VALUES ('Banana');

ERROR 1265 (01000): Data truncated for column 'fruit_name' at row 1

Eine Liste mit mehreren erlaubten Werten wird ebenfalls nicht akzeptiert:

mysql> INSERT INTO fruits_enum VALUES ('Apple,Orange');

ERROR 1265 (01000): Data truncated for column 'fruit_name' at row 1

Wenn du dir den Inhalt der Tabelle anschaust, kannst du sehen, dass keine ungültigen Werte gespeichert wurden:

mysql> SELECT * FROM fruits_enum;

+------------+
| fruit_name |
+------------+
| Apple      |
+------------+
1 row in set (0.00 sec)

Frühere Versionen von MySQL erzeugten eine Warnung statt einer Fehlermeldung und speicherten einen leeren String anstelle eines ungültigen Wertes. Dieses Verhalten kann durch Deaktivieren des standardmäßigen Strict-SQL-Modus aktiviert werden. Es ist auch möglich, einen anderen Standardwert als einen leeren String anzugeben:

mysql> CREATE TABLE new_fruits_enum
    -> (fruit_name ENUM('Apple', 'Orange', 'Pear')
    -> DEFAULT 'Pear');

Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO new_fruits_enum VALUES();

Query OK, 1 row affected (0.02 sec)

mysql> SELECT * FROM new_fruits_enum;

+------------+
| fruit_name |
+------------+
| Pear       |
+------------+
1 row in set (0.00 sec)

Wenn du hier keinen Wert angibst, wird der Standardwert Pear gespeichert.

SET( value1 [, value2 [, …]]) [CHARACTER SET charset_name] [COLLATE collation_name]

Dieser Typ speichert eine Reihe von String-Werten. Eine Spalte des Typs SET kann auf null oder mehr Werte aus der folgenden Liste gesetzt werden value1, value2und so weiter, bis zu einem Maximum von 64 verschiedenen Werten. Die Werte sind zwar Zeichenketten, werden aber in der Datenbank als Ganzzahl gespeichert. SET unterscheidet sich von ENUM dadurch, dass jede Zeile nur einen ENUM Wert in einer Spalte speichern kann, aber mehrere SET Werte. Dieser Typ ist nützlich, um eine Auswahl aus einer Liste zu speichern, z. B. die Vorlieben der Benutzer. In diesem Beispiel werden Obstnamen verwendet; der Name kann eine beliebige Kombination der vordefinierten Werte sein:

mysql> CREATE TABLE fruits_set
    -> ( fruit_name SET('Apple', 'Orange', 'Pear') );

Query OK, 0 rows affected (0.08 sec)

mysql> INSERT INTO fruits_set VALUES ('Apple');

Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO fruits_set VALUES ('Banana');

ERROR 1265 (01000): Data truncated for column 'fruit_name' at row 1

mysql> INSERT INTO fruits_set VALUES ('Apple,Orange');

Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM fruits_set;

+--------------+
| fruit_name   |
+--------------+
| Apple        |
| Apple,Orange |
+--------------+
2 rows in set (0.00 sec)

Beachte auch hier, dass wir mehrere Werte aus der Menge in einem einzigen Feld speichern können und dass bei ungültigen Eingaben ein leerer String gespeichert wird.

Wie bei den numerischen Typen empfehlen wir, dass du immer den kleinstmöglichen Typ wählst, um Werte zu speichern. Wenn du zum Beispiel den Namen einer Stadt speicherst, solltest du CHAR oder VARCHAR verwenden und nicht etwa den Typ TEXT. Kürzere Spalten tragen dazu bei, die Größe deiner Tabelle gering zu halten, was sich wiederum positiv auf die Leistung auswirkt, wenn der Server eine Tabelle durchsuchen muss.

Die Verwendung einer festen Größe mit dem Typ CHAR ist oft schneller als die Verwendung einer variablen Größe mit VARCHAR, da der MySQL-Server weiß, wo jede Zeile beginnt und endet, und schnell über Zeilen springen kann, um die benötigte zu finden. Bei Feldern mit fester Länge wird jedoch jeder Platz, den du nicht nutzt, verschwendet. Wenn du z. B. bis zu 40 Zeichen für einen Städtenamen zulässt, verwendet CHAR(40) immer 40 Zeichen, egal wie lang der Name der Stadt tatsächlich ist. Wenn du den Städtenamen als VARCHAR(40) deklarierst, belegst du nur so viel Speicherplatz, wie du brauchst, plus 1 Byte für die Länge des Namens. Wenn der durchschnittliche Städtename 10 Zeichen lang ist, bedeutet das, dass bei Verwendung eines Feldes mit variabler Länge im Durchschnitt 29 Bytes weniger pro Eintrag benötigt werden. Das kann einen großen Unterschied machen, wenn du Millionen von Adressen speicherst.

Im Allgemeinen solltest du ein Feld mit variabler Länge verwenden, wenn der Speicherplatz knapp ist oder du große Schwankungen in der Länge der zu speichernden Zeichenketten erwartest; wenn die Leistung im Vordergrund steht, solltest du ein Feld mit fester Länge verwenden.

Datum- und Zeittypen

Diese Typen dienen dazu, bestimmte Zeitstempel, Daten oder Zeitbereiche zu speichern. Besondere Vorsicht ist geboten, wenn du mit Zeitzonen zu tun hast. Wir werden versuchen, die Details zu erklären, aber es lohnt sich, diesen Abschnitt und die Dokumentation später noch einmal zu lesen, wenn du tatsächlich mit Zeitzonen arbeiten musst. Die Datums- und Zeittypen in MySQL sind:

DATE

Speichert und zeigt ein Datum im Format YYYY-MM-DD für den Bereich 1000-01-01 bis 9999-12-31. Datumsangaben müssen immer als Dreiergruppe aus Jahr, Monat und Tag eingegeben werden, aber das Format der Eingabe kann variieren, wie in den folgenden Beispielen gezeigt wird:

YYYY-MM-DD oder YY-MM-DD

Es ist optional ob du zweistellige oder vierstellige Jahreszahlen angibst. Wir empfehlen dir dringend, die vierstellige Version zu verwenden, um Verwirrung über das Jahrhundert zu vermeiden. Wenn du die zweistellige Version verwendest, wirst du in der Praxis feststellen, dass 70 bis 99 als 1970 bis 1999 interpretiert werden und 00 bis 69 als 2000 bis 2069.

YYYY/MM/DD, YYYY:MM:DD, YY-MM-DD, oder andere Formate mit Satzzeichen

MySQL erlaubt beliebige Interpunktionszeichen, um die Komponenten eines Datums zu trennen. Wir empfehlen, Bindestriche zu verwenden und wiederum zweistellige Jahreszahlen zu vermeiden.

YYYY-M-D, YYYY-MM-D, oder YYYY-M-DD

Wenn Satzzeichen verwendet werden (auch hier ist jedes Satzzeichen erlaubt), können einstellige Tage und Monate als solche angegeben werden. Zum Beispiel kann der 2. Februar 2006 als 2006-2-2 angegeben werden. Die zweistelligen Jahresangaben sind verfügbar, werden aber nicht empfohlen.

YYYYMMDD oder YYMMDD

Satzzeichen können in beiden Datumsstilen weggelassen werden, aber die Ziffernfolgen müssen sechs oder acht Ziffern lang sein.

Du kannst auch ein Datum eingeben, indem du sowohl ein Datum als auch eine Uhrzeit in den Formaten angibst, die später für DATETIME und TIMESTAMP beschrieben werden, aber nur die Datumskomponente wird in einer Spalte DATE gespeichert. Unabhängig von der Eingabeart ist die Speicherung und Anzeige immer YYYY-MM-DD. Das Null-Datum 0000-00-00 ist in allen Versionen erlaubt und kann verwendet werden, um einen unbekannten oder Dummy-Wert darzustellen. Wenn ein Eingabedatum außerhalb des Bereichs liegt, wird das Null-Datum gespeichert. Allerdings ist dies nur in den MySQL-Versionen bis einschließlich 5.6 standardmäßig möglich. Sowohl 5.7 als auch 8.0 setzen standardmäßig SQL-Modi, die dieses Verhalten verbieten: STRICT_TRANS_TABLES, NO_ZERO_DATE, und NO_ZERO_IN_DATE.

Wenn du eine ältere Version von MySQL verwendest, empfehlen wir dir, diese Modi zu deiner aktuellen Sitzung hinzuzufügen:

mysql> SET sql_mode=CONCAT(@@sql_mode,
    -> ',STRICT_TRANS_TABLES',
    -> ',NO_ZERO_DATE', ',NO_ZERO_IN_DATE');

Tipp

Du kannst die Variable sql_mode auch auf globaler Serverebene und in der Konfigurationsdatei setzen. Diese Variable muss alle Modi auflisten, die du aktivieren möchtest.

Hier sind einige Beispiele für das Einfügen von Daten auf einem MySQL 8.0 Server mit Standardeinstellungen:

mysql> CREATE TABLE testdate (mydate DATE);

Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO testdate VALUES ('2020/02/0');

ERROR 1292 (22007): Incorrect date value: '2020/02/0'
for column 'mydate' at row 1

mysql> INSERT INTO testdate VALUES ('2020/02/1');

Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO testdate VALUES ('2020/02/31');

ERROR 1292 (22007): Incorrect date value: '2020/02/31'
for column 'mydate' at row 1

mysql> INSERT INTO testdate VALUES ('2020/02/100');

ERROR 1292 (22007): Incorrect date value: '2020/02/100'
for column 'mydate' at row 1

Sobald die INSERT Anweisungen ausgeführt wurden, enthält die Tabelle die folgenden Daten:

mysql> SELECT * FROM testdate;

+------------+
| mydate     |
+------------+
| 2020-02-01 |
+------------+
1 row in set (0.00 sec)

MySQL hat dich davor geschützt, "schlechte" Daten in deiner Tabelle zu speichern. Manchmal musst du die eigentliche Eingabe aufbewahren und sie später manuell verarbeiten. Das kannst du tun, indem du die oben genannten SQL-Modi aus der Liste der Modi in der Variable sql_mode entfernst. In diesem Fall würdest du nach der Ausführung der vorherigen INSERT Anweisungen die folgenden Daten erhalten:

mysql> SELECT * FROM testdate;

+------------+
| mydate     |
+------------+
| 2020-02-00 |
| 2020-02-01 |
| 0000-00-00 |
| 0000-00-00 |
+------------+
4 rows in set (0.01 sec)

Beachte auch hier, dass das Datum im YYYY-MM-DD Format angezeigt wird, unabhängig davon, wie es eingegeben wurde.

TIME [fraction]

Speichert eine Zeit im Format HHH:MM:SS für den Bereich -838:59:59 bis 838:59:59. Dies ist nützlich, um die Dauer einer Aktivität zu speichern. Die Werte, die gespeichert werden können, liegen außerhalb des Bereichs der 24-Stunden-Uhr, damit große Unterschiede zwischen den Zeitwerten (bis zu 34 Tage, 22 Stunden, 59 Minuten und 59 Sekunden) berechnet und gespeichert werden können. fraction in TIME und anderen verwandten Datentypen gibt die Genauigkeit der Sekundenbruchteile im Bereich von 0 bis 6 an. Der Standardwert ist 0, was bedeutet, dass keine Sekundenbruchteile erhalten bleiben.

Die Zeiten müssen immer in der Reihenfolge Tage, Stunden, Minuten, Sekunden eingegeben werden, wobei die folgenden Formate verwendet werden:

DD HH:MM:SS[.fraction], HH:MM:SS[.fraction], DD HH:MM, HH:MM, DD HH, oder SS[.fraction]

DD steht für einen ein- oder zweistelligen Wert von Tagen im Bereich von 0 bis 34. Der DD Wert wird vom Stundenwert getrennt, HHdurch ein Leerzeichen getrennt, während die anderen Komponenten durch einen Doppelpunkt getrennt werden. Beachte, dass MM:SS keine gültigeKombination ist, da sie nicht eindeutig von HH:MM. Wenn in der Definition von TIME nichts angegeben ist fraction nicht angegeben oder auf 0 gesetzt wird, führt das Einfügen von Sekundenbruchteilen dazu, dass die Werte auf die nächste Sekunde gerundet werden.

Wenn du zum Beispiel 2 13:25:58.999999 in eine Spalte TIME mit einem fraction von 0 einfügst, wird der Wert 61:25:59 gespeichert, da die Summe von 2 Tagen (48 Stunden) und 13 Stunden 61 Stunden beträgt. Ab MySQL 5.7 verbietet der standardmäßig eingestellte SQL-Modus das Einfügen von falschen Werten. Es ist jedoch möglich, das ältere Verhalten zu aktivieren. Wenn du dann versuchst, einen Wert einzufügen, der außerhalb des zulässigen Bereichs liegt, wird eine Warnung ausgegeben und der Wert wird auf die maximal verfügbare Zeit begrenzt. Wenn du versuchst, einen ungültigen Wert einzufügen, wird ebenfalls eine Warnung ausgegeben und der Wert auf Null gesetzt. Du kannst den Befehl SHOW WARNINGS verwenden, um die Details der Warnung zu melden, die durch die vorherige SQL-Anweisung erzeugt wurde. Wir empfehlen, den standardmäßigen Strict-SQL-Modus beizubehalten. Anders als beim Typ DATE scheint es keinen Vorteil zu haben, falsche TIME Einträge zuzulassen, abgesehen von einem einfacheren Fehlermanagement auf der Anwendungsseite und der Beibehaltung von Legacy-Verhaltensweisen.

Lass uns das alles in der Praxis ausprobieren:

mysql> CREATE TABLE test_time(id SMALLINT, mytime TIME);

Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO test_time VALUES(1, "2 13:25:59");

Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO test_time VALUES(2, "35 13:25:59");

ERROR 1292 (22007): Incorrect time value: '35 13:25:59'
for column 'mytime' at row 1

mysql> INSERT INTO test_time VALUES(3, "900.32");

Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM test_time;

+------+----------+
| id   | mytime   |
+------+----------+
|    1 | 61:25:59 |
|    3 | 00:09:00 |
+------+----------+
2 rows in set (0.00 sec)

H:M:Ssowie ein-, zwei- und dreistellige Kombinationen

Du kannst verschiedene Kombinationen von Ziffern verwenden, wenn du Daten einfügst oder aktualisierst; MySQL wandelt sie in das interne Zeitformat um und zeigt sie einheitlich an. Zum Beispiel ist 1:1:3 gleichbedeutend mit 01:01:03. VerschiedeneZiffernzahlen können gemischt werden; zum Beispiel ist 1:12:3 gleichbedeutend mit 01:12:03. Betrachte diese Beispiele:

mysql> CREATE TABLE mytime (testtime TIME);

Query OK, 0 rows affected (0.12 sec)

mysql> INSERT INTO mytime VALUES
    -> ('-1:1:1'), ('1:1:1'),
    -> ('1:23:45'), ('123:4:5'),
    -> ('123:45:6'), ('-123:45:6');

Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM mytime;

+------------+
| testtime   |
+------------+
|  -01:01:01 |
|   01:01:01 |
|   01:23:45 |
|  123:04:05 |
|  123:45:06 |
| -123:45:06 |
+------------+
5 rows in set (0.01 sec)

Beachte, dass die Stunden mit zwei Ziffern für Werte im Bereich von -99 bis 99 angezeigt werden.

HHMMSS, MMSSund SS

Satzzeichen können weggelassen werden, aber die Ziffernfolgen müssen zwei, vier oder sechs Ziffern lang sein. Beachte, dass das äußerste rechte Ziffernpaar immer als ein SS (Sekunden) interpretiert wird, das zweite Paar ganz rechts (falls vorhanden) als MM (Minuten) und das dritte Paar ganz rechts (falls vorhanden) als HH (Stunden). Das bedeutet, dass ein Wert wie 1222 als 12 Minuten und 22 Sekunden und nicht als 12 Stunden und 22 Minuten interpretiert wird.

Du kannst auch eine Uhrzeit eingeben, indem du sowohl ein Datum als auch eine Uhrzeit in den für DATETIME und TIMESTAMP beschriebenen Formaten angibst, aber nur die Zeitkomponente wird in einer Spalte TIME gespeichert. Unabhängig von der Eingabeart ist die Speicherung und Anzeige immer HH:MM:SS. Die Nullzeit 00:00:00 kann verwendet werden, um einen unbekannten Wert oder einen Dummy-Wert darzustellen.

TIMESTAMP[(fraction)]

Speichert und zeigt ein Datums- und Zeitpaar im Format YYYY-MM-DD HH:MM:SS[.fraction][time zone offset] für den Bereich 1970-01-01 00:00:01.000000 bis 2038-01-19 03:14:07.999999. Dieser Typ ist dem Typ DATETIME sehr ähnlich, aber es gibt ein paar Unterschiede. Beide Typen akzeptieren einen Zeitzonenmodifikator für den Eingabewert MySQL 8.0, und beide Typen speichern und präsentieren die Daten auf dieselbe Weise für jeden Client in derselben Zeitzone. Allerdings werden die Werte in den TIMESTAMP Spalten intern immer in der UTC-Zeitzone gespeichert, so dass es möglich ist, für Clients in verschiedenen Zeitzonen automatisch eine lokale Zeitzone zu erhalten. Das allein ist schon ein sehr wichtiger Unterschied, an den du dich erinnern solltest. TIMESTAMP ist beim Umgang mit verschiedenen Zeitzonen bequemer zu verwenden.

Vor MySQL 5.6 unterstützte nur der Typ TIMESTAMP die automatische Initialisierung und Aktualisierung. Außerdem konnte nur eine einzige solche Spalte in einer bestimmten Tabelle dies tun. Ab 5.6 unterstützen sowohl TIMESTAMP als auch DATETIME dieses Verhalten und eine beliebige Anzahl von Spalten kann dies tun.

Die in einer TIMESTAMP Spalte gespeicherten Werte entsprechen immer der Vorlage YYYY-MM-DD HH:MM:SS[.fraction][time zone offset]aber die Werte können in einer Vielzahl von Formaten angegeben werden:

YYYY-MM-DD HH:MM:SS oder YY-MM-DD HH:MM:SS

Für die Datums- und Zeitkomponenten gelten dieselben lockeren Beschränkungen wie für die Komponenten DATE und TIME, die zuvor beschrieben wurden. Das bedeutet, dass alle Interpunktionszeichen erlaubt sind, einschließlich (anders als bei TIME) der Flexibilität bei der Interpunktion in der Zeitkomponente. Zum Beispiel ist –0— gültig.

YYYYMMDDHHMMSS oder YYMMDDHHMMSS

Satzzeichen können weggelassen werden, aber die Zeichenfolge sollte entweder 12 oder 14 Ziffern lang sein. Wir empfehlen, nur die eindeutige 14-stellige Version zu verwenden, und zwar aus denselben Gründen, die für den Typ DATE erläutert wurden. Du kannst auch Werte mit anderen Längen angeben, ohne Trennzeichen zu verwenden, aber wir empfehlen dies nicht.

Schauen wir uns die Funktion der automatischen Aktualisierung genauer an. Du steuerst dies, indem du die folgenden Attribute zur Spaltendefinition hinzufügst, wenn du eine Tabelle erstellst, oder später, wie wir in "Strukturen ändern" erklären werden :

1. Wenn du möchtest, dass der Zeitstempel nur gesetzt wird, wenn eine neue Zeile in die Tabelle eingefügt wird, füge DEFAULT CURRENT_TIMESTAMP am Ende der Spaltendeklaration hinzu.

2. Wenn du keinen Standard-Zeitstempel möchtest, sondern die aktuelle Zeit von verwendet werden soll, wenn die Daten in einer Zeile aktualisiert werden, füge ON UPDATE CURRENT_TIMESTAMP am Ende der Spaltendeklaration hinzu.

3. Wenn du beides willst, d.h. wenn du willst, dass der Zeitstempel in jeder neuen Zeile und bei jeder Änderung einer bestehenden Zeile auf die aktuelle Zeit gesetzt wird, füge DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP am Ende der Spaltendeklaration hinzu.

Wenn du für eine Spalte TIMESTAMP weder DEFAULT NULL noch NULL angibst, hat sie 0 als Standardwert.

YEAR[(4)]

Speichert eine vierstellige Jahreszahl im Bereich 1901 bis 2155 sowie die Jahreszahl Null, 0000. Ungültige Werte werden in die Jahreszahl Null umgewandelt. Du kannst Jahreszahlen entweder als Zeichenketten (wie '2005') oder als ganze Zahlen (wie 2005) eingeben. Der Typ YEAR benötigt 1 Byte Speicherplatz.

In früheren Versionen von MySQL war es möglich, den Parameter anzugeben. digits entweder 2 oder 4 zu übergeben. Die zweistellige Version speicherte Werte von 70 bis 69, was 1970 bis 2069 entspricht. MySQL 8.0 unterstützt den zweistelligen YEAR Typ nicht, und die Angabe des digits Parameter für Anzeigezwecke ist veraltet.

DATETIME[(fraction)]

Speichert und zeigt ein Datums- und Zeitpaar im Format YYYY-MM-DD HH:MM:SS[.fraction][time zone offset] für den Bereich 1000-01-01 00:00:00 bis 9999-12-31 23:59:59. Wie bei TIMESTAMP entspricht der gespeicherte Wert immer der Vorlage YYYY-MM-DD HH:MM:SS, aber der Wert kann in denselben Formaten eingegeben werden, die in der Beschreibung von TIMESTAMP aufgeführt sind. Wenn du einer DATETIME Spalte nur ein Datum zuweist, wird die Nullzeit 00:00:00 angenommen. Wenn du einer DATETIME Spalte nur eine Uhrzeit zuweist, wird das Datum 0000-00-00 als Null angenommen. Dieser Typ hat die gleichen automatischen Aktualisierungsfunktionen wie TIMESTAMP. Wenn das Attribut NOT NULL nicht für eine DATETIME Spalte angegeben wird, ist ein NULL Wert der Standardwert; andernfalls ist der Standardwert 0. Anders als bei TIMESTAMP werden die Werte von DATETIME für dieSpeicherung nicht in die UTC-Zeitzone umgewandelt.

Andere Typen

Seit MySQL 8.0 fallen die Datentypen "Spatial" und " JSON " ( ) unter diese allgemeine Kategorie. Die Verwendung dieser Datentypen ist ein ziemlich fortgeschrittenes Thema, das wir hier nicht näher behandeln werden.

Räumliche Datentypen befassen sich mit der Speicherung geometrischer Objekte, und MySQL hat Typen, die den OpenGIS-Klassen entsprechen. Die Arbeit mit diesen Typen ist ein Thema, das ein eigenes Buch wert ist.

Der Datentyp JSON ermöglicht die native Speicherung von gültigen JSON-Dokumenten. Vor MySQL 5.7 wurde JSON normalerweise in einer TEXT oder einer ähnlichen Spalte gespeichert. Das hat jedoch viele Nachteile: Zum Beispiel werden die Dokumente nicht validiert und die Speicherung wird nicht optimiert (alle JSON-Dokumente werden einfach in ihrer Textform gespeichert). Mit dem nativen Typ JSON wird es im Binärformat gespeichert. Um es in einem Satz zusammenzufassen: Verwende den Datentyp JSON für JSON, lieber Leser.

Schlüssel und Indizes

Du wirst feststellen, dass fast alle Tabellen, die du verwendest, eine PRIMARY KEY Klausel in ihrer CREATE TABLE Anweisung haben, und manchmal auch mehrere KEY Klauseln. Die Gründe, warum du einen Primärschlüssel und Sekundärschlüssel brauchst, wurden in Kapitel 2 erläutert. In diesem Abschnitt wird erläutert, wie Primärschlüssel deklariert werden, was dabei hinter den Kulissen passiert und warum du eventuell auch andere Schlüssel und Indizes für deine Daten erstellen solltest.

Ein Primärschlüssel identifiziert jede Zeile in einer Tabelle eindeutig. Noch wichtiger ist, dass bei der standardmäßigen InnoDB Speicherung ein Primärschlüssel auch als geclusterter Index verwendet wird. Das bedeutet, dass alle eigentlichen Tabellendaten in einer Indexstruktur gespeichert sind. Das ist ein Unterschied zu MyISAM, das Daten und Indizes getrennt speichert. Wenn eine Tabelle einen geclusterten Index verwendet, nennt man sie eine geclusterte Tabelle. Wie bereits erwähnt, wird in einer geclusterten Tabelle jede Zeile in einem Index gespeichert, im Gegensatz zu einem Heap, wie er normalerweise genannt wird. Das Clustern einer Tabelle führt dazu, dass die Zeilen nach der Reihenfolge des Cluster-Indexes sortiert werden und tatsächlich in den Blattseiten dieses Indexes gespeichert werden. Es kann nicht mehr als einen geclusterten Index pro Tabelle geben. Bei solchen Tabellen beziehen sich die sekundären Indizes auf die Datensätze im geclusterten Index und nicht auf die eigentlichen Tabellenzeilen. Das führt in der Regel zu einer verbesserten Abfrageleistung, kann sich aber nachteilig auf die Schreibvorgänge auswirken. InnoDB erlaubt es dir nicht, zwischen geclusterten und nicht geclusterten Tabellen zu wählen; dies ist eine Designentscheidung, die du nicht ändern kannst.

Primärschlüssel sind im Allgemeinen ein empfohlener Teil jedes Datenbankdesigns, aber für InnoDB sind sie notwendig. Wenn du bei der Erstellung einer InnoDB-Tabelle keine PRIMARY KEY Klausel angibst, verwendet MySQL die erste UNIQUE NOT NULL Spalte als Basis für den Clustered Index. Wenn keine solche Spalte vorhanden ist, wird ein versteckter Cluster-Index erstellt, der auf den ID-Werten basiert, die InnoDB jeder Zeile zuweist.

Da InnoDB die Standard-Speicher-Engine von MySQL ist und heutzutage ein De-facto-Standard ist, werden wir uns in diesem Kapitel auf sein Verhalten konzentrieren. Alternative Speicher-Engines wie MyISAM, MEMORY oder MyRocks werden im Abschnitt "Alternative Speicher-Engines" behandelt .

Wie bereits erwähnt, wird ein Primärschlüssel zu einem geclusterten Index, und alle Daten der Tabelle werden in den Blattblöcken dieses Indexes gespeichert. InnoDB verwendet B-Baum-Indizes (genauer gesagt, die B+Baum-Variante), mit Ausnahme von Indizes für räumliche Datentypen, die die R-Baum-Struktur verwenden. Andere Speicher-Engines können andere Indextypen implementieren, aber wenn die Speicher-Engine einer Tabelle nicht angegeben ist, kannst du davon ausgehen, dass alle Indizes B-Bäume sind.

Mit einem geclusterten Index, oder anders gesagt, mit indexorganisierten Tabellen, werden Abfragen und Sortierungen der Primärschlüsselspalten beschleunigt. Ein Nachteil ist jedoch, dass das Ändern von Spalten in einem Primärschlüssel teuer ist. Ein gutes Design erfordert daher einen Primärschlüssel, der auf Spalten basiert, die häufig zum Filtern in Abfragen verwendet, aber selten geändert werden. Denke daran, dass ein fehlender Primärschlüssel dazu führt, dass InnoDB einen impliziten Cluster-Index verwendet. Wenn du dir also nicht sicher bist, welche Spalten du für einen Primärschlüssel auswählen sollst, kannst du eine synthetische id-ähnliche Spalte verwenden. In diesem Fall könnte zum Beispiel der Datentyp SERIAL gut geeignet sein.

Von den internen Details von InnoDB einmal abgesehen: Wenn du einen Primärschlüssel für eine Tabelle in MySQL deklarierst, wird eine Struktur erstellt, die Informationen darüber speichert, wo die Daten jeder Zeile in der Tabelle gespeichert sind. Diese Informationen werden als Index bezeichnet und dienen dazu, die Suche nach dem Primärschlüssel zu beschleunigen. Wenn du zum Beispiel PRIMARY KEY (actor_id) in der Tabelle actor in der Datenbank sakila deklarierst, erstellt MySQL eine Struktur, die es ihm ermöglicht, Zeilen, die mit einem bestimmten actor_id (oder einer Reihe von Bezeichnern) übereinstimmen, extrem schnell zu finden.

Das ist nützlich, um z. B. Schauspieler mit Filmen oder Filme mit Kategorien zu verbinden. Du kannst die in einer Tabelle verfügbaren Indizes mit demBefehl SHOW INDEX (oder SHOW INDEXES) anzeigen:

mysql> SHOW INDEX FROM category\G

*************************** 1. row ***************************
        Table: category
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: category_id
    Collation: A
  Cardinality: 16
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
      Visible: YES
   Expression: NULL
1 row in set (0.00 sec)

Die Kardinalität ist die Anzahl der eindeutigen Werte im Index; für einen Index auf einem Primärschlüssel ist dies die gleiche wie die Anzahl der Zeilen in der Tabelle.

Beachte, dass alle Spalten, die Teil eines Primärschlüssels sind, als NOT NULL deklariert sein müssen, da sie einen Wert haben müssen, damit die Zeile gültig ist. Ohne den Index besteht die einzige Möglichkeit, Zeilen in der Tabelle zu finden, darin, jede einzelne Zeile von der Festplatte zu lesen und zu prüfen, ob sie mit der category_id übereinstimmt, nach der du suchst. Bei Tabellen mit vielen Zeilen ist diese erschöpfende, sequentielle Suche extrem langsam. Du kannst aber nicht einfach alles indizieren; darauf kommen wir am Ende dieses Abschnitts zurück.

Du kannst weitere Indizes für die Daten in einer Tabelle erstellen. Das tust du, damit andere Suchvorgänge (ob auf anderen Spalten oder Kombinationen von Spalten) schnell sind und um sequenzielle Scans zu vermeiden. Nehmen wir zum Beispiel wieder die Tabelle actor. Sie hat nicht nur einenPrimärschlüssel für actor_id, sondern auch einen Sekundärschlüssel für last_name, um die Suche nach dem Nachnamen eines Schauspielers zu verbessern:

mysql> SHOW CREATE TABLE actor\G

*************************** 1. row ***************************
       Table: actor
Create Table: CREATE TABLE `actor` (
  `actor_id` smallint unsigned NOT NULL AUTO_INCREMENT,
  ...
  `last_name` varchar(45) NOT NULL,
  ...
  PRIMARY KEY (`actor_id`),
  KEY `idx_actor_last_name` (`last_name`)
) ...
1 row in set (0.00 sec)

Du kannst sehen, dass das Schlüsselwort KEY verwendet wird, um MySQL mitzuteilen, dass ein zusätzlicher Index benötigt wird. Alternativ kannst du auch das Wort INDEX anstelle von KEY verwenden. Nach diesem Schlüsselwort folgt ein Indexname, und dann wird die Spalte, die indiziert werden soll, in Klammern gesetzt. Du kannst auch Indizes hinzufügen, nachdem die Tabellen erstellt wurden - du kannst so ziemlich alles an einer Tabelle ändern, nachdem sie erstellt wurde. Das wird im Abschnitt "Strukturen ändern" behandelt .

Du kannst einen Index für mehr als eine Spalte erstellen. Betrachte zum Beispiel die folgende Tabelle, die eine modifizierte Tabelle von sakila ist:

mysql> CREATE TABLE customer_mod (
    -> customer_id smallint unsigned NOT NULL AUTO_INCREMENT,
    -> first_name varchar(45) NOT NULL,
    -> last_name varchar(45) NOT NULL,
    -> email varchar(50) DEFAULT NULL,
    -> PRIMARY KEY (customer_id),
    -> KEY idx_names_email (first_name, last_name, email));

Query OK, 0 rows affected (0.06 sec)

Wie du siehst, haben wir einen Primärschlüsselindex für die Identifizierungsspalte customer_id und einen weiteren Index - idx_names_email- hinzugefügt, der die Spalten first_name, last_name und email in dieser Reihenfolge umfasst. Überlegen wir nun, wie du diesen zusätzlichen Index verwenden kannst.

Du kannst den Index idx_names_email für die schnelle Suche nach Kombinationen der drei Namensspalten verwenden. Er ist zum Beispiel in der folgenden Abfrage nützlich:

mysql> SELECT * FROM customer_mod WHERE
    -> first_name = 'Rose' AND
    -> last_name = 'Williams' AND
    -> email = '[e-mail]';

Wir wissen, dass es bei der Suche hilft, weil alle im Index aufgeführten Spalten in der Abfrage verwendet werden. Mit der Anweisung EXPLAIN kannst du überprüfen, ob das, was du glaubst, dass es passieren sollte, auch tatsächlich passiert:

mysql> EXPLAIN SELECT * FROM customer_mod WHERE
    -> first_name = 'Rose' AND
    -> last_name = 'Williams' AND
    -> email = '[e-mail]'\G

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: customer_mod
   partitions: NULL
         type: ref
possible_keys: idx_names_email
          key: idx_names_email
      key_len: 567
          ref: const,const,const
         rows: 1
     filtered: 100.00
        Extra: Using index
1 row in set, 1 warning (0.00 sec)

Wie du siehst, meldet MySQL, dass die possible_keys idx_names_email sind (was bedeutet, dass der Index für diese Abfrage verwendet werden könnte) und dass die key, für die er sich entschieden hat, idx_names_email ist. Was du erwartest und was passiert, ist also dasselbe, und das ist eine gute Nachricht! Mehr über die Anweisung EXPLAIN erfährst du in Kapitel 7.

Der Index, den wir erstellt haben, ist auch nützlich für Abfragen, die nur die Spalte first_name betreffen. Er kann zum Beispiel von der folgenden Abfrage verwendet werden:

mysql> SELECT * FROM customer_mod WHERE
    -> first_name = 'Rose';

Du kannst EXPLAIN erneut verwenden, um zu prüfen, ob der Index verwendet wird. Der Grund dafür, dass er verwendet werden kann, ist, dass die Spalte first_name die erste im Index ist. In der Praxis bedeutet das, dass der Index die Informationen über die Zeilen aller Personen mit demselben Vornamen bündelt bzw. speichert und somit alle Personen mit demselben Vornamen finden kann.

Der Index kann auch für die Suche nach Kombinationen aus Vor- und Nachnamen verwendet werden, und zwar aus genau denselben Gründen, die wir gerade besprochen haben. Der Index fasst Personen mit demselben Vornamen zusammen und gruppiert Personen mit identischen Vornamen nach dem Nachnamen. Er kann also für diese Abfrage verwendet werden:

mysql> SELECT * FROM customer_mod WHERE
    -> first_name = 'Rose' AND
    -> last_name = 'Williams';

Der Index kann jedoch nicht für diese Abfrage verwendet werden, weil die Spalte ganz links im Index, first_name, nicht in der Abfrage vorkommt:

mysql> SELECT * FROM customer_mod WHERE
    -> last_name = 'Williams' AND
    -> email = '[e-mail]';

Der Index soll helfen, die Menge der Zeilen auf eine kleinere Menge möglicher Antworten einzugrenzen. Damit MySQL einen Index verwenden kann, muss die Abfrage die folgenden beiden Bedingungen erfüllen:

1. Die Spalte ganz links in der KEY (oder PRIMARY KEY) Klausel muss in der Abfrage enthalten sein.

2. Die Abfrage darf keine OR Klauseln für Spalten enthalten, die nicht indiziert sind.

Auch hier kannst du die EXPLAIN Anweisung verwenden, um zu prüfen, ob ein Index für eine bestimmte Abfrage verwendet werden kann.

Bevor wir diesen Abschnitt beenden, hier noch ein paar Ideen, wie du Indizes auswählen und gestalten kannst. Wenn du überlegst, einen Index einzufügen, solltest du Folgendes bedenken:

• Indizes kosten Speicherplatz auf der Festplatte und müssen bei jeder Datenänderung aktualisiert werden. Wenn sich deine Daten häufig ändern oder sich viele Daten ändern, wenn du eine Änderung vornimmst, verlangsamen Indizes den Prozess. In der Praxis sind Indizes jedoch in der Regel von Vorteil, da SELECT Anweisungen (Datenlesen) viel häufiger vorkommen als andere Anweisungen (Datenänderungen).

• Füge nur einen Index hinzu, der häufig genutzt werden wird. Mach dir nicht die Mühe, Spalten zu indizieren, bevor du siehst, welche Abfragen deine Nutzer und deine Anwendungen benötigen. Du kannst nachträglich immer noch Indizes hinzufügen.

• Wenn alle Spalten in einem Index in allen Abfragen verwendet werden, führe die Spalte mit der höchsten Anzahl an Duplikaten links in der KEY Klausel auf. Dadurch wird die Indexgröße minimiert.

• Je kleiner der Index ist, desto schneller wird er sein. Wenn du große Spalten indizierst, bekommst du einen größeren Index. Das ist ein guter Grund, um sicherzustellen, dass deine Spalten so klein wie möglich sind, wenn du deine Tabellen entwirfst.

• Bei langen Spalten kannst du nur ein Präfix der Werte aus einer Spalte verwenden, um den Index zu erstellen. Dazu fügst du nach der Spaltendefinition einen Wert in Klammern hinzu, z. B. KEY idx_names_email (first_name(3), last_name(2), email(10)). Das bedeutet, dass nur die ersten 3 Zeichen von first_name indexiert werden, dann die ersten 2 Zeichen von last_name und dann 10 Zeichen von email. Das ist eine erhebliche Ersparnis gegenüber der Indexierung von 140 Zeichen aus den drei Spalten! Wenn du so vorgehst, ist dein Index weniger in der Lage, Zeilen eindeutig zu identifizieren, aber er ist viel kleiner und immer noch recht gut darin, passende Zeilen zu finden. Die Verwendung eines Präfixes ist für lange Typen wie TEXT obligatorisch.

Zum Abschluss dieses Abschnitts müssen wir noch einige Besonderheiten in Bezug auf Sekundärschlüssel in InnoDB besprechen. Erinnere dich daran, dass alle Tabellendaten in den Blättern des geclusterten Index gespeichert sind. actor Das bedeutet, dass wir auf die Daten des Primärschlüssels zugreifen müssen, wenn wir die Daten von first_name benötigen, um nach last_name zu filtern, auch wenn wir idx_actor_last_name für eine schnelle Filterung verwenden können. Folglich werden bei jedem Sekundärschlüssel in InnoDB alle Primärschlüsselspalten implizit an seine Definition angehängt. Unnötig lange Primärschlüssel in InnoDB führen daher zu deutlich aufgeblähten Sekundärschlüsseln.

Das kann man auch in der Ausgabe von EXPLAIN sehen (beachte die Extra: Using index in der ersten Ausgabe des ersten Befehls):

mysql> EXPLAIN SELECT actor_id, last_name FROM actor WHERE last_name = 'Smith'\G

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: actor
   partitions: NULL
         type: ref
possible_keys: idx_actor_last_name
          key: idx_actor_last_name
      key_len: 182
          ref: const
         rows: 1
     filtered: 100.00
        Extra: Using index
1 row in set, 1 warning (0.00 sec)

mysql> EXPLAIN SELECT first_name FROM actor WHERE last_name = 'Smith'\G

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: actor
   partitions: NULL
         type: ref
possible_keys: idx_actor_last_name
          key: idx_actor_last_name
      key_len: 182
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

Tatsächlich ist idx_actor_last_name ein abdeckender Index für die erste Abfrage, was bedeutet, dass InnoDB alle benötigten Daten allein aus diesem Index extrahieren kann. Für die zweite Abfrage muss InnoDB jedoch einen zusätzlichen Lookup eines geclusterten Indexes durchführen, um den Wert für die Spalte first_name zu erhalten.

Die AUTO_INCREMENT-Funktion

Mit der MySQL-eigenen Funktion AUTO_INCREMENT kannst du einen eindeutigen Bezeichner für eine Zeile erstellen, ohne eine SELECT Abfrage zu starten. So funktioniert es. Nehmen wir wieder die vereinfachte Tabelle actor:

mysql> CREATE TABLE actor (
    -> actor_id smallint unsigned NOT NULL AUTO_INCREMENT,
    -> first_name varchar(45) NOT NULL,
    -> last_name varchar(45) NOT NULL,
    -> PRIMARY KEY (actor_id)
    -> );

Query OK, 0 rows affected (0.03 sec)

Es ist möglich, Zeilen in diese Tabelle einzufügen, ohne die actor_id anzugeben:

mysql> INSERT INTO actor VALUES (NULL, 'Alexander', 'Kaidanovsky');

Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO actor VALUES (NULL, 'Anatoly', 'Solonitsyn');

Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO actor VALUES (NULL, 'Nikolai', 'Grinko');

Query OK, 1 row affected (0.00 sec)

Wenn du dir die Daten in dieser Tabelle ansiehst, kannst du sehen, dass jeder Zeile ein Wert für die Spalte actor_id zugewiesen ist:

mysql> SELECT * FROM actor;

+----------+------------+-------------+
| actor_id | first_name | last_name   |
+----------+------------+-------------+
|        1 | Alexander  | Kaidanovsky |
|        2 | Anatoly    | Solonitsyn  |
|        3 | Nikolai    | Grinko      |
+----------+------------+-------------+
3 rows in set (0.00 sec)

Jedes Mal, wenn eine neue Zeile eingefügt wird, wird ein eindeutiger Wert für die Spalte actor_id für diese neue Zeile erstellt.

Schau dir an, wie diese Funktion funktioniert. Wie du siehst, wird die Spalte actor_id mit den Klauseln NOT NULL AUTO_INCREMENT als Ganzzahl deklariert. AUTO_INCREMENT teilt MySQL mit, dass, wenn kein Wert für diese Spalte angegeben wird, der zugewiesene Wert um eins höher sein soll als der derzeit in der Tabelle gespeicherte Höchstwert. Die Folge AUTO_INCREMENT beginnt bei einer leeren Tabelle mit 1.

Die NOT NULL Klausel ist für AUTO_INCREMENT Spalten erforderlich; wenn du NULL einfügst (oder 0, obwohl das nicht empfohlen wird), findet der MySQL-Server automatisch den nächsten verfügbaren Bezeichner und weist ihn der neuen Zeile zu. Du kannst manuell negative Werte einfügen, wenn die Spalte nicht als UNSIGNED definiert wurde; bei der nächsten automatischen Erhöhung verwendet MySQL jedoch einfach den größten (positiven) Wert in der Spalte oder beginnt bei 1, wenn es keine positiven Werte gibt.

Die Funktion AUTO_INCREMENT hat die folgenden Voraussetzungen:

• Die Spalte, für die sie verwendet wird, muss indiziert sein.

• Die Spalte, für die sie verwendet wird, darf keinen DEFAULT Wert haben.

• Es kann nur eine AUTO_INCREMENT Spalte pro Tabelle geben.

MySQL unterstützt verschiedene Speicher-Engines, über die wir im Abschnitt "Alternative Speicher-Engines" mehr erfahren . Wenn du den nicht standardmäßigen MyISAM-Tabellentyp verwendest, kannst du die Funktion AUTO_INCREMENT für Schlüssel verwenden, die aus mehreren Spalten bestehen. Du kannst also mehrere unabhängige Zähler in einer einzigen AUTO_INCREMENT Spalte haben. Mit InnoDB ist dies jedoch nicht möglich.

Die Funktion AUTO_INCREMENT ist zwar nützlich, aber nicht auf andere Datenbankumgebungen übertragbar, und sie verbirgt die logischen Schritte zur Erstellung neuer Bezeichner. Sie kann auch zu Unklarheiten führen: Wenn du zum Beispiel eine Tabelle löschst oder abschneidest, wird der Zähler zurückgesetzt, aber wenn du ausgewählte Zeilen löschst (mit einer WHERE Klausel), wird der Zähler nicht zurückgesetzt. Wenn eine Zeile innerhalb einer Transaktion eingefügt wird und diese Transaktion dann zurückgenommen wird, wird der Bezeichner trotzdem verbraucht. Als Beispiel erstellen wir die Tabelle count, die ein automatisch inkrementierendes Feld counter enthält:

mysql> CREATE TABLE count (counter INT AUTO_INCREMENT KEY);

Query OK, 0 rows affected (0.13 sec)

mysql> INSERT INTO count VALUES (),(),(),(),(),();

Query OK, 6 rows affected (0.01 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM count;

+---------+
| counter |
+---------+
| 1       |
| 2       |
| 3       |
| 4       |
| 5       |
| 6       |
+---------+
6 rows in set (0.00 sec)

Das Einfügen mehrerer Werte funktioniert wie erwartet. Jetzt wollen wir ein paar Zeilen löschen und dann sechs neue Zeilen hinzufügen:

mysql> DELETE FROM count WHERE counter > 4;

Query OK, 2 rows affected (0.00 sec)

mysql> INSERT INTO count VALUES (),(),(),(),(),();

Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM count;

+---------+
| counter |
+---------+
| 1       |
| 2       |
| 3       |
| 4       |
| 7       |
| 8       |
| 9       |
| 10      |
| 11      |
| 12      |
+---------+
10 rows in set (0.00 sec)

Hier sehen wir, dass der Zähler nicht zurückgesetzt wird und bei 7 weiterläuft. Wenn wir jedoch die Tabelle abschneiden, also alle Daten entfernen, wird der Zähler auf 1 zurückgesetzt:

mysql> TRUNCATE TABLE count;

Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO count VALUES (),(),(),(),(),();

Query OK, 6 rows affected (0.01 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM count;

+---------+
| counter |
+---------+
| 1       |
| 2       |
| 3       |
| 4       |
| 5       |
| 6       |
+---------+
6 rows in set (0.00 sec)

Zusammengefasst: AUTO_INCREMENT garantiert eine Abfolge von transaktionalen und monoton ansteigenden Werten. Es garantiert jedoch in keiner Weise, dass jeder einzelne angegebene Bezeichner genau auf den vorherigen folgt. Normalerweise ist dieses Verhalten von AUTO_INCREMENT klar genug und sollte kein Problem darstellen. Wenn dein spezieller Anwendungsfall jedoch einen Zähler erfordert, der garantiert keine Lücken aufweist, solltest du eine Umgehungslösung in Betracht ziehen. Leider wird diese wahrscheinlich auf der Anwendungsseite implementiert werden.

Strukturen verändern

Wir haben dir alle Grundlagen gezeigt, die du zum Erstellen von Datenbanken, Tabellen, Indizes und Spalten brauchst. In diesem Abschnitt lernst du, wie du Spalten, Datenbanken, Tabellen und Indizes in bereits bestehenden Strukturen hinzufügst, entfernst und änderst.

Hinzufügen, Entfernen und Ändern von Spalten

Mit der Anweisung ALTER TABLE kannst du neue Spalten zu einer Tabelle hinzufügen, vorhandene Spalten entfernen und Spaltennamen, -typen und -längen ändern.

Beginnen wir mit der Frage, wie du bestehende Spalten änderst. Nehmen wir ein Beispiel, in dem wir eine Tabellenspalte umbenennen. Die Tabelle language hat eine Spalte mit dem Namen last_update, die den Zeitpunkt enthält, zu dem der Datensatz geändert wurde. Um den Namen dieser Spalte in last_updated_time zu ändern, würdest du schreiben:

mysql> ALTER TABLE language RENAME COLUMN last_update TO last_updated_time;

Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

In diesem Beispiel wird die Online-DDL-Funktion von MySQL verwendet. Was tatsächlich hinter den Kulissen passiert, ist, dass MySQL nur die Metadaten ändert und die Tabelle nicht wirklich neu schreiben muss. Das kannst du daran erkennen, dass keine Zeilen betroffen sind. Nicht alle DDL-Anweisungen können online ausgeführt werden, daher wird dies bei vielen Änderungen, die du vornimmst, nicht der Fall sein.

Hinweis

DDL steht für Data Definition Language und ist im Kontext von SQL eine Untergruppe von Syntax und Anweisungen, die zum Erstellen, Ändern und Löschen von Schemaobjekten wie Datenbanken, Tabellen, Indizes und Spalten verwendet werden. CREATE TABLE und ALTER TABLE sind zum Beispiel beide DDL-Operationen.

Die Ausführung von DDL-Anweisungen erfordert spezielle interne Mechanismen, einschließlich spezieller Sperren - und das ist auch gut so, denn du möchtest wahrscheinlich nicht, dass sich Tabellen ändern, während deine Abfragen laufen! Diese speziellen Sperren werden in MySQL Metadaten-Sperren genannt. Wir geben einen detaillierten Überblick darüber, wie sie funktionieren, in "Metadaten-Sperren".

Beachte, dass alle DDL-Anweisungen, einschließlich derjenigen, die über Online-DDL ausgeführt werden, Metadaten-Sperren erfordern, um sie zu erhalten. In diesem Sinne sind Online-DDL-Anweisungen nicht so "online", aber sie sperren die Zieltabelle nicht vollständig, während sie ausgeführt werden. Die Ausführung von DDL-Anweisungen auf einem laufenden System unter Last ist ein riskantes Unterfangen: Selbst eine Anweisung, die fast sofort ausgeführt werden sollte, kann zu einem Chaos führen. Wir empfehlen, dass du dich in Kapitel 6 und im Link zur MySQL-Dokumentation sorgfältig über das Sperren von Metadaten informierst und verschiedene DDL-Anweisungen mit und ohne gleichzeitige Last ausführst. Das ist vielleicht nicht so wichtig, während du MySQL lernst, aber wir denken, dass es sich lohnt, dich im Voraus zu warnen. Nachdem das geklärt ist, kommen wir zurück zu unserer ALTER der Tabelle language.

Du kannst das Ergebnis mit der Anweisung SHOW COLUMNS überprüfen:

mysql> SHOW COLUMNS FROM language;

+-------------------+------------------+------+-----+-------------------+...
| Field             | Type             | Null | Key | Default           |...
+-------------------+------------------+------+-----+-------------------+...
| language_id       | tinyint unsigned | NO   | PRI | NULL              |...
| name              | char(20)         | NO   |     | NULL              |...
| last_updated_time | timestamp        | NO   |     | CURRENT_TIMESTAMP |...
+-------------------+------------------+------+-----+-------------------+...
3 rows in set (0.01 sec)

Im vorherigen Beispiel haben wir die Anweisung ALTER TABLE mit dem Schlüsselwort RENAME COLUMN verwendet. Das ist eine Funktion von MySQL 8.0. Aus Kompatibilitätsgründen könnten wir alternativ ALTER TABLE mit dem Schlüsselwort CHANGE verwenden:

mysql> ALTER TABLE language CHANGE last_update last_updated_time TIMESTAMP
    -> NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;

Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

In diesem Beispiel kannst du sehen, dass wir der Anweisung ALTER TABLE mit dem Schlüsselwort CHANGE vier Parameter übergeben haben:

1. Der Name der Tabelle, language

2. Der ursprüngliche Spaltenname, last_update

3. Der neue Spaltenname, last_updated_time

4. Der Spaltentyp, TIMESTAMP, mit vielen zusätzlichen Attributen, die notwendig sind, um die ursprüngliche Definition nicht zu verändern

Du musst alle vier angeben, d.h. du musst den Typ und alle dazugehörigen Klauseln neu spezifizieren. Da wir in diesem Beispiel MySQL 8.0 mit den Standardeinstellungen verwenden, hat TIMESTAMP keine expliziten Vorgaben mehr. Wie du siehst, ist die Verwendung von RENAME COLUMN viel einfacher als CHANGE.

Wenn du den Typ und die Klauseln einer Spalte ändern willst, aber nicht ihren Namen, kannst du das Schlüsselwort MODIFY verwenden:

mysql> ALTER TABLE language MODIFY name CHAR(20) DEFAULT 'n/a';

Query OK, 0 rows affected (0.14 sec)
Records: 0  Duplicates: 0  Warnings: 0

Du kannst dies auch mit dem Schlüsselwort CHANGE tun, aber indem du denselben Spaltennamen zweimal angibst:

mysql> ALTER TABLE language CHANGE name name CHAR(20) DEFAULT 'n/a';

Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

Sei vorsichtig, wenn du Typen änderst:

• Verwende keine inkompatiblen Typen, denn du verlässt dich darauf, dass MySQL die Daten erfolgreich von einem Format in ein anderes umwandelt (wenn du zum Beispiel eine INT Spalte in eine DATETIME Spalte umwandelst, wird sie wahrscheinlich nicht das tun, was du dir erhofft hast).

• Schneide die Daten nicht ab, es sei denn, das ist das, was du willst. Wenn du die Größe eines Typs reduzierst, werden die Werte an die neue Breite angepasst, und du kannst Daten verlieren.

Angenommen, du möchtest eine zusätzliche Spalte zu einer bestehenden Tabelle hinzufügen. So machst du es mit der Anweisung ALTER TABLE:

mysql> ALTER TABLE language ADD native_name CHAR(20);

Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

Du musst das Schlüsselwort ADD, den neuen Spaltennamen sowie den Spaltentyp und die Klauseln angeben. In diesem Beispiel wird die neue Spalte native_name als letzte Spalte der Tabelle hinzugefügt, wie in der Anweisung SHOW COLUMNS gezeigt:

mysql> SHOW COLUMNS FROM artist;

+-------------------+------------------+------+-----+-------------------+...
| Field             | Type             | Null | Key | Default           |...
+-------------------+------------------+------+-----+-------------------+...
| language_id       | tinyint unsigned | NO   | PRI | NULL              |...
| name              | char(20)         | YES  |     | n/a               |...
| last_updated_time | timestamp        | NO   |     | CURRENT_TIMESTAMP |...
| native_name       | char(20)         | YES  |     | NULL              |...
+-------------------+------------------+------+-----+-------------------+...
4 rows in set (0.00 sec)

Wenn du möchtest, dass sie stattdessen die erste Spalte ist, verwende das Schlüsselwort FIRST wie folgt:

mysql> ALTER TABLE language ADD native_name CHAR(20) FIRST;

Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW COLUMNS FROM language;

+-------------------+------------------+------+-----+-------------------+...
| Field             | Type             | Null | Key | Default           |...
+-------------------+------------------+------+-----+-------------------+...
| native_name       | char(20)         | YES  |     | NULL              |...
| language_id       | tinyint unsigned | NO   | PRI | NULL              |...
| name              | char(20)         | YES  |     | n/a               |...
| last_updated_time | timestamp        | NO   |     | CURRENT_TIMESTAMP |...
+-------------------+------------------+------+-----+-------------------+...
4 rows in set (0.01 sec)

Wenn du willst, dass er an einer bestimmten Stelle eingefügt wird, benutze das Schlüsselwort AFTER:

mysql> ALTER TABLE language ADD native_name CHAR(20) AFTER name;

Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW COLUMNS FROM language;

+-------------------+------------------+------+-----+-------------------+...
| Field             | Type             | Null | Key | Default           |...
+-------------------+------------------+------+-----+-------------------+...
| language_id       | tinyint unsigned | NO   | PRI | NULL              |...
| name              | char(20)         | YES  |     | n/a               |...
| native_name       | char(20)         | YES  |     | NULL              |...
| last_updated_time | timestamp        | NO   |     | CURRENT_TIMESTAMP |...
+-------------------+------------------+------+-----+-------------------+...
4 rows in set (0.00 sec)

Um eine Spalte zu entfernen, verwendest du das Schlüsselwort DROP, gefolgt von dem Spaltennamen. So wirst du die neu hinzugefügte Spalte native_name wieder los:

mysql> ALTER TABLE language DROP native_name;

Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

Dabei werden sowohl die Spaltenstruktur als auch die in der Spalte enthaltenen Daten gelöscht. Außerdem wird die Spalte aus allen Indizes entfernt, in denen sie enthalten war; wenn sie die einzige Spalte in einem Index ist, wird auch der Index gelöscht. Du kannst eine Spalte nicht entfernen, wenn sie die einzige in einer Tabelle ist; dazu musst du stattdessen die Tabelle löschen, wie in "Strukturen löschen" erklärt . Sei vorsichtig, wenn du Spalten löschst, denn wenn sich die Struktur einer Tabelle ändert, musst du in der Regel auch alle INSERT Anweisungen ändern, mit denen du Werte in einer bestimmten Reihenfolge einfügst. Mehr dazu erfährst du unter "Die INSERT-Anweisung".

MySQL erlaubt es dir, mehrere Änderungen in einer einzigen ALTER TABLE Anweisung anzugeben, indem du sie mit Kommas trennst. Hier ist ein Beispiel, das eine neue Spalte hinzufügt und eine andere anpasst:

mysql> ALTER TABLE language ADD native_name CHAR(255), MODIFY name CHAR(255);

Query OK, 6 rows affected (0.06 sec)
Records: 6  Duplicates: 0  Warnings: 0

Beachte, dass du dieses Mal siehst, dass sechs Datensätze geändert wurden. Bei den vorherigen ALTER TABLE Befehlen meldete MySQL, dass keine Zeilen betroffen waren. Der Unterschied besteht darin, dass wir dieses Mal keine Online-DDL-Operation durchführen, denn das Ändern des Typs einer Spalte führt immer dazu, dass eine Tabelle neu erstellt wird. Wir empfehlen, die Online-DDL-Operationen im Referenzhandbuch zu lesen, wenn du deine Änderungen planst. Wenn du Online- und Offline-Operationen kombinierst, wird eine Offline-Operation durchgeführt.

Wenn keine Online-DDL verwendet wird oder eine der Änderungen "offline" ist, ist es sehr effizient, mehrere Änderungen in einem einzigen Vorgang zusammenzuführen. Das spart potenziell die Kosten für die Erstellung einer neuen Tabelle, das Kopieren von Daten aus der alten Tabelle in die neue Tabelle, das Löschen der alten Tabelle und die Umbenennung der neuen Tabelle in den Namen der alten Tabelle für jede einzelne Änderung.

Hinzufügen, Entfernen und Ändern von Indizes

Wie wir bereits besprochen haben, ist es oft schwer zu wissen, welche Indizes nützlich sind, bevor die Anwendung, die du erstellst, genutzt wird. Vielleicht stellst du fest, dass eine bestimmte Funktion der Anwendung viel beliebter ist, als du erwartet hast, und du musst herausfinden, wie du die Leistung für die entsprechenden Abfragen verbessern kannst. Deshalb ist es nützlich, wenn du Indizes auch nach dem Einsatz deiner Anwendung hinzufügen, ändern und entfernen kannst. Dieser Abschnitt zeigt dir, wie das geht. Beachte, dass das Ändern von Indizes keine Auswirkungen auf die in einer Tabelle gespeicherten Daten hat.

Wir beginnen mit dem Hinzufügen eines neuen Indexes. Stell dir vor, dass die Tabelle language häufig mit einer WHERE Klausel abgefragt wird, die die name angibt. Um diese Abfragen zu beschleunigen, hast du beschlossen, einen neuen Index hinzuzufügen, den du idx_name nennst. So fügst du ihn hinzu, nachdem die Tabelle erstellt wurde:

mysql> ALTER TABLE language ADD INDEX idx_name (name);

Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

Auch hier kannst du die Begriffe KEY und INDEX synonym verwenden. Du kannst die Ergebnisse mit der Anweisung SHOW CREATE TABLE überprüfen:

mysql> SHOW CREATE TABLE language\G

*************************** 1. row ***************************
       Table: language
Create Table: CREATE TABLE `language` (
  `language_id` tinyint unsigned NOT NULL AUTO_INCREMENT,
  `name` char(255) DEFAULT NULL,
  `last_updated_time` timestamp NOT NULL
    DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`language_id`),
  KEY `idx_name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=8
    DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

Wie erwartet, ist der neue Index Teil der Tabellenstruktur. Du kannst auch einen Primärschlüssel für eine Tabelle angeben, nachdem sie erstellt wurde:

mysql> CREATE TABLE no_pk (id INT);

Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO no_pk VALUES (1),(2),(3);

Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE no_pk ADD PRIMARY KEY (id);

Query OK, 0 rows affected (0.13 sec)
Records: 0  Duplicates: 0  Warnings: 0

Nun wollen wir uns ansehen, wie man einen Index entfernt. Um einen Nicht-Primärschlüssel-Index zu entfernen, gehst du wie folgt vor:

mysql> ALTER TABLE language DROP INDEX idx_name;

Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0

Du kannst einen Primärschlüsselindex wie folgt löschen:

mysql> ALTER TABLE no_pk DROP PRIMARY KEY;

Query OK, 3 rows affected (0.07 sec)
Records: 3  Duplicates: 0  Warnings: 0

MySQL lässt nicht zu, dass du mehrere Primärschlüssel in einer Tabelle hast. Wenn du den Primärschlüssel ändern willst, musst du den bestehenden Index entfernen, bevor du den neuen hinzufügst. Wir wissen jedoch, dass es möglich ist, DDL-Operationen zu gruppieren. Nimm dieses Beispiel:

mysql> ALTER TABLE language DROP PRIMARY KEY,
    -> ADD PRIMARY KEY (language_id, name);

Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0

Du kannst einen einmal erstellten Index nicht mehr ändern. manchmal möchtest du ihn jedoch ändern, z. B. wenn du die Anzahl der indizierten Zeichen in einer Spalte verringern oder eine weitere Spalte zum Index hinzufügen möchtest. Die beste Methode dafür ist, den Index zu löschen und ihn dann mit den neuen Angaben neu zu erstellen. Angenommen, du möchtest, dass der Index idx_name nur die ersten 10 Zeichen von artist_name enthält. Gehe einfach wie folgt vor:

mysql> ALTER TABLE language DROP INDEX idx_name,
    -> ADD INDEX idx_name (name(10));

Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

Umbenennen von Tabellen und Ändern anderer Strukturen

Wir haben gesehen, wie man Spalten und Indizes in einer Tabelle ändern kann; jetzt sehen wir uns an, wie man Tabellen selbst ändern kann. Es ist einfach, eine Tabelle umzubenennen. Angenommen, du möchtest language in languages umbenennen. Verwende dazu den folgenden Befehl:

mysql> ALTER TABLE language RENAME TO languages;

Query OK, 0 rows affected (0.04 sec)

Das Schlüsselwort TO ist optional.

Es gibt noch einige andere Dinge, die du mit ALTER Kontoauszügen machen kannst, z. B:

• Ändere den Standardzeichensatz und die Sortierreihenfolge für eine Datenbank, eine Tabelle oder eine Spalte.

• Verwalte und ändere Beschränkungen. Du kannst zum Beispiel Fremdschlüssel hinzufügen und entfernen.

• Füge eine Partitionierung zu einer Tabelle hinzu oder ändere die aktuelle Partitionierungsdefinition.

• Ändere die Speicher-Engine einer Tabelle.

Mehr über diese Operationen findest du im MySQL-Referenzhandbuch, in den Abschnitten über die ALTER DATABASE und ALTER TABLE Anweisungen. Eine alternative, kürzere Schreibweise für dieselbe Anweisung ist RENAME TABLE:

mysql> RENAME TABLE languages TO language;

Query OK, 0 rows affected (0.04 sec)

Eine Sache, die sich nicht ändern lässt, ist der Name einer bestimmten Datenbank. Wenn du jedoch die InnoDB-Engine verwendest, kannst du RENAME verwenden, um Tabellen zwischenDatenbanken zu verschieben:

mysql> CREATE DATABASE sakila_new;

Query OK, 1 row affected (0.05 sec)

mysql> RENAME TABLE sakila.language TO sakila_new.language;

Query OK, 0 rows affected (0.05 sec)

mysql> USE sakila;

Database changed

mysql> SHOW TABLES LIKE 'lang%';

Empty set (0.00 sec)

mysql> USE sakila_new;

Database changed

mysql> SHOW TABLES LIKE 'lang%';

+------------------------------+
| Tables_in_sakila_new (lang%) |
+------------------------------+
| language                     |
+------------------------------+
1 row in set (0.00 sec)

Löschen von Strukturen

Im vorherigen Abschnitt haben wir gezeigt, wie du Spalten und Zeilen aus einer Datenbank löschen kannst; jetzt beschreiben wir, wie du Datenbanken und Tabellen entfernen kannst.

Datenbanken fallen lassen

Eine Datenbank zu löschen, ist ganz einfach. So löschst du die Datenbank sakila:

mysql> DROP DATABASE sakila;

Query OK, 25 rows affected (0.16 sec)

Die Anzahl der Zeilen, die in der Antwort zurückgegeben wird, entspricht der Anzahl der entfernten Tabellen. Du solltest vorsichtig sein, wenn du eine Datenbank löschst, da alle Tabellen, Indizes und Spalten gelöscht werden, ebenso wie alle zugehörigen Dateien und Verzeichnisse auf der Festplatte, die MySQL zu ihrer Verwaltung verwendet.

Wenn eine Datenbank nicht existiert, führt der Versuch, sie zu löschen, dazu, dass MySQL einen Fehler meldet. Versuchen wir noch einmal, die Datenbank sakila zu löschen:

mysql> DROP DATABASE sakila;

ERROR 1008 (HY000): Can't drop database 'sakila'; database doesn't exist

Du kannst den Fehler vermeiden, was nützlich ist, wenn du die Anweisung in ein Skript einfügst, indem du die Phrase IF EXISTS verwendest:

mysql> DROP DATABASE IF EXISTS sakila;

Query OK, 0 rows affected, 1 warning (0.00 sec)

Du kannst sehen, dass eine Warnung gemeldet wird, da die Datenbank sakila bereits gelöscht wurde.

Tische entfernen

Das Entfernen von Tabellen ist so einfach wie das Entfernen einer Datenbank. Erstellen und entfernen wir eine Tabelle aus der Datenbank sakila:

mysql> CREATE TABLE temp (id SERIAL PRIMARY KEY);

Query OK, 0 rows affected (0.05 sec)

mysql> DROP TABLE temp;

Query OK, 0 rows affected (0.03 sec)

Keine Sorge: Die Meldung auf 0 rows affected ist irreführend. Du wirst feststellen, dass die Tabelle definitiv weg ist.

Du kannst die Phrase IF EXISTS verwenden, um Fehler zu vermeiden. Lass uns noch einmal versuchen, die Tabelle temp zu löschen:

mysql> DROP TABLE IF EXISTS temp;

Query OK, 0 rows affected, 1 warning (0.01 sec)

Wie üblich kannst du die Warnung mit der Anweisung SHOW WARNINGS untersuchen:

mysql> SHOW WARNINGS;

+-------+------+-----------------------------+
| Level | Code | Message                     |
+-------+------+-----------------------------+
| Note  | 1051 | Unknown table 'sakila.temp' |
+-------+------+-----------------------------+
1 row in set (0.00 sec)

Du kannst mehr als eine Tabelle in einer einzigen Anweisung löschen, indem du die Tabellennamen mit Kommas trennst:

mysql> DROP TABLE IF EXISTS temp, temp1, temp2;

Query OK, 0 rows affected, 3 warnings (0.00 sec)

In diesem Fall gibt es drei Warnungen, weil keine dieser Tabellen existiert.