SQL Variablen-Typen @ PS-Trainer

Bool (Wahr / Falsch)
MySQL bietet keine Bool-Typen, sondern an deren Stelle SET-Listen. Eine SET-Variable mit einer Werte-"Liste" aus einem einzigen Wert entspricht genau einer BOOL-Variable und benötigt 1 Byte Speicher pro Datensatz. Allerdings wird dabei nur 1 Bit benutzt.	SET bietet die Möglichkeit, bei gleichem Speicher-Bedarf auch die restlichen 7 Bit zu verwenden. 1 SET-Variable entspricht daher einer Zusammenfassung von 8 BOOL-Variablen. ♦ Details zur SET-Type ↓ auf dieser Seite.

Ganze Zahlen

Rechts die Liste der verfügbaren Daten-Typen.

► In der Praxis sind viele Datenbanken weit über-dimensioniert. Beispielsweise werden für Zahlen im Bereich 0..100 Variable vom Typ INTEGER verwendet. Der Einsatz von TINYINT würde den Speicher-Bedarf auf 1/4 reduzieren.

► Wenn nur positive Werte auftreten, kann man bei gegebener Speicher-Größe den Werte-Bereich mit dem Attribut UNSIGNED verdoppeln. Das trifft zumindest für Primär- und Fremdschlüssel fast immer zu.

► Einfügen illegaler Daten mit INSERT oder UPADTE:
In diesem Fall werden die Daten auf die jeweilige Unter- bzw. Ober-Grenze gesetzt.

► Wenn die Type auf eine mit kleinerem Speicher geändert wird, dann werden die Werte vorhandener Daten auf die neuen Unter- und Obergrenzen geändert.

Type	Speicher	Attribut	Bereich
TINYINT	1 Byte		-128 .. +127
TINYINT	1 Byte	UNSIGNED	0 .. +255
SMALLINT	2 Byte		-32768 .. +32767
SMALLINT	2 Byte	UNSIGNED	0 .. 65535
MEDIUMINT	3 Byte		-2²³ .. +2²³-1 -8 Mio .. +8 Mio
MEDIUMINT	3 Byte	UNSIGNED	0 .. +2²⁴-1 0 .. +16 Mio
INTEGER	4 Byte		-2³¹ .. +2³¹-1 -2 Mia .. +1 Mia
INTEGER	4 Byte	UNSIGNED	0 .. +2³²-1 0 .. +4 Mia
BIGINT	8 Byte		-2⁶³ .. +2⁶³-1
BIGINT	8 Byte	UNSIGNED	0 .. +2⁶⁴-1

► Andere Datenbanken verwenden evtl. andere Typen-Bezeichnungen. Es entspricht

INT1 → TINYINT, INT2 → SMALLINT, INT3 → MEDIUMINT, INT → INTEGER, INT4 → INTEGER, INT8 → BIGINT.

♦ Details zum Thema Zahlen in der Informatik.
♦ Details zu Funktionen ganzer Zahlen

Gleitkomma-Zahlen

Rechts die Liste der verfügbaren Daten-Typen.

► Die Bezeichnung "Stellen" bezieht sich auf die Anzahl verlässlicher Dezimal-Ziffern.

► Die meisten Programme verwenden ohne besondere Vereinbarung DOUBLE. Geringere Genauigkeit (FLOAT) sollten sie nur dann verwenden, wenn bei großer Anzahl von Daten der Speicher-Bedarf zu groß wird.

Type	Speicher	Bereich	Genauigkeit
FLOAT	4 Byte	+/- 3 * 10³⁸	1.2*10^-7 7 Stellen
DOUBLE	8 Byte	+/- 10³⁰⁸	2.2*10^-16 15 Stellen
DECIMAL(M,D)	*	*	*

Die Standard ANSI/ISO SQL-92 Typen NUMERIC und DECIMAL werden verwendet, um Bereich und Genauigkeit individuell festzulegen. In MySQL sind die beiden Typen äquivalent.
Die Argumente M und D bezeichnen Anzahl der Stellen und Dezimal-Stellen.
Das Attribut UNSIGNED setzt die Untergrenze auf 0, ändert jedoch nichts an der Obergrenze.

Beispiel: DECIMAL(4,2) belegt 6 Byte Speicher und begrenzt die Daten auf den Bereich -99.99 .. +99.99 bei einer Genauigkeit (Auflösung) von +/- 0.01

Type	M,D	Sp.	Bereich	Gen.
DECIMAL(M,D)	M>D=0	M+1	+/- (10^M-10^-D)	10^-D M Stellen
DECIMAL(M,D)	M>D>0	M+2	+/- (10^M-D-10^-D)	10^-D M Stellen

♦ Details zu Zahlen-Systemen und zu Gleitkomma-Zahlen nach IEEE-754

Datum und Zeit

► Ein Spezialfall ist die Möglichkeit, unbekannte Angaben von Jahr, Monat und/oder Tag auf 0 zu setzen (Details).
► Illegale Werte (z.B. Monat>12, Minute>59) werden ignoriert, die Daten werden dann nicht eingetragen oder geändert.
► Ein Timestamp dient zum automatischen Speichern von Datum und Zeit der letzten Änderung eines Datensatzes. Eingabe und Änderung dieser Daten ist daher sinnlos.

► Unabhängig davon kann man Datum und Zeit auch in Zahlen-Feldern speichern, z.B. als INTEGER → Timestamp, als → Y1900-Datum oder als → Julianischer Tag.
Speichern als Text (String) ist ungünstig: Hoher Speicherbedarf, man kann damit nicht rechnen, anfällig für Fehler.

Type	Speicher	Bereich
DATE	3 Byte	0000-00-00 .. 9999-12-31
DATETIME	8 Byte	0000-00-00 00:00:00 .. 9999-12-31 23:59:59
TIMESTAMP	4 byte	1970 .. 2106 *
TIME	3 Byte	-838:59:59 .. +838.59:59
YEAR	1 Byte	1901 .. 2155

♦ Details zum Thema Datum und Zeit in SQL allgemein und MySQL speziell.

♦ Details zum Thema Datum und Zeit und zu internen Daten-Formaten.

Text (String)

► CHAR belegt immer den kompletten deklarierten Speicherplatz, unabhängig von der darin enthaltenen Text-Länge.

► Alle anderen Text-(String)-Typen belegen nur so viel Speicher wie der jeweilige Daten-Inhalt erfordert, dazu noch 1..4 Byte für die Codierung der Text-Länge.

► VARCHAR wird für kurze Texte empfohlen. Das Argument M begrenzt die Text-Länge.

Type	Speicher	Länge
CHAR(M)	1<=M<256 Byte	< 256 Byte
VARCHAR(M)	(L<=M) + 1 Byte	< 256 byte
TINYTEXT	L+1 Byte	< 2⁸ = 256 Byte
TEXT	L+2 Byte	< 2¹⁶ Byte = 64 kB
MEDIUMTEXT	L+3 Byte	< 2²⁴ Byte = 16 MB
LONGTEXT	L+4 Byte	< 2³² Byte = 4 GB

Unicode.

Ein lokaler Zeichensatz (z.B. → ISO-8859 / Latin-1) schränkt die Daten auf die eigene Verwendung ein und schließt alle fremden Sonderzeichen aus.
► Wenn Text-Daten international ausgetauscht werden, oder wenn auch andere (slawische, griechische, cyrillische, ...) Sonderzeichen verwendet werden sollen, dann setzt man → UTF-8 codierten → Unicode ein.
Das ist in Europa zumindest bei Namen und Adressen fast immer der Fall.

► UTF-8 Codierung und -Decodierung muss allerdings vom User-Interface bereitgestellt werden. Die Daten selbst sind international eindeutig, sie werden auf jedem Betriebssystem jedes Landes korrekt dargestellt.

► UTF-8 Zeichen können 1..4 Byte Speicherplatz belegen. Die häufigen Standard-(ASCII)-Zeichen brauchen 1 Byte, Umlaute (auch griechische Zeichen u.a.) 2 Byte, diverse Sonderzeichen (z.B. €) 3..4 Byte.
Das bedeutet: Unicode-Zeichen benötigen etwas mehr Speicherplatz als Texte im lokalen (1-Byte)-Zeichensatz.

► Das Attribut Collation bezeichnet den verwendeten Zeichensatz. Wenn ein Zeichen in diesem Zeichensatz nicht definiert ist, wird es als ? gespeichert. Der Text "Größe" wird daher in einem ASCII-Textfeld als "Gr??e" eingetragen.

► Die Collation steuert in erster Linie das Verhalten bei Sortierung. Die Tabelle rechts demonstriert (unverbindlich) das empirisch ermittelte Verhalten der deutschen Umlaute und des scharfen ß bei Sortierung.

Collation	Umlaute	ß
ascii_general	AaOoUu	ss sz
utf8_general	AaäöüÜÖÄoOuU	ß ss sz
utf8_bin	AOUaouäöüÜÖÄ	ss sz ß
cp1250_general	AäaÄÖöOoÜüuU	ß ss sz
cp1250_bin	AOUaouÄÖÜäöü	ss sz ß
latin1_general	AaäÄOoÖöuUÜü	ss sz ß
latin1_de1	AäaÄÖöOoÜüuU	ß ss sz
latin1_de2	AaäÄOoÖöuUÜü	ss ß sz

Binär (Blob)

Moderne Datenbanken können selbstverständlich auch binäre Daten speichern. Das betrifft vorwiegend Bilder, aber auch Audio- und andere Daten.

► Wenn eine Dazenbank keine Binär-Daten speichern kann, oder andere wichtige Gründe dagegen sprechen, dann kann man Binär-Daten mit verschiedenen Verfahren (z.B. BinHex) in Text-Daten umwandeln.
Dabei wird die Datei vergrößert, kann aber wie einfacher Text mit jedem Medium übertragen und in jeder Datenbank gespeichert werden.

Type	Speicher	Länge
TINYBLOB	L+1 Byte	< 2⁸ = 256 Byte
BLOB	L+2 Byte	< 2¹⁶ Byte = 64 kB
MEDIUMBLOB	L+3 Byte	< 2²⁴ Byte = 16 MB
LONGBLOB	L+4 Byte	< 2³² Byte = 4 GB

♦ Details zu den Themen Blob, PHP und Bilder / Binär-Strings.

SQL @ MySQL	Die Datenbank-'Sprache' (Standard Query Language)
Bool-Typ	Zusammenfassung 1 SET = 8..64 BOOL-Variable
Ganze Zahlen	10 Varianten für jeden Zahlen-Bereich.
Gleitkomma-Zahlen	3 Varianten für fixen oder variablen Zahlen-Bereich.
Datum & Zeit	5 spezialisierte Typen
Text (String)	Von einzelnen Zeichen bis zu 4 GB
WerteListe	Auswahl von 1 oder mehreren Elementen aus einer Liste
Binär-Daten	Bilder, Audio und andere "Blob"-Daten bis zu 4 GB

WerteListe
ENUM Ein Feld der Type ENUM kann genau 1 (String)-Element aus einer vor-definierten Liste enthalten. Die Liste wird bereits bei der Feld-Definition festgelegt. Dieses Verhalten wird vom User-Interface meist mit Options-Schaltern (radio-buttons) realisiert: a b c	Die Werte-Liste kann max. 65535 Elemente enthalten. In der Praxis enthält die Liste meist nur einige wenige Werte, da sie sonst unübersichtlich und daher unbrauchbar wird. Intern wird ein ganzzahliger Index gespeichert, welcher auf ein Element der Liste verweist. Der Speicher-Bedarf beträgt daher theoretisch 1..2 Byte, in der Praxis normalerweise nur 1 Byte, ist daher sehr sparsam.
SET Ein Feld der Type SET kann 1 bis 64 (String)-Elemente aus einer vor-definierten Liste enthalten. Die Liste wird ebenfalls mit der Feld-Definition festgelegt. Feld-Werte werden wie Texte (Strings) festgelegt. Dieses Verhalten wird vom User-Interface meist mit Auswahl-Kästchen (checkbox) realisiert: x y z	Bei der Befehls-Eingabe werden die einzelnen Elemente mit ; Strichpunkt getrennt, z.B. "a";"z" Die Texte der Werte-Liste dürfen daher keine ; Zeichen enthalten. Intern wird für jedes Element der Liste ein Bit verwendet. Der Speicher-Bedarf beträgt je nach Länge der Werte-Liste 1..8 Byte, ist daher sehr sparsam.

SQL Variablen-Typen

Am Beispiel von MySQL

Bool (Wahr / Falsch)

Ganze Zahlen

Gleitkomma-Zahlen

Datum und Zeit

Text (String)

WerteListe

ENUM

SET

Binär (Blob)