Datenbanktabellen - Spaltentypen und Werte

Vorbemerkungen

Wenn eine Tabellenspalte erzeugt wird, muss ihr ein Spaltentyp zugewiesen werden. Der Spaltentyp regelt, welche Einträge in die Zellen dieser Spalte aus Sicht von MySQL zulässig sind und wie sie von MySQL bei Verwendung in MySQL-Befehlen interpretiert werden. Über verschiedene Konstanten für den SQL-Modus und das Schlüsselwort IGNORE kann gesteuert werden, wie sich MySQL verhalten soll, wenn bei einem INSERT- oder UPDATE-Vorgang einer Zelle ein Wert zugewiesen werden soll, der nach dem Spaltentyp nicht zulässig ist.

Wird ein Wert aus einer MySQL-Datenbank mit dem php-Befehl mysqli_query($con, "SELECT .."); ausgelesen, ist für die Weiterverarbeitung des Wertes mit php-Befehlen unbedingt zu beachten, dass der Wert im Ergebnis der Abfrage immer den Typ "String" besitzt, unabhängig vom MySQL-Spaltentyp der Zelle in der Datenbank. Falls der php-Befehl, an den ein Wert aus dem Ergebnis übergeben werden soll, danach differenziert, welcher Datentyp der Wert besitzt, gelingt die korrekte Verarbeitung des Wertes nur, wenn vor der Verarbeitung der Datentyp des Wertes innerhalb der php-Anwendung vom php-Datentyp "String" in den erforderlichen php-Datentyp umgewandelt wird.

zum Anfang

Strikter SQL-Modus und IGNORE

Über die Vorgabe des Spaltentyps wird festgelegt, welche Werte in einer Zelle der Spalte gespeichert werden dürfen. Allerdings ist es möglich, dass ein Nutzer Daten eingibt, die nicht in der Spalte gespeichert werden können, sei es dass der Wert zu groß ist oder zu viele Zeichen enthält oder Zeichen enthält, die nicht zugelassen sind. Oder es könnten durch Datenmanipulation (z.B. Rechenvorgänge, Aktionen auf Zeichenketten) Werte entstehen, die nicht in die Spalte eingetragen werden dürfen. Durch die Konfiguration des Servers über den SQL-Modus (vgl. auch Datenbanktabellen - ENGINE und Fehlertoleranz) kann weitgehend eingestellt werden, welche Fehler MySQL tolerieren soll und bei welchen es den Befehl abbrechen soll.

Wenn MySQL fehlertolerant sein soll, passt es die Werte so an, dass es diese eintragen kann, wobei die Anpassung vom Spaltentyp abhängt. Daher werden die Veränderung unzulässiger Eingaben weiter unten bei den Hinweisen zu den Spaltentypen beschrieben. Unabhängig vom Spaltentyp, dem SQL-Modus und dem optionalen Befehlszusatz IGNORE werden allerdings INSERT- und UPDATE-Befehle niemals ausgeführt, wenn dadurch in einer Spalte / Spaltenkombination, in welcher keine Duplikate zulässig sind, durch den einzufügenden/ändernden Datensatz ein Duplikat entstehen würde.

Mit dem SQL-Modus "STRICT_TRANS_TABLES" kann für transaktionale Datenbanktabellen (also für InnoDB-Tabellen) erzwungen werden, dass bei einer zusammenhängenden Folge von Befehlen auf transaktionalen Tabellen (die Befehlsfolge muss als "Transaktion" gekennzeichnet sein) sämtliche bereits ausgeführten Befehle dieser Befehlsfolge rückgängig gemacht werden, wenn im Verlauf der Transaktion entdeckt wird, dass in eine Zelle ein nicht zugelassener Wert eingetragen werden soll. Nach dem Zurücksetzen hat die Datenbank den gleichen Zustand wie vor dem Start der Befehlsfolge. Sofern INSERT- und UPDATE-Befehle auf Datenbanktabellen ausgeführt werden sollen, die nicht transaktional sind (z.B. MyISAM-Tabelle) kann mit dem SQL-Modus "STRICT_ALL_TABLES" verhindert werden, dass eine Anpassung unzulässiger Werte erfolgt. Befindet sich der Befehl allerdings innerhalb einer Befehlsfolge, so wird die Befehlsfolge nur abgebrochen. Der fehlerhafte Befehl und alle folgenden Befehle werden nicht ausgeführt. Befehle aus der Befehlsfolge, die vor dem fehlerhaften Befehl ausgeführt wurden, kann das System selber nicht automatisch zurücksetzen. Daher sollte im Falle von nicht transaktionalen Datenbanktabellen der Modus STRICT_ALL_TABLES nur dann eingesetzt werden, wenn der Abbruch der Befehlsfolge weniger Probleme verursacht als die Anpassung von Werten oder das Überspringen des fehlerhaften Befehls.

Der optionale Hinweis IGNORE kann sowohl mit den beiden STRICT-Modi als auch ohne STRICT-Modus eingesetzt werden. Sofern der Hinweis IGNROE vorhanden ist, verhält sich MySQL bezüglich der Ausführung immer so, als wäre kein STRICT-Modus eingestellt. MySQL ist dann fehlertolerant, soweit durch den Befehl kein unzulässiges Duplikat entstehen würde. Außerdem führt der Hinweis IGNORE dazu, dass MySQL sowohl bei der Anpassung von Werten als auch bei der Nichtausführung von Befehlen nur eine Warnmeldung erzeugt. Ist dagegen der Hinweis IGNORE nicht vorhanden, unterscheidet MySQL bei den Meldungen zwischen Fehlermeldungen (Befehl wurde nicht ausgeführt) und Warnmeldungen (für die Ausführung des Befehls wurde ein Wert angepasst). Überlässt man MySQL die Werteprüfung, sollte man in PHP nach einem gescheiterten Befehl mit mysqli_error($con) bzw. $mysqli->error sofort die Fehlermeldung auslesen und den Nutzer über die Fehlerursache informieren, damit er weiß, warum der Befehl scheiterte. Und mit mysqli_get_warnings($con) bzw. $mysqli->get_warnings() können und sollten in PHP die Warnmeldungen abgerufen und danach ausgegeben werden, damit der Nutzer z.B. darüber informiert wird, dass MySQL seine Eingaben angepasst hat.

zum Anfang

Spaltentypen

Spaltentypen für Zahlen

...int(x): In diesen Zellen können nur ganze Zahlen notiert werden. Es gibt in MySQL die folgenden Ganzzahltypen: INTEGER (= INT), TINYINT, SMALLINT, MEDIUMINT, und BIGINT, von denen TINYINT, MEDIUMINT und BIGINT Erweiterungen der Ganzzahltypen aus Standard-SQL sind.
In MySQL kann diesen Datentypgen zusätzlich das Attribut "unsigned" zugewiesen werden. Fehlt dieses Attribut, dürfen sowohl positive als auch negative Werte eingetragen werden, ansonsten nur positive.
Die unterschiedlichen Angaben für ... bedeuten, dass unterschiedlich große Zahlen dort gespeichert werden können. So können z.B. bei dem kleinsten Wertebereich "tinyint" nur Werte im Bereich von -128 bis + 127 bzw. bei "tinyint unsigned" nur Werte im Bereich von 0 bis 255 gespeichert werden. Dies sind Werte, die nur 1 Byte Speicherplatz belegen. Für int gilt -2.147.483.648 bis +2.147.483.647 bzw. 0 bis 4.294.967.295. (4 Byte Speicherbedarf pro Zahl). Die größten ganzen Zahlen können in Zellen vom Typ bigint gespeichert werden und benötigen jeweils 8 Byte Speicherplatz.
Die Vorgabewerte für x, welche MySQL bei der Definition von ...int-Spalten verwendet, reichen aus, um sämtliche Stellen der Zahlen, die in der Spalte gespeichert werden können, anzuzeigen. Mit x < Vorgabewert kann in Anwendungen, welche diesen Wert interpretieren, gesteuert werden, dass in der Ausgabe sämtliche Zahlen aus der Spalte maximal mit x Ziffern angezeigt werden. Durch die MysQL-Zusatzeigenschaft "ZEROFILL" kann dabei erreicht werden, dass einer Zahl die aus weniger Ziffern als x bestetht, die Ziffer 0 so oft vorangestellt wird, bis auch diese Zahl aus x Ziffern besteht. Ändert man für eine Spalte den Ganzzahltyp in einen anderen Ganzzahltyp, sollte man darauf achten, dass man auch den Wert für x verändert, da MySQL bei dem Wechsel von einem Ganzzahltyp zu einem anderen Ganzzahltyp den Wert für x nicht automatisch anpasst.
Falls ... = tiny und (x) = (1) bedeutet dieses meist, dass derjenige, der die Datenbanktabelle angelegt hat, möchte, dass die Werte in diesen Zellen als Wahrheitswert interpretiert werden. Dabei soll die Ziffer 0 als "falsch" und alle anderen Werte sollen als "wahr" interpretiert werden.

decimal(M,D): In Zellen von diesem Typ können positive und negative Dezimalzahlen (Festkommazahlen) mit mindestens einer und bis zu (M-D) Ziffern vor dem "Komma" und genau D Stellen nach dem "Komma" gespeichert werden. M muss um mindestens 1 größer sein als D und darf höchstens 65 sein. Außerdem gilt 0 ≶= D <= 30.
Zu beachten ist, dass das "Komma" ohne Umformatierung durch den Programmierer allerdings in der englischen Schreibweise als Dezimalpunkt ausgegeben wird/einzugeben ist.
Eine Angabe wie decimal(4,2) bedeutet, dass für die Zahl insgesamt maximal 4 Ziffern gespeichert werden, davon immer zwei nach dem Komma. Fehlen Ziffern nach dem Komma, wird für jede fehlende Ziffer automatisch die Ziffer 0 eingetragen. Wird an den MySQL-Server ein Zahl mit mehr Nachkommastellen übergeben, werden abhängig von den Vorgaben des MySQL-Servers entweder die "überflüssigen" Dezimalstellen abgeschnitten oder der Wert auf 2 Nachkommastellen gerundet eingetragen.
Wenn nicht der strikte SQL-Modus eingestellt ist, passt MySQL zu große Zahlen und zu kleine Zahlen, die eingegeben wurden automatisch an. Es wird dann für zu große Zahlen statt des nicht zuglassenen Wertes automatisch der maximal mögliche Wert in der Zelle eingetragen (im Falle von decimal(4,2) und dem Wert 100 also 99,99), falls in dem Skript, welches die Werte aus dem Eingabeformular ausliest und in die Datenbank einträgt, der Programmierer für zu große Werte keine andere Verarbeitungsvorschrift vorgibt.
Synonyme für diesen Datentyp sind DEC, NUMERIC und FIXED. Sie besitzen die gleichen Optionen wie DECIMAL.

FLOAT, DOUBLE PRECISION: In Zellen mit diesen Spaltentypen werden die Zahlen im Binärformat gespeichert. Dadurch sind Rundungsfehler möglich. Daher eignen sich diese Spaltentypen nur für Zahlen, bei denen nach der Speicherung nicht der exakte Wert benötigt wird. Die Zahlen werden in Zellen mt diesem Spaltentyp als Fließkommazahlen (= Kommazahlen in Exponentialschreibweise) notiert. Es können sowohl negative als auch positive Fließkommazahlen gespeichert werden.
Eine Spalte mit dem Typ FLOAT kann als FLOAT, FLOAT(M) und FLOAT(M,D) deklariert werden. M gibt die Anzahl der Ziffern an und D die Anzahl der Ziffern nach dem Dezimalpunkt. Wird M nicht angegeben, ist das identisch mit der Angabe FLOAT(1). Es steht dann genau die erste Ziffer der Zahl vor dem Komma. Wird für M die Zahl 0 eingegeben, ist die Stelle vor dem Komma immer die Ziffer 0. M darf allerdings nur eine Zahl aus dem Wertebereich {0, 24} sein, damit die Zelle den Datentyp FLOAT besitzt. Wenn M eine Zahl aus dem Werbereich {25, 53} ist, verändert MySQL den Datentyp zu DOUBLE. Fehlt die Angabe zu D, werden so viele Stellen nach dem Komma gespeichert, wie es die Kapazität des Systems erlaubt.
Eine Ziffer in FLOAT ist etwa bis zur 7. Dezimalstelle exakt. Die Verwendung von FLOAT für Rechenoperationen in MySQL kann zu unerwarteten Problemen führen, weil alle Berechungen in MySQL mit doppelter Präzision, also mit dem Datentyp DOUBLE, ausgeführt werden.
Auch für den Datentyp DOUBLE kann angegeben werden, wie viele Stellen vor und nach dem Komma stehen sollen. Für diesen Datentyp kann die Deklaration daher als DOUBLE, DOUBLE(M) und DOUBLE(M,D) erfolgen. Der Datentyp DOUBLE PRECISION[(M,D)] ist in MySQL ein Synonym für DOUBLE. In einer Zelle vom Typ DOUBLE können die Zahlen aus folgendem Wertebereichen gespeichert werden: negative Zahlen {≈-1,8E+308, ≈-2,225E-308}, die Zahl 0 {0}, positive Zahlen {≈2,225E-308, ≈1,8E+308}.
Der Datentyp REAL[(M,D)] ist in MySQL ebenfalls ein Synonym für DOUBLE, es sei denn der SQL-Modus REAL_AS_FLOAT ist aktiviert, dann ist der Datentyp REAL ein Synonym für FLOAT.
Auch für Datentypen FLOAT und DOUBLE sind die Attribute UNSIGNED und ZEROFILL möglich. Wird für diese Spalten das Attribut "UNSIGNED" gesetzt, verringert sich die Anzahl der zulässigen Werte in diesen Zellen, denn dann dürfen keine negativen Zahlen gespeichert werden, die Menge der zulässigen positiven Zahlen, die in der Zelle gespeichert werden dürfen, ändert sich dadurch nicht.

Wenn bei einem INSERT- oder UPDATE-Befehl in ein Feld mit einem numerischen Datentyp eine Zahl eingegeben werden soll, deren Wert größer oder kleiner ist, als durch den Spaltentyp zugelassen, hängt die Reaktion von MySQL davon ab, welcher SQL-Modus aktiviert ist. Wenn der strikte SQL-Modus aktiviert ist, der dem Standard-Modus von SQL entspricht, wird der Befehl nicht ausgeführt und MySQL meldet einen Fehler. Wenn eingestellt ist, dass sich MySQL nicht so restriktiv verhalten soll, wählt MySQL bei einer Zahl, die zu groß ist, die größte zulässige Zahl für die Zelle und für eine Zahl, die zu klein ist, die kleinste zulässige Zahl und informiert über die Änderung des Zahlenwertes mit einer Warnmeldung.

Datentypen für Datums- und Zeitangaben

Mit dem Datentyp TIME(fsp) kann ein Zeitintervall erfasst werden. Ein Zeitintervall ist die Zeitspanne zwischen zwei Zeitpunkten. In diesen Zellen sind Eingaben aus dem Wertebereich {-838:59:59.000000, 838:59:59.00000} zulässig. Die Einheiten sind (h:min:sec). Wie viele Dezimalstellen für die Sekunden gespeichert werden können, wird durch den Wert fsp festgelegt. fsp darf nur eine ganze Zahl aus dem Wertebereich {0, 6} sein. Wird der Datentyp nur mit TIME angeben, entspricht das dem Datentyp TIME(0). Bei diesen beiden Angaben werden keine Bruchteile von Sekunden gespeichert.

Die anderen Datentypen DATE, DATETIME, YEAR und TIMESTAMP erfassen den Wert für einen Zeitpunkt.
YEAR, YEAR(2) und YEAR(4) können nur Jahreszahlen aufnehmen, wobei die Angabe in den Klammern angeben, ob die Jahresangabe zweistellig oder vierstelltig sein soll. Zweistellige Jahreszahlen für YEAR werden von MySQL wie folgt umgesetzt: 70-99 zu 1970 - 1999, 00 - 69 zu 2000 bis 2069.
Mit DATE können Datumsangaben, die Ziffern für das Jahr, den Monat und den Tag enthalten gespeichert werden {1000-01-01, 9999-12-31}, mit DATETIME(fsp) zusätzlich die genaue Uhrzeit {1000-01-01 00:00:00.000000, 9999-12-31 23:59:59.999999}. TIMESTAMP(fsp) umfasst nur eine Teilmenge von DATETIME, nämlich den Wertebereich {1970-01-01 00:00:01.000000 UTC, 2038-01-19 03:14:07,99999 UTC}. Die "krummen" Werte für TIMESTAMP resultieren daraus, dass diese Angabe in Form einer Zahl vom Typ INT gespeichert wird. Der Wert 0 entspricht dem Zero-Wert 0000-00-00 00:00:00.000000. Wird der TIMESTAMP in eine Datumsangabe umgerechnet, ist immer die Zeitzone anzugeben, für welche der genaue Zeitpunkt in Form einer Datums-/Zeitangabe angegeben werden soll.

Enthält eine Datenbanktabelle eine Spalte mit dem Datentyp TIMESTAMP und wurde für diese festgelegt, dass bei jeder Änderung des Datensatzes der Wert angepasst werden soll (DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP), so kann anhand des Spaltenwertes immer ausgelesen werden, wann dieser Datensatz zuletzt verändert wurde.

Mit dem Modus SQL-ALLOW-INVALID-DATES kann für die Datentypen DATE und DATETIME zugelassen werden, dass Kombinationen von Monats- und Tagesangaben eingetragen werden können, die in der Realität nicht vorkommen, z.B. 30.2.2005. Durch den SQL-MODUS NO-ZERO-IN-DATE kann für diese Spalten verhindert werden, dass als Monats- und Tagesangabe der Wert 0 eingetragen wird.

Unabhängig vom SQL-Modus wird bei den Datentypen DATE, DATETIME, TIMESTAMP und YEAR immer der zugehörige zero-Wert (DATE = 0000-00-00, DATETIME und TIMESTAMP: 0000-00-00 00:00:00, YEAR: 0000 ) von MySQL verwendet, wenn in diese Spalten ein Wert eingetragen werden soll, der kleiner oder größer ist und in der Spalte ZERO-Werte erlaubt sind. Soll statt des ZERO-Wertes der Wert NULL eingetragen werden, muss der NULL-Wert für diese Spalten explizit erlaubt und der SQL-Modus NO_ZERO_DATE gelten. Sind weder der NULL-Wert noch ZERO-Werte erlaubt, kann der Befehl nicht ausgeführt werden. Für TIME wird nur bei restriktivem Modus der datentyp-spezifische "zero"-Wert eingetragen (TIME = 00:00:00). Ansonsten passt MySQL die Eingabe an, es wird also entweder der jeweils größte bzw. der kleinste zulässige Wert gewählt.

Spaltentypen für Buchstaben, Worte und Texte

Jedes Zeichen wird geräteintern durch eine Zahl repräsentiert, welche der Position des Zeichens innerhalb des Zeichensatzes entspricht. Anfangs wurde nur ein Zeichensatz verwendet, der wenige Zeichen enthielt. Im Laufe der Zeit stellte der eine und andere fest, dass mehr Zeichen benötigt werden, um die gewünschten Sachverhalte digital abzubilden. Weil es wegen der fehlenden Vernetzung zunächst nicht erforderlich war, dass alle den gleichen Zeichensatz verwendeten, entwickelten sich verschiedene Zeichensätze für die verschiedenen Sprachen und Anwendungszwecke. Daher muss für Spalten, in denen Buchstaben, Worte und Texte gespeichert werden, immer angegeben, welcher Zeichensatz verwendet wird.

Bei Zeichensätzen die nur 255 Zeichen enthalten, reicht 1 Byte aus, um jedem Zeichen genau eine Zahl in Binärdarstellung zuordnen zu können. Enthält der Zeichensatz mehr als 255 Zeichen, werden je nach Zeichensatz pro Zeichen 1 bis 4 Bytes Speicherplatz benötigt. Einige Zeichensätze nutzen dabei für jedes Zeichen die gleiche Anzahl von Bytes, andere dagegen eine variable Länge, um weniger Speicherplatz zu belegen. Wenn ein Zeichensatz für die Codierung immer drei Byte pro Zeichen verwendet, teilt das System eine Bytefolge in Abschnitte von drei Bytes auf und kann dann für jedes Dreierpäckchen den Zahlenwert ermitteln. Für die Ausgabe wählt das System dann aus der Zeichensatztabelle das grafische Zeichen, welches dieser Zahl zugeordnet ist. Bei Zeichensätzen, welche eine variable Bytezahl aufweisen (z.B. utf8 - 1 bis 4 Byte pro Zeichen) muss das System die Bytefolge Byte für Byte auswerten. Anhand der Codierungsregeln kann es aus der Binärdarstellung eines Bytes ablesen, ob das Byte einem grafischen Zeichen zugeordnet ist, oder ob auch das nächste, die nächsten beiden oder sogar die drei folgenden Bytes mit ausgewertet werden müssen, um die Zahl berechnen zu können, deren grafisches Gegenstück auszugeben ist.

Für die Spaltentypen char(x), varchar(x) und die diversen vier Varianten von TEXT ("TINYTEXT", "MEDIUMTEXT", "LONGTEXT" und "TEXT" ) ist jeweils anzugeben, welcher Zeichensatz für die Zeichenketten gilt. Von dem gewählten Zeichensatz hängt ab, welche Zeichen in den Zellen der Spalte gespeichert werden können und wie viele Bytes pro Zeichen für die Speicherung benötigt werden.

Soll in eine Zeile ein Zeichen eingegeben werden, welches nicht in dem Zeichensatz enthalten ist, welcher Basis der Zeichenkodierung ist, wird beim STRICT-Modus der Befehl nicht ausgeführt oder im anderen Modus die Zeichen, die im Zeichensatz fehlen durch ein zeichensatzspezifisches "JOKER"-Zeichen ersetzt. Für die Sortierung ist die gewählt Collation entshcheidend. Eine Collation gehört immer zu genau einem Zeichensatz und enthält Sortierregeln. Für einen Zeichensatz kann es mehrere Collation geben.

Anhand der Positionsziffer können die verschiedenen Werte, die in einer Spalte gespeichert sind, sortiert werden. Diese Sortierregel wird als "Binär-Collation" bezeichnet. Die Sortierung nach der Positionsziffer entspricht aber meist nicht der Sortierung der Zeichenkette innerhalb der Schriftsprachen, für welche der Zeichensatz genutzt werden kann. Auch können die Sortierregeln bei gleicher Schriftsprache länderspezifisch oder sogar anwendungsspezifisch unterschiedlich sein. So gibt es z.B. für die Sprache Deutsch in Deutschland eine Sortierregel für den Duden und eine für das Telefonbuch. Alle zusammengehörigen Sortierregeln für eine Sprache oder einen Anwendungszweck bilden eine Collation. Durch die Wahl der passenden Collation für eine Spalte wird erreicht, dass die sortierten Werte in der erwarteten Reihenfolge erscheinen.

Weil jeder Collation genau ein Zeichensatz zugeordnet ist, reicht es bei der Spaltendefinition aus, nur die Collation anzugeben, um die Menge der anzuwendenden Sortierregeln für die Spalte auszuwählen. Wird dagegen nur der Zeichensatz angegeben, wird von MySQL der Spalte jene Collation zugeordnet, die innerhalb der Datebank laut INFORMATION_SCHEMA.CHARACTER_SETS für diesen Zeichensatz die Standard-Collation ist. Wenn einer Spalte das Attribut BINARY zugeordnet wird, wählt MySQL für diese Spalte automatisch den Zeichensatz aus, der für die Datenbanktabelle als Standard-Zeichensatz festgelegt ist, und ordnet die Binär-Collation zu. Fehlt jegliche Angabe zum Zeichensatz und zur Codierung, wird der Standard-Zeichensatz und die zugehörige Standard-Collation zugeordnet, der für die Datenbanktabelle festgelegt ist.

char(L), varchar(L):Zellen mit diesen Datentypen nehmen Zeichenketten auf, die zwischen 0 und L Zeichen lang sind. Welche Zeichen innerhalb der Zeichenketten zulässig sind, wird mit der Angabe für die Zeichenkodierung festgelegt. Die Zeichenkodierung wirkt sich auch darauf aus, wie viel Speicherplatz für Werte in diesen Zellen benötigt wird.
Für char gilt 0 <= L <= 255 und für varchar gilt 0 <= L <= 65535. Der Maximalwert 65535 kann nur dann einer Zelle zugewiesen werden, wenn es die einzige Spalte in der Datenbanktabelle ist und eine Zeichenkodierung verwendet wird, die je Zeichen nur 1 Byte Speicherplatz benötigt. Denn für alle Werte, die in einer Tabellenzeile, also für einen Datensatz innerhalb der Tabelle, gespeichert werden können, stehen insgesamt maximal 65536 Byte (= 64 KB) an Speicherplatz zur Verfügung. Datenbankserver sind aber mindestens für den Tabellentyp "INNODB" häufig so eingestellt, dass sie statt 64KB Zeichen nur 16KB Zeichen (also 16.384) Zeichen pro Datensatz zulassen. Manche begrenzen die Anzahl noch weiter. (8 KB oder 4 KB).
Beim Datentyp char(L) richtet sich der Speicherbedarf immer nach der Zahl L und der Zeichenkodierung. Bei einer Zeichenkodierung mit einer festen Bytelänge F pro Zeichen (Zeichensatz latin1: F = 1, Zeichensatz UCS2: F = 2 ), werden pro Zelle jeweils L x F Byte benötigt, auch wenn in der Zelle deutlich weniger Zeichen als L gespeichert werden sollen. An die zu speichernde Zeichenkette werden so viele Leerzeichen angehängt, bis die Zeichenkette L Zeichen lang ist.
Beim Datentyp char(L) und einer Zeichenkodierung mit variabler Bytelänge ermittelt MySQL, wie viel Byte ein einzelnes Zeichen des Zeichensatzes maximal belegt (bei utf8mb4 sind dies M = 4 Byte). Außer bei dem Tabellenformat InnoDB werden in diesem Fall an die zu speichernde Zeichenfolge so viele Leerzeichen angehängt, bis die Zeichenfolge L x M Byte lang ist. Das Tabellenformat InnoDB geht nicht so verschwenderisch mit dem Speicherplatz um, es werden aber mindestens L Byte pro Zelle benötigt. Wenn für die zu speichernde Zeichenkette nicht so viele Bytes benötigt werden, werden auch in diesem Fall so viele Leerzeichen angehängt, bis die Zeichenkette L Bytes lang ist. Ist die Zeichenkette länger als L Bytes aber weniger als N x L Bytes lang (N <= M), werden an die Zeichenkette so viele Leerzeichen angehängt, bis sie genau N x L Bytes lang ist, es sei denn für die Zeichenkette ohne Ergänzung durch Leerzeichen werden schon mehr als 768 Bytes benötigt. Falls das der Fall ist, wird die Zeichenkette wie eine Zeichenkette vom Datentyp varchar gespeichert.
Beim Datentyp varchar(L) notiert sich das System in einem Byte (0<=L<=255) bzw. zwei Byte (255<L<=65535), wie viele Zeichen die Zeichenkette enthält und belegt anschließend nur nur soviel Speicherplatz, wie für die Zeichen in der Zeichenkette erforderlich sind.
Weil MySQL beim Speichern einer Zeichenkette im Datentyp varchar das Ende der Zeichenfolge nicht durch Anhängen von Leerzeichen manipuliert und notiert wurde, wie viele Zeichen die Zeichenkette lang ist, wird die Zeichenfolge genauso wieder ausgelesen, wie sie eingegeben wurde. Daher bleiben bei Zeichenketten vom Typ varchar auch Leerzeichen am Ende der Zeichenkette erhalten. Dagegen werden beim Speichern von Zeichenketten im Datentyp char sämtliche Leerzeichen am Ende der Zeichenkette entfernt. Denn MysQL kann nicht erkennen, ob es die Leerzeichen selber hinzugefügt hat, um eine Bytefolge mit einer Länge von N x L Bytes zu erreichen, oder ob die Leerzeichen mit eingegeben wurden.

Wenn bei einem INSERT- oder UPDATE-Vorgang in ein Feld mit dem Datentyp char(x) oder varchar(x) mehr als x Zeichen gespeichert werden sollen, wird der Befehl nicht ausgeführt, wenn in MySQL der strikte SQL-Modus aktiviert ist. MySQL gibt dann eine Fehlermeldung aus. Wenn sich MySQL nicht so restriktiv verhalten soll, kürzt MySQL die Zeichenkette und trägt nur die ersten x Zeichen der Zeichenkette ein. In diesem Fall informiert MySQL mit einer Warnmeldung über die Veränderung des Wertes.

Spaltentypen für Binärdaten

MySQL kennt die Datentypen "BINARY", "VARBINARY", "TINYBLOB", "MEDIUMBLOB", "LONGBLOB", und "BLOB". Diese Datentypen weisen große Ähnlichkeiten zu den Datentypen "CHAR", "VARCHAR", "TINYTEXT", "MEDIUMTEXT", "LONGTEXT" und "TEXT" auf. Im Gegensatz zu den Spaltentypen für Buchstaben, Worte und Texte werden hier die Daten in Binärdarstellung und ohne Angabe zur ursprünglichen Codierung gespeichert. Beim Auslesen der Daten muss daher die ursprüngliche Codierung bekannt sein, wenn die Daten korrekt interpretiert werden sollen. Die Datentypgen "MEDIUMBLOB", "LONGBLOB", und "BLOB" belegen genau wie die entsprechenden Datentypen "MEDIUMTEXT", "LONGTEXT", und "TEXT" nur 9 - 12 Bytes in einem Datensatz, egal wie groß die Datenmenge ist, die der Zelle zugeordnet wurde. Die Daten werden bei diesen Datentypen außerhalb der Datensatztabelle gespeichert und die Zelle enthält nur den Verweis darauf, in welcher Datei der eigentliche Zellinhalt gespeichert ist.

Spaltentyp BIT

In diesem Spaltentyp kann ein einzelnes Bit (BIT, BIT(1), Wert 0 oder 1) oder eine Bitfolge (BIT(M), 2 <= M <= 64), die aus maximal 64 Bits besteht, gespeichert werden. Eine Bitfolge enthält nur die Ziffern 0 und 1. Wenn eine Bitfolge eingetragen werden soll, die weniger Bits enthält als durch die Zahl M vorgegeben wird, wird der einzugebenden Bifolge so oft die Ziffer 0 vorangestellt, bis die Anzahl der Bits in der Bitfolge gleich M ist.

MySQL-spezifische Spaltentypen

enum('m','w')Innerhalb der Klammer können die Zeichenketten notiert werden, die alternativ als Eingabe in die Zellen der Spalte zulässig sind. Jeder Wert ist zwischen zwei Hochkommata zu notieren und die Werte werden durch Komma getrennt. Im Beispiel also 'm' oder 'w'.Intern werden diese Werte von MySQL mit Integerzahlen durchnummeriert (indiziert), beginnend mit der Zahl 1. Auch die leere Zeichenkette darf als zugelassener Wert definiert werden und besitzt dann als Index die Zahl, die ihrer Position in der Auflistung entspricht. Der NULL-Wert kann nicht innerhalb der Auflistung deklariert werden, sondern muss mit einer zusätzlichen Anweisung erlaubt werden. Wenn der NULL-Wert in der ENUM-Spalte erlaubt ist, besitzt dieser immer den Index NULL. Insgesamt können bis zu 65.535 alternative Werte definiert werden.
Eine leere Zeichenkette wird von MySQL übrigens auch immer dann in eine Zelle eingetragen, wenn in der Spalte ein Wert eingetragen werden soll, der nicht in der ENUM-Definition enthalten ist, nicht der strikte SQL-Modus eingestellt und kein Standard-Wert vorgegeben ist. Dieser leeren Zeichenkette, die aufgrund einer fehlerhaften Eingabe zugeordnet wurde, ist allerdings der Index 0 zugeordnet.
Wenn man in der ENUM-Definition lediglich einen Wert definiert (möglichst nicht die leere Zeichenkette), für die Spalte nicht den NULL-Wert erlaubt, den Wert aus der Liste nicht als DEFAULT-Wert deklariert und nicht der strikte SQL-Modus eingestellt ist, kann die ENUM-Spalte genutzt werden, um boolesche Werte zu speichern. Sinnvoll ist es dann, dem in der ENUM-Liste definierten Wert den booleschen Wert "true" zuzuordnen, weil der Index dieses Wertes innerhalb der ENUM-Spalte mit dem Integerwert 1 übereinstimmt, der sonst oft mit den booleschen Wert true assoziiert wird. Denn die leere Zeichenkette, die bei der Zuweisung von Werten zugeordnet wird, die nicht in der EMUM-Liste definiert sind, besitzt immer den Index 0, der mit dem booleschen Wert false assoziiert ist.
Falls man mit der ENUM-Spalte nicht die booleschen Werte repräsentieren möchte und "enum('x','y','z')" in mehreren Datenbanktabellen verwendet werden soll, sollte geprüft werden, ob es nicht sinnvoller ist, für enum('x','y','z') eine eigene Datenbanktabelle anzulegen und diese per Fremdschlüsselbeziehung mit jenen Datenbanktabellen, welche enum('x','y','z') verwenden sollen, zu verknüpfen. Die Auslagerung in eine Datenbanktabelle hat den Vorteil, dass Änderungen an der Menge der Werte von enum('x','y','z') nur in dieser Datenbanktabelle ausgeführt werden müssen, und die geänderten Werte dann automatisch in den verknüpften Datenbanktabellen zur Verfügung stehen und in allen Datenbanktabellen logisch gleiche Werte auch von der Darstellung her identisch sind. Nachteilig ist allerdings, dass für die Verknüpfung in jeder Datenbanktabelle Indizes erzeugt werden müssen, wodurch sich der Speicherbedarf erhöht.

set('x', 'y', 'z') Dieser Datentyp, ist dann geeignet, wenn man in den Zellen einer Spalte Listen ermöglichen möchte, die nur Werte enthalten dürfen, welche in der SET-Definition enthalten sind. Statt einer Liste kann in Zellen von diesem Typ alternativ immer auch die leere Zeichenfolge oder der Wert, welcher in der Spalte "Vorgabewert" enthalten ist, eingetragen werden. Insgesamt können bis zu 64 Werte innerhalb der SET-Definition aufgelistet werden.
Als Vorgabewert sind die leere Zeichenkette und jede beliebige Liste zulässig, welche aus den Werten der SET-Definition gebildet werden kann. Wenn der NULL-Wert für die Spalte erlaubt ist, kann auch der NULL-Wert der Vorgabewert sein.
Die Werte in der SET-Definition dürfen selber kein Komma enthalten, damit die Listen aus den Zellen wieder in die Elemente aus der SET-Definition zerlegt werden können. Innerhalb der SET-Definition darf ein Wert zwar mehrfach genannt werden, er darf aber in einer Zelle und dem Vorgabewert nur maximal einmal auftauchen.
Wenn z.B. in einem Hotel Zusatzwünsche zum Zimmer erfasst werden sollen, könnte die SET-Definition wie folgt aussehen (set('barrierefrei', 'Meerblick', 'Bad mit Badewanne'). Als Standardwert bietet sich dann die leere Zeichenfolge an, die andeutet, dass keine Zusatzwünsche geäußert wurden. Im Beispiel könnten dann in den Zellen der Spalte folgende Einträge stehen: "barrierefrei,Meerblick", "barrierefrei,Meerblick,Bad mit Badewanne", "", "Meerblick", "Meerblick,Bad mit Badewanne".
Wenn die Eingabe nicht über eine Anwendung erfolgt, die verhindert, dass bestimmte Kombinationen aus der SET-Menge eingetragen werden können, sollte die Auflistung nur Werte enthalten, die sich nicht gegenseitig ausschließen und die beliebig kombinierbar sind.
Damit bei einem INSERT-Vorgang oder UPDATE-Vorgang alle markierten Listeneinträge in die Zelle übernommen werden, müssen die markierten Listeneinträge als zusammenhängender String übergeben werden. Zwischen den gewählten Listeneinträgen muss jeweils ein Komma stehen, aber es darf kein weiteres Zeichen, auch kein Leerzeichen, auf das Komma folgen oder dem Komma vorangehen. Jedes Element aus der Liste darf maximal einmal genannt werden. Die gewählten Elemente dürfen in beliebiger Reihenfolge genannt werden. In der Ausgabe erscheinen sie allerdings in der Reihenfolge, in der sie in der SET-Definition stehen.
Mit der SET-Definition lässt sich der Datentyp BOOLEAN nicht nachbilden, da nicht erzwungen werden kann, dass nur einer der beiden Werte eingegeben wird.

Weitere Spaltentypen

MySQL kennt darüberhinaus - abhängig von der Version der Datenbank - diverse Spaltentypen für geometrische Daten und "JSON"-Datentypen.

zum Anfang

Ergänzende Informationen zum Spaltentyp

NULL-WERT

Enthält die Spalte den Wert "NO", so sind keine Null-Werte in der Spalte zugelassen.

Der Wert "YES" für eine Spalte ("SP") kennzeichnet dagegegen, dass der NULL-Wert als Wert in "SP" eingetragen werden darf. Außerdem ist bei "YES" der NULL-Wert der Vorgabewert für "SP", wenn für "SP" kein Vorgabewert explizit vorgegeben ist. Wird also ein neuer Datensatz in die Tabelle eingefügt und dabei für "SP" kein Wert oder ein nicht zulässiger Wert übermittelt, trägt MySQL für diesen Datensatz in die Spalte "SP" den NULL-Wert ein, sofern die Datenbanktabelle fehlertolerant sein soll.

EXTRA

auto-increment: Der Hinweis "auto_increment" bedeutet, dass jedem Datensatz vom Datenbankmanagementsystem automatisch eine Ganzzahl als Primärschlüsselwert zugeordnet wird, wenn der Datensatz in die Tabelle eingefügt wird. Und zwar merkt sich das System den zuletzt vergebenen Wert und weist dann einem einzufügenden Datensatz den um 1 erhöhten Wert zu.
Eine Spalte, welche die Eigenschaft "auto-increment" besitzt, ist immer die einzige Primärschlüsselspalte der Tabelle.

Vorgabewert

Wenn eine Datenbanktabelle fehlertolerant sein soll und in diese Datenbanktabelle der Datensatz "DS_neu" eingefügt werden soll, und dabei für "DS_neu" für die Spalte "SP" kein Wert übermittelt wird, versucht die SQL-Datenbank für "DS_neu" stattdessen in die Spalte "SP" den Vorgabewert für "SP" einzutragen. Auch wenn für "DS_neu" für "SP" ein nicht zulässiger Wert übermittelt wurde und dieser von MySQL nicht angepasst werden kann, wählt die fehlertolerante Datenbanktabelle den Vorgabewert aus.

Bei Spalten, für die kein Index oder nur ein einfacher Index festgelegt wurde, funktioniert das reibungslos. Wurde allerdings für die Spalte ein einspaltiger Index festgelegt, der Duplikate verbietet, und wurde zuvor schon einem anderen Datensatz "DS_alt" in dieser Spalte der Vorgabewert zugewiesen, scheitert das Einfügen des Datensatzes "DS_neu". Gehört die Spalte "SP" zu einem mehrspaltigen und eindeutigen Index, verhält sich die Datenbank analog. Daher sind Vorgabewerte für Spalten, für die ein Index formuliert wurde, der Duplikate verhindern soll, sehr wahrscheinlich überflüssig.

Wenn eine Datenbanktabelle fehlertolerant sein soll und für die Spalte "SP" kein NULL-Wert erlaubt ist und auch kein Vorgabewert explizit festgelegt wurde, versucht MySQL den impliziten Vorgabewert in die Spalte einzutragen. Für Spalten, in die beliebige Zeichen oder Zeichenketten eingetragen werden dürfen, ist der implizite Vorgabewert immer die leere Zeichenkette (''), für Spalten, in denen nur Zahlen erlaubt sind, ist es die Zahl 0. Für alle anderen Spalten richtet sich der implizite Vorgabewert nach dem Spaltenformat, (z.B. bei TIMESTAMP: 0000-00-00 00:00:00, bei ENUM-Spalten der erste Wert aus der ENUM-Definition).

Bis zur Version 8.0.13 konnte eine MySQL-Datenbank nur für die Spalten TIMESTAMP und DATETIME angewiesen werden, den Vorgabewert per Funktion zu erzeugen. Außerdem war bis zu dieser Version für die Spalten TIMESTAMP und DATETIME lediglich die Funktion CURRENT_TIMESTAMP erlaubt, wobei der Name der Funktion in der DEFAULT-Anweisung wie eine Konstante zu notieren war. Die Klammern nach dem dem Funktionsnamen mussten also weggelassen werden (... TIMESTAMP DEFAULT CURRENT_TIMESTAMP ).

Mit der Version 8.0.13 wurde eingeführt, dass Vorgabewerte nicht nur in der "Konstanten-Schreibweise" sondern auch per "expression" zugewiesen werden können. Bei der Zuweisung von Konstanten wird je nach Spaltentyp die Zahl oder die Zeichenkette in der Spaltendeklaration unmittelbar nach der Anweisung DEFAULT notiert (z.B. ... DEFAULT 0 / ... DEFAULT 'abc'). Bei der Zuweisung per "expression" wird der Vorgabewertwert dagegen in runden Klammern nach dem Wort DEFAULT notiert ( ... DEFAULT ('abc') / ... DEFAULT (RAND() + 5 ) ). Nur mit dieser Schreibweise ist es möglich, den Vorgabewert für jede Spalte per Funktion zu erzeugen. Seitdem sind auch erst Vorgabewerte für jene Spalten möglich, deren Typ eine Variante von BLOB oder TEXT ist, bzw. welche JSON-Daten oder geometrische Daten speichern können. Denn für diese Spaltentypen muss der Vorgabewert per Expression zugewiesen werden, selbst wenn der Vorgabewert eine Konstante ist.

zum Anfang