Vorbereitung auf die MySQL-Zertifizierung

5.1 Data Type Overview

Man kann in Datenbanken unterschiedliche Werte speichern: einfachen Text, Datum, Zahlen von 1 bis 100, Währungen, usw. Abhängig von den zu speichernden Werten muss man den passenden Datentyp für die jeweilige Spalte auswählen. Außerdem ist aus Performance-Gründen wichtig, den passenden Datentyp zu wählen, da die einzelnen Datentypen unterschiedlich viel Speicherplatz in Anspruch nehmen.

Die Datentypen in MySQL kann man in drei Hauptkategorien einteilen:

- numerische Datentypen (Integer, Fließkomma-, Dezimalzahlen, Boolean, etc.)

- String-Typen (einzelne Zeichen, Text, etc.)
- Datums- und Zeittypen (Uhrzeit, Datum)

Beim Anlegen einer Spalte in einer Tabelle muss man neben den Spaltenamen auch den Datentyp festlegen. Hier ein Beispiel:

CREATE TABLE auskunft 
( 
    name VARCHAR(20) CHARACTER SET latin1, 
    email TEXT CHARACTER SET latin1 COLLATE latin1_general_cs 
);

In diesem Beispiel wird eine neue Tabelle namens auskunft erstellt. Diese Tabelle enthält zwei Spalten name und email, welche als VARCHAR mit 20 Zeichen und TEXT definiert werden.

5.2 Numeric Data Types

5.2.1 Integer Data Types

Die wichtigsten Integer-Datentypen sind TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT, welche Ganzzahlen mit unterschiedlichem Speicherbedarf darstellen.

Der Speicherbedarf ist vom gewählten Datentyp abhängig, niemals von der Spaltengröße des einzelnen Datentyps. Das heißt, dass INT(4) nicht den halben Speicherplatz von INT(8) in Anspruch nimmt. Die Zahl in Klammern bedeutet, wie viele Ziffern bei MySQL-Abfragen angezeigt werden. Bei INT(4) werden nur die ersten vier Stellen angezeigt, auch wenn man beispielsweise eine fünfstellige Zahl gespeichert hat.

Um die Tabellen einer MySQL-Datenbank möglichst platzsparend zu erstellen, muss man sich im Klaren sein, welchen Wertebereich die zu speichernden Zahlen haben werden. Um beispielsweise die Monate zu speichern, reicht es vollkommen aus, die Spalte als TINYINT (Wertebereich: 0-255, 1 Byte Speicherplatz) zu deklarieren. Der Datentyp INT wäre (Wertebereich: 0 bis 4294967295, 4 Bytes Speicherplatz) maßlos übertrieben.

TINYINT[(M)] [UNSIGNED] [ZEROFILL]

Ganzzahl mit kleinem Wertebereich (-128 bis 127 oder 0 bis 255). Speicherbedarf: 1 byte

BOOL, BOOLEAN

Entspricht TINYINT(1). Mögliche Werte: wahr, falsch

SMALLINT[(M)] [UNSIGNED] [ZEROFILL]

Ganzzahl mit kleinem Wertebereich (-32768 bis 32767 oder 0 bis 65535). Speicherbedarf: 2 bytes

MEDIUMINT[(M)] [UNSIGNED] [ZEROFILL]

Mittelgroße Ganzzahl. Wertebereich (-8388608 bis 8388607 oder 0 und 16777215). Speicherbedarf: 3 bytes

INT[(M)] [UNSIGNED] [ZEROFILL] oder INTEGER[(M)] [UNSIGNED] [ZEROFILL]

Ganzzahl. Wertebereich (-2147483648 bis 2147483647 oder 0 bis 4294967295). Speicherbedarf: 4 bytes

BIGINT[(M)] [UNSIGNED] [ZEROFILL]

Große Ganzzahl. Wertebereich (-9223372036854775808 bis 9223372036854775807 oder 0 bis 18446744073709551615). Speicherbedarf: 8 bytes .

5.2.2 Floating-Point Data Types

Unter Fließkomma-Zahlen versteht man Zahlen, die aus einem Integer-Teil (die Zahl vor dem Komma) und einem Bruch (die Zahl nach dem Komma) bestehen. Der Fließkomma-Datentyp ist etwas problematisch, da viele Zahlen nicht 100% genau dargestellt werden können und es daher zu Rundungs- und Rechenfehlern kommen kann.

Es gibt zwei Floating-Point-Datentypen: FLOAT steht für „single-precision“ und DOUBLE für „double-precision“. Ähnlich wie bei INT kann die Anzahl der Dezimal- und Bruchstellen hinter dem Komma (D,M) begrenzt werden. Werden diese Werte weggelassen, werden Standardwerte verwendet.

FLOAT[(M,D)] [UNSIGNED] [ZEROFILL]

Kleine Fließkommazahl. Wertebereich (-3.402823466E+38 bis -1.175494351E-38 oder 1.175494351E-38 bis 3.402823466E+38). M steht für die Anzahl der Dezimalstellen, D für die Stellen hinter dem Dezimalpunkt. Eine Fließkommazahl mit einfacher Genauigkeit ist auf etwa sieben Dezimalstellen genau.

DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL]

Fließkommazahl Wertebereich (-1.7976931348623157E+308 bis -2.2250738585072014E-308 oder 0 und der Bereich zwischen 2.2250738585072014E-308 und 1.7976931348623157E+308)

DOUBLE PRECISION

Entspricht DOUBLE

FLOAT(p) [UNSIGNED] [ZEROFILL]

Fließkommazahl. p steht für die Genauigkeit in Bit. Abhängig davon, wie hoch die Genauigkeit hinter dem Komma ist, wählt MySQL den richtigen Datentyp (FLOAT, DOUBLE)

Beispiel:

CREATE TABLE rechner 
( 
    zahlen FLOAT(10,3), 
    zahlenGenauer DOUBLE(15,7)
);

Zeile 1: Single-Precision-Spalte, 10 Nachkomma-, 3 Dezimalstellen
Zeile 2: Double-Precision-Spalte, 15 Nachkomma-, 7 Dezimalstellen

5.2.3 Fixed-Point Data Types

Der Fixed-Point Datentyp ist DECIMAL. Dieser besteht wie FLOAT und DOUBLE aus Dezimal- und Nachkommastellen. Bei DECIMAL werden die Zahlen in der vorgegebenen Anzahl an Dezimal- und Nachkommastellen gespeichert. Dies führt zwar dafür, dass DECIMAL weniger performant wie FLOAT oder DOUBLE ist, dafür aber genauer ist.

Daher wird DECIMAL beispielsweise für Kalkulationen verwendet, wo genaue Berechnungen erforderlich sind.

Wichtig: Bei DECIMAL ist die Speichergöße abhängig von der festgelegten Anzahl der Dezimal- und Nachkommastellen!

DECIMAL[(M[,D])] [UNSIGNED] [ZEROFILL]

Gepackte „exakte“ Festkommazahl. M steht für die Anzahl der Dezimalstellen, D für die Anzahl der Stellen hinter dem Komma. Wenn D 0 ist, haben die Werte keinen Dezimalpunkt und keine Nachkommastellen.

DEC[(M[,D])] [UNSIGNED] [ZEROFILL],

NUMERIC[(M[,D])] [UNSIGNED] [ZEROFILL],

FIXED[(M[,D])] [UNSIGNED] [ZEROFILL]

Entsprechen alle DECIMAL

Beispiel:

CREATE TABLE waehrung 
( 
    us_dollar DECIMAL(10,2), 
);

Erklärung: Datentyp mit 10 Dezimal- und 2 Nachkommastellen

5.3 The BIT Data Type

Um Bitwerte (Beispiel: 0100101) zu speichern, nutzt man den Datentyp BIT. In Klammern gibt man an, wie viele Bits gespeichert werden sollen. Um Bit-Werte direkt einzutragen, muss man die Syntax b'value' verwenden, also beispielsweise INSERT INTO t1 SET spalte1 = b'0101001' WHERE id = "5"

BIT[(M)]

Bitfeldtyp. M steht für die Anzahl von Bits (1 bis 64)

5.4 String Data Types

CHAR = Zeichenkette mit fester Länge, mehrere unterschiedliche Zeichen (non-binary) möglich.

VARCHAR = Zeichenkette mit variabler Länge, mehrere unterschiedliche Zeichen (non-binary) möglich.

TEXT = Zeichenkette mit variabler Länge, mehrere unterschiedliche Zeichen (non-binary) möglich.

BINARY = Binäre Zeichenkette mit fester Länge

VARBINARY = Binäre Zeichenkette mit variabler Länge

BLOB = Binäre Zeichenkette mit variabler Länge

ENUM = Aufzählung

[NATIONAL] CHAR(M) [BINARY | ASCII | UNICODE]

Char ist eine Zeichenkette fester Länge. Leere Stellen bei der Speicherung werden automatisch aufgefüllt. M steht für die Länge der Zeichenkette (Wert 0-255).

CHAR

Einzelnes Zeichen. Dieser Typ ist synonym zu CHAR(1).

[NATIONAL] VARCHAR(M) [BINARY]

VARCHAR steht für VARYING CHARACTER und ist eine Zeichenkette mit variabler Länge, wobei M für die maximale Anzahl (0 bis 65.535) steht. Das Attribut BINARY sorgt dafür, dass die Spalte binär sortiert wird (Sortierung nach numerischen Zeichenwerten)

BINARY(M)

Ähnlich dem Datentyp CHAR, speichert aber Strings aus binären Bytes statt nichtbinärer Zeichen-Strings.

VARBINARY(M)

Ähnlich VARCHAR, speichert aber Strings aus binären Bytes statt nichtbinärer Zeichen-Strings.

TINYBLOB

Eine BLOB-Spalte mit einer Maximallänge von 255 (28 – 1) Byte.

 

TINYTEXT

Eine TEXT-Spalte mit einer Maximallänge von 255 (28 – 1) Zeichen.

BLOB[(M)]

Eine BLOB-Spalte mit einer Maximallänge von 65.535 (216 – 1) Byte.

TEXT[(M)]

Eine TEXT-Spalte mit einer Maximallänge von 65.535 (216 – 1) Zeichen.

MEDIUMBLOB

Eine BLOB-Spalte mit einer Maximallänge von 16.777.215 (224 – 1) Byte.

MEDIUMTEXT

Eine TEXT-Spalte mit einer Maximallänge von 16.777.215 (224 – 1) Zeichen.

 

LONGBLOB

Eine BLOB-Spalte mit einer Maximallänge von 4.294.967.295 (232 – 1) Byte (4 Gbyte)

LONGTEXT

Eine TEXT-Spalte mit einer Maximallänge von 4.294.967.295 (232 – 1) Zeichen.

ENUM('value1','value2',...)

Eine Auflistung. Es handelt sich dabei um ein String-Objekt, das aus der Liste der Werte 'value1', 'value2', ..., NULL und einem speziellen Fehlerwert '' genau einen Wert auswählt. Eine ENUM-Spalte kann maximal 65.535 verschiedene Werte aufweisen. ENUM-Werte werden intern als Integers dargestellt.

SET('value1','value2',...)

Eine Menge. Es handelt sich um ein String-Objekt, dass null oder mehr Werte haben kann, die jeweils aus der Werteliste 'value1', 'value2', ... stammen. Eine SET-Spalte kann maximal 64 Mitglieder haben. SET-Werte werden intern als Integers dargestellt.

5.4.1 Character Set Support

Character Set – Der Zeichensatz

Der Zeichensatz legt fest, welche Schriftzeichen unterstützt werden. Auf der Welt gibt es eine Vielfalt an Sprachen und viele besitzen sogar eigene Schriftzeichen. Aus diesem Grund gibt es bei MySQL einige Zeichensätze (character sets), um beispielsweise chinesische Schriftzeichen oder die deutschen Sonderzeichen wie ß speichern und anzeigen zu können.

MySQL unterstützt standardmäßig den Latin1-Zeichensatz (auch bekannt als ISO-8859-1), welcher für westeuropäischen Länder genutzt wird. Hier fehlen aber einige Zeichen, wie beispielsweise ä,ö,ü aus der deutschen Sprache. In letzterem Fall sollte man ISO-8859-15 wählen. Beim Latin1-Zeichensatz belegt jedes Zeichen 1 Byte.

Collation – Sortierreihenfolge

Die offizielle Bezeichnung Collation steht bei Datenbanken für die Sortierreihenfolge. Mit Collation legt man also fest, wie die Strings durch die Anweisungen SELECT, ORDER BY oder GROUP BY sortiert werden.

Einer der wichtigsten Festlegung durch die Sortierreihenfolge (Collation) ist, ob die Groß-/Kleinschreibung beachtet wird. Wird die Groß-/Kleinschreibung beachtet, so spricht man von case sensitiv. Im anderen Fall spricht man von case insensitiv.

Des Weiteren kann es entscheidend sein, wie Varianten eines bestimmten Zeichens gehandhabt werden. Wie sollen beispielsweise „a“, „á“ und „à“ sortiert werden? Werden diese Zeichen als dasselbe behandelt, spricht man von accent insensitiv, andernfalls von accent sensitiv.

mysql> SHOW COLLATION LIKE 'latin1%';
+-------------------+---------+----+---------+----------+---------+
| Collation         | Charset | Id | Default | Compiled | Sortlen |
+-------------------+---------+----+---------+----------+---------+
| latin1_german1_ci | latin1  |  5 |         | Yes      |       1 | 
| latin1_swedish_ci | latin1  |  8 | Yes     | Yes      |       1 | 
| latin1_danish_ci  | latin1  | 15 |         | Yes      |       1 | 
| latin1_german2_ci | latin1  | 31 |         | Yes      |       2 | 
| latin1_bin        | latin1  | 47 |         | Yes      |       1 | 
| latin1_general_ci | latin1  | 48 |         | Yes      |       1 | 
| latin1_general_cs | latin1  | 49 |         | Yes      |       1 | 
| latin1_spanish_ci | latin1  | 94 |         | Yes      |       1 | 
+-------------------+---------+----+---------+----------+---------+
8 rows in set (0.00 sec)

Ein Zeichensatz kann mehrere Collations besitzen, wie man anhand der oben stehenden MySQL-Ausgabe für den Latin1-Zeichensatz sehen kann. _ci steht für case insensitiv, _cs für case sensitiv und _bin für Binary

Unterschied non-binary-string und binary-string

non-binary-strings sind CHAR, VARCHAR und TEXT. Diese werden intern als „richtige Zeichen“ gespeichert, welche zu einem bestimmten Zeichensatz gehören. Die Sortierung ist abhängig von der Kollation. Non-binary-strings werden dann benutzt, wenn man Zeichenketten speichern möchte, also beispielsweise geschriebene Texte.

binary-strings sind BINARY, VARBINARY, BLOB. Diese Datentypen werden intern als binäre Zeichen gespeichert, d.h. sie enthalten byte- anstatt zeichenbasierter Strings. Jeder Buchstabe, jedes Zeichen wird intern als Binärzahl gespeichert, wobei jede Binärzahl als Synonym für genau ein Textzeichen steht. Somit entsteht beispielsweise beim Sortieren der Eindruck, dass binary-strings case-sensitiv sind. Das liegt aber daran, dass bsp. a intern anders gespeichert wird als A.

binary-strings besitzen keinen Zeichensatz. Sie werden eingesetzt, wenn man „rohe Daten“ wie Bilder oder komprimierte Daten speichern möchte.

Beispiel:

CREATE TABLE login
(
	login_user	CHAR(32) CHARACTER SET latin1;
	password	CHAR(32) CHARACTER SET latin1 COLLATE latin1_general_cs;
	picture	MEDIUMBLOB;
);

5.4.2 Non-Binary String Data Types: CHAR, VARCHAR, TEXT

Datentypen des Typs "Nicht-Binäre Zeichenketten" (non-binary string) sind Datentypen, bei den Zeichen intern auch als Zeichen gespeichert werden und die einen Zeichensatz (character set) und eine Sortierreihenfolge (collation) besitzen.

Non-binary strings unterscheiden sich in der maximalen Länge der Zeichenkette und der Art und Weise, wie Leerzeichen gehandhabt werden.

CHAR(M)

Bei Char spricht man von einem "fixed-length type", was bedeutet, dass eine Zeichenkette immer dieselbe Größe und Länge hat. Definiert man beispielsweise eine Spalte mit CHAR(30) und speichert in dieser Spalte einen Wert mit 10 Zeichen, so werden die restlichen Zeichen mit Leerzeichen aufgefüllt.

==> Zeichenkette mit fester Länge und gleich-großem Speicherplatz. Automatisches Auffüllen mit Leerzeichen. Länge kann von 0-255 definiert werden.

Beispiel: Bei einem Single-Character-Set muss MySQL bei CHAR(10) 10 Byte pro Wert reservieren. Bei UTF8 (3 byte pro Zeichen) wären es sogar 30 Byte! Dieser Speicherplatz wird selbst dann benötigt, wenn ein leerer Zeichenstring gespeichert wird.

VARCHAR(M)

Hierbei handelt es sich um eine Zeichenkette mit variabler Länge. Das bedeutet, das wirklich nur die gewünschten Zeichen gespeichert und bei kürzeren Zeichenketten keine Zeichen aufgefüllt werden werden. Aus diesem Grund ist die Speichergröße variabel. Bei VARCHAR ergibt sich die Speichergröße aus der Länge der Zeichenkette zuzüglich 1-2 Byte für die Angabe der Zeichenkettelänge (zusätzliche Information).

==> Zeichenkette mit variabler Länge und unterschiedlichem Speicherplatz. Länge kann von 0-65.535 definiert werden.

Wert	CHAR(4)	Speicherplatz	VARCHAR(4)	Speicherplatz
''	' '	4 Byte	' '	1 Byte
'ab'	'ab '	4 Byte	'ab '	3 Byte
'abcd'	'abcd'	4 Byte	'abcd'	5 Byte
'abcdefgh'	'abcd'	4 Byte	'abcd'	5 Byte

Beispiel: Wenn eine Spalte als VARCHAR(10) deklariert ist 10 Single-Byte-Zeichen gespeichert werden, so werden 10 Bytes + 1 Byte benötigt. Dieser Speicher wird automatisch angepasst, wenn bsp. Triple-Byte-Zeichen (UTF8) verwendet werden. Hier werden 30 Bytes + 1 Byte benötigt.

TEXT

Hier gibt es gleich vier unterschiedliche Typen (TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT), bei den Speichergröße und die maximale Zeichenlänge vorgeschrieben sind. Im Prinzip handelt es sich hier um einen VARCHAR-Typ, bei dem Zeichenlänge fest definiert und nicht selbst ausgewählt werden kann. TEXT ist also ebenfalls eine Zeichenkette mit variabler Länge, der benötigte Speicherplatz ist also davon abhängig, wie viel Text nun wirklich gespeichert wird.

Übersicht Datentypen/Speicherplatz nicht-binärer Zeichenketten:

CHAR(M) = Speicher: M Zeichen, Max. Länge: 255 Zeichen

VARCHAR(M) = Speicher: Anzahl Zeichen + 1 od. 2 Byte, Max. Länge: 65.535 Zeichen

TINYTEXT = Speicher: Anzahl Zeichen + 1 Byte, Max. Länge: 255 Zeichen

TEXT = Speicher: Anzahl Zeichen + 2 Byte, Max. Länge: 65.535 Zeichen

MEDIUMTEXT = Speicher: Anzahl Zeichen + 3 Byte, Max. Länge: 16.777.215 Zeichen

LONGTEXT = Speicher: Anzahl Zeichen + 4 Byte, Max. Länge: 4.294.967.295 Zeichen

5.4.3 Binary String Data Types: BINARY, VARBINARY, BLOB

BINARY, VARBINARY und BLOB sind binäre Zeichenketten und equivalent zu den nicht-binär Zeichenketten CHAR, VARCHAR und TEXT. Wie bereits besprochen werden bei binären Zeichenketten die einzelnen Zeichen als Binärdaten gespeichert. Jedes Zeichen hat einen eindeutigen Binärwert zugeordnet, so dass jedes Zeichen, sei es bsp. groß oder klein geschrieben, eindeutig identifzierbar ist. Aus diesem Grund haben BINARY, VARBINARY und BLOB keinen Zeichensatz (character set) und keine Sortierreihenfolge (collation). Sortierung und Vergleiche basieren in diesem Fall auf den numerischen Werten der Bytes in den Werten

BINARY(M)

Genau wie CHAR ist die Zeichenlänge genau vorgegeben und eventuell fehlende Zeichen werden automatisch mit Leerzeichen aufgefüllt, so dass die Speichergröße stehts gleich groß ist. Der Füllwert ist 0x00 (das Nullbyte)

==> Binäre Zeichenkette mit fester Länge und gleich-großem Speicherplatz. Automatisches Auffüllen mit Leerzeichen. Länge kann von 0-255 definiert werden.

Hinweis: Da fehlende Zeichen automatisch hinzugefügt werden, sollte man diesen Datentyp nicht für Anwendungen benutzen, die damit Probleme haben, beispielsweise Passwortfelder.

VARBINARY(M)

VARBINARY ist das binäre Synonym zu VARCHAR. Hier werden die fehlenden Zeichen nicht durch Leerzeichen gefüllt und die Speicherplatzgröße ist abhängig davon, wie viele Zeichen letztendlich gespeichert werden.

BLOB

Wie auch bei TEXT gibt es beim binären Synonym vier verschiedene Typen mit vorgegebener Zeichenlänge und Speichergröße: TINYBLOB, BLOB, MEDIUMBLOB und LONBLOB. Diese Datentypen unterscheiden sich darin, wie viele Zeichen maximal gespeichert werden können und wie viel Speicherplatz für die Zusatzinformationen (1-4 Byte) benötigt werden.

Übersicht Datentypen/Speicherplatz binärer Zeichenketten:

BINARY(M) = Speicher: M Zeichen, Max. Länge: 255 Zeichen

VARBINARY(M) = Speicher: Anzahl Zeichen + 1 od. 2 Byte, Max. Länge: 65.535 Zeichen

TINYBLOB = Speicher: Anzahl Zeichen + 1 Byte, Max. Länge: 255 Zeichen

BLOB = Speicher: Anzahl Zeichen + 2 Byte, Max. Länge: 65.535 Zeichen

MEDIUMBLOB = Speicher: Anzahl Zeichen + 3 Byte, Max. Länge: 16.777.215 Zeichen

LONGBLOB = Speicher: Anzahl Zeichen + 4 Byte, Max. Länge: 4.294.967.295 Zeichen

5.4.4 The ENUM and SET Types

ENUM

ENUM steht enumeration, was ins Deutsche übersetzt Aufzählung bedeutet. ENUM wird oftmals dafür benutzt, Speichervorgänge effizienter zu gestalten. Das Prinzip ist einfach: man definiert in einer Spalte vom Typ ENUM mehrere Werte ('Members'), so dass zwei oder mehrere Auswahlen vorhanden sind. MySQL speichert diese Werte/Members intern als Zahlen beginnend bei 1 bis n. Jeder Wert hat nun intern eine eindeutige Zahl zugewiesen bekommen, so dass MySQL intern immer nur die Zahl und nicht den kompletten String speichern muss. Dieses wirkt sich natürlich positiv auf die Performance aus.

Folgendes Beispiel soll den ENUM-Datentyp näher erläutern: stellen wir uns vor, wir möchten eine Tabelle erstellen, in der Städte gespeichert werden sollen. Zu jeder Stadt soll das jeweilige Land als Zusatzinformation angegeben werden. Anstatt für die Spalte "Land" den Datentyp VARCHAR oder TINYTEXT zu verwenden und das Land jedes Mal als Zeichenkette zu speichern, können wir die Spalte als ENUM mit vordefinierten Werten erstellen:

CREATE TABLE staedte
(
	stadt	CHAR(30),
	land	ENUM('Deutschland', 'Frankreich', 'Italien', 'Schweiz')
);

Beim Speichern wird die normale Syntax wie gewohnt verwendet:

INSERT INTO staedte (stadt, land) VALUES('Berlin', 'Deutschland');

Es scheint auf den ersten Blick keinen Unterschied zu geben, aber intern wird 'Deutschland' nicht als String, sondern als Zahl 1 (für das erste Mitglied, Frankreich wäre Zahl 2) gespeichert. ENUM wird oftmals auch dafür benutzt, um TRUE/FALSE Werte zu speichern.

ENUM kann bis zu 65.535 Werte/Members besitzen. Der Speicherverbrauch ist unterschiedlich:

 

bis 255 Members = 1 Byte pro Member

256 bis 65.535 = 2 Bytes pro Member

SET

SET entspricht vom Prinzip her dem Datentyp ENUM. In oben gewähltem Beispiel ist logischerweise jeder Stadt genau ein Land zugewiesen. Was ist aber, wenn man mehre Dinge etwas zuordnen kann? Dann ist ENUM der falsche und SET der richtige Datentyp. Mit SET legt man eine gewünschte Menge an Werten an. Nun ist es aber möglich, beim Speichern mehrere Werte aus dieser Menge auszuwählen und zu speichern.

Zum besseren Verständnis soll folgendes Beispiel dienen: eine Krankheit äußerst sich durch bestimmte Symptome wieder. Krankheiten können dieselben Symptome haben und sich durch einige andere Symptome unterscheiden. Letztendlich muss eine Krankheit durch Ihre Symptome definiert werden und das sind in der Regel mehr als ein Symptom.

CREATE TABLE krankheiten
(
	krankheit	CHAR(30),
	symptome	SET('Heiserkeit', 'Übelkeit', 'Juckreiz', 'Fieber')
)

Nun können wir die Krankheiten definieren:

INSERT INTO krankheiten (krankheit, symptome) VALUES ('Grippe', 'Fieber, Übelkeit')
INSERT INTO krankheiten (krankheit, symptome) VALUES ('Husten', 'Heiserkeit')

Ähnlich wie bei ENUM liegt der Vorteil bei SET in der Art und Weise, wie die Werte gespeichert werden. Auch bei SET hat jeder Eintrag einen individuellen Zahleneintrag. Das Problem bei SET ist, dass ein Wert nicht nur einen Member, sondern gleich mehrere zugewiesen werden können. Mit einfachen Dezimalzahlen kann man dieses nicht darstellen, aus diesem Grund verwendet man Binärzahlen:

Member 1 = Heiserkeit = Dezimalwert 1 = Binär: 0001

Member 2 = Übelkeit = Dezimalwert 2 = Binär: 0010

Member 3 = Juckreiz = Dezimalwert 4 = Binär: 0100

Member 4 = Fieber = Dezimalwert 8 = Binär: 1000

Aus diesen Binarzahlen kann man nun genau festlegen, welche Members ein Wert hat. Das erste Beispiel von oben hätte die Dezimalzahl 10 oder als Binärzahl 1010:

1000 (Fieber) + 0010 (Übelkeit) = 1010

5.5 Temporal Data Types

Datentypen für Datum und Uhrzeit ermöglichen das Speichern zeitlicher Werte. Bei diesen Datentypen handelt es sich um DATETIME, DATE, TIMESTAMP, TIME und YEAR, welche unterschiedliche Datums- und Zeitangaben über verschiedene Zeiträume speichern. Des Weiteren unterscheiden sich diese Datentypen in der Speichergröße, welchen jeder Wert beansprucht.

DATE (3 Bytes) = '1000-01-01' bis '9999-12-31'

TIME (3 Bytes) = '-838:59:59' bis '838:59:59'

DATETIME (8 Bytes) = '1000-01-01 00:00:00' bis '9999-12-31 23:59:59'

TIMESTAMP (4 Bytes) = '1970-01-01 00:00:00' bis '2037-12-01 23:59:59'

YEAR (1 Byte) = 1901-2155 (YEAR(4)) oder 1970-2069(YEAR(2))

Jeder dieser Datentypen hat einen so genannten Nullwert, der dann automatisch verwendet bzw. gespeichert wird, wenn ein gar kein oder ein fehlerhaftes Datum gespeichert werden soll:

Datentyp = Funktion = „Nullwert“

DATETIME = YYYY-MM-DD hh:mm:ss = '0000-00-00 00:00:00'

DATE = YYYY-MM-DD = '0000-00-00'

TIMESTAMP = YYYY-MM-DD hh:mm:ss = '0000-00-00 00:00:00'

TIME = hh:mm:ss = '00:00:00'

YEAR = YYYY = 0000

MySQL verwendet den ISO-8601-Standard für das Speichern von Datums- und Uhrzeitwerten. Aus diesem Grund sollte man Anfragen immer nach folgendem Schema stellen: YYYY-MM-DD bzw. hh:mm:ss

Folgendes ist aber erlaubt:

- Führende Nullen können weggelassen werden, also '2008-1-26' entspricht '2008-01-26'.

- Der Trennstrich - kann durch ein anderes Zeichen (/) ersetzt werden

- Kurzform der Jahresansicht ist erlaubt, also 70-03-23 entspricht 1970-03-23. Achtung: 70-99 entspricht 1970-1999 und 00-69 entspricht 2000-2069

- Auch bei der Uhrzeit können führende Nullen weggelassen werden

Um die Anzeige des Datums und der Uhrzeit zu beeinflussen, kann man die Funktionen DATE_FORMAT() und TIME_FORMAT() verwenden.

5.5.1 The DATE, TIME, DATETIME and YEAR Data Typs

Die einzelnen Datentypen erklären sich anhand oben genannter Aufzählung von selbst. Interessant ist zu wissen, welche Möglichkeiten es gibt, Datum- und Uhrzeit zu speichern. Hier gibt es die unterschiedlichsten Schreibweisen:

- '2008-12-25 15:30:45' kann folgendermaßen geschrieben werden: '2008.12.25 15+30+45' oder '2008/12/25 15*30*45' oder '2008@12@25 15^30^45' oder 20081225153045

Die Werte sollten eine Länge von 6, 8, 12 oder 14 haben. Hat die Zahl 8 oder 14 Stellen, so werden die Formate YYYYMMDD bzw. YYYYMMDDHHMMSS angenommen. Bei sechs oder zwölf Stellen, dann werden die Formate YYMMDD bzw. YYMMDDHHMMSS angenommen.

5.5.2 The TIMESTAMP Data Type

Der Datentyp TIMESTAMP ähnelt dem Datentyp DATETIME. Beide werden dazu benutzt, um Datums- und Uhrzeitangaben zu speichern. Sie unterscheiden sich aber in dem Wertebereich. Außerdem bringt der TIMESTAMP Datentyp weitere, nützliche Funktionen mit. Wie der Name schon sagt, handelt es sich um einen Zeitstempel und daher wird TIMESTAMP dazu verwendet, Zeitangaben bei Änderung, Erstellung oder Löschung von Datensätzen zu speichern.

Wenn man in MySQL zwei Spalten des Typs TIMESTAMP anlegt, so erhält die erste Spalte automatisch den Default-Wert CURRENT_TIMESTAMP, so dass bei einem neuen Datensatz automatisch die aktuelle Uhrzeit/Datum gespeichert wird.

mysql> CREATE TABLE zeit (
    -> tbl01 TIMESTAMP,
    -> tbl02 TIMESTAMP,
    -> txt TINYTEXT );

mysql> DESCRIBE zeit;
+-------+-----------+------+-----+---------------------+-------+
| Field | Type      | Null | Key | Default             | Extra |
+-------+-----------+------+-----+---------------------+-------+
| tbl01 | timestamp | NO   |     | CURRENT_TIMESTAMP   |       | 
| tbl02 | timestamp | NO   |     | 0000-00-00 00:00:00 |       | 
| txt   | tinytext  | YES  |     | NULL                |       | 
+-------+-----------+------+-----+---------------------+-------+

Nach Anlegen eines neuen Datensatzes wird die aktuelle Zeit automatisch gesetzt. Dieses geschieht nur beim Anlegen, nicht wenn sich ein Datensatz ändert!

mysql> INSERT INTO zeit (txt) VALUES ('Hier steht text...');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM zeit;
+---------------------+---------------------+--------------------+
| tbl01               | tbl02               | txt                |
+---------------------+---------------------+--------------------+
| 2008-02-19 13:58:50 | 0000-00-00 00:00:00 | Hier steht text... | 
+---------------------+---------------------+--------------------+

Hier sieht man sehr gut, dass automatisch nach dem Anlegen des Datensatzes das aktuelle Datum gespeichert wird.

Es ist aber auch möglich, dass ein Feld nur bei Änderungen aktualisiert wird. Dazu verwendet man beim Anlegen das Attribut ON UPDATE CURRENT_TIMESTAMP.

MySQL zeigt eine Fehlermeldung an, wenn man beides miteinander verbindet, also DEFAULT CURRENT_TIMESTAMP mit ON UPDATE CURRENT_TIMESTAMP. Wenn man beides nutzen möchte, muss man die Tabelle folgendermaßen erstellen:

mysql> CREATE TABLE zeit (
    -> tbl01 TIMESTAMP DEFAULT 0,
    -> tbl02 TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    -> txt TINYTEXT );

Beim Anlegen des Datensatzes (INSERT, REPLACE) wird nur Spalte tbl01 aktualisiert. Wird ein Datensatz geändert (UPDATE), ändert sich Spalte tbl02:

mysql> UPDATE zeit SET txt="Text (aktualisiert)";
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM zeit;
+---------------------+---------------------+---------------------+
| tbl01               | tbl02               | txt                 |
+---------------------+---------------------+---------------------+
| 2008-02-19 14:06:38 | 2008-02-19 14:07:33 | Text (aktualisiert) | 
+---------------------+---------------------+---------------------+
1 row in set (0.00 sec)

5.6 Column Attributes

Beim Anlegen einer neuen Spalte kann man optional weitere Attribute festlegen. Somit kann man beeinflussen, wie MySQL die Spalte "behandelt". Attribute können sein, welche Default-Werte, welcher Zeichensatz oder welche Sortierreihenfolge (Collation) verwendet werden sollen. Die Angabe von Attributen ist nicht zwingend erforderlich. Welche Attribute man nutzen kann ist abhängig von dem verwendetem Datentyp.

CREATE TABLE irgendwas
(
	spalte1 INT UNSIGNED NOT NUL,
	spalte2 CHAR(30) CHARACTER SET utf8,
	spalte3 DATETIME DEFAULT '2008-12-01 00:30:01'
);

Spalte1 = Datentyp INT, keine negativen Werte möglich, kein Nullwerte möglich

Spalte2 = Datentyp CHAR, verwendeter Zeichensatz ist utf8

Spalte3 = Datentyp DATETIME mit vorgegebenen Default-Wert

5.6.1 Numeric Column Attributes

Folgende Attribute können bei nummerischen Datentypen wie INT oder FLOAT angegeben werden:

UNSIGNED = Der Wertebereich ist nur für positive Zahlen gültig. Negative Zahlen sind nicht erlaubt.

ZEROFILL = Leere oder fehlende Zahlen werden automatisch mit Nullen aufgefüllt. Beispiel INT(4) ZEROFILL: Speichert man die Zahlen 5, 35 und 4523, so werden diese als 00005, 00035 und 04523 gespeichert und angezeigt.

AUTO_INCREMENT = Wird dazu benutzt, um eindeutige Zahlen einem Datensatz zuzuweisen. Dabei wird bei einem neuen Eintrag der Eintrag um eins erhöht. Dieses wird beispielsweise bei einem Index verwendet, so dass jeder Datensatz über eine ID erreichbar ist.

5.6.2 String Column Attributes

Folgende Attribute können bei string-Datentypen wie CHAR oder TEXT angegeben werden:

CHARACTER SET = Festlegen des Zeichensatzes (utf8, latin1, etc)

COLLATE = Sortierreihenfolge festlegen. Dieser muss vom Zeichensatz unterstützt werden.

BINARY = Binäre Sortierreihenfolge kann bei nicht-binären Zeichensätzen (CHAR, TEXT, etc) aktiviert werden

mysql> SHOW CHARACTER SET;
+----------+-----------------------------+---------------------+
| Charset  | Description                 | Default collation   |
+----------+-----------------------------+---------------------+
| big5     | Big5 Traditional Chinese    | big5_chinese_ci     |
| dec8     | DEC West European           | dec8_swedish_ci     |
| koi8r    | KOI8-R Relcom Russian       | koi8r_general_ci    |
| latin1   | cp1252 West European        | latin1_swedish_ci   |

5.6.3 General Column Attributes

Folgende Attribute können auf alle Datentypen angewandt werden:

NULL od. NOT NULL = Legt fest, ob eine Spalte NULL-Werte enthalten darf oder nicht (NOT NULL). Der NULL-Wert bedeutet „Keine Daten“. Folgende Aussage aus dem offiziellen MySQL-Handbuch soll dieses Attribut näher erläutern.

NULL-Werte stiften bei SQL-Neulingen immer wieder Verwirrung, da diese oft denken, NULL sei dasselbe wie der leere String ''. Doch das ist nicht der Fall. Die folgenden Anweisungen sind beispielsweise völlig verschieden:

mysql> INSERT INTO my_table (phone) VALUES (NULL);
mysql> INSERT INTO my_table (phone) VALUES ('');

Beide Anweisungen fügen einen Wert in die phone-Spalte ein, die erste allerdings einen NULL-Wert und die zweite einen leeren String. Die erste Eingabe bedeutet so viel wie „Telefonnummer unbekannt“ und die zweite bedeutet „Die Person hat kein Telefon und somit keine Telefonnummer“.

Beispiel:
CREATE TABLE test ( abc INT NOT NULL);

DEFAULT = Mit diesem Attribut kann man einen Standard-Wert festlegen, der dann automatisch gespeicht wird, wenn beim Einfügen eines neuen Datensatzes kein Wert angegeben wird.

- DEFAULT kann bei allen Datentypen außer TEXT und BLOB verwendet werden

- Bei einer NOT NULL Spalte ist der Default-Wert NULL nicht erlaubt

- DEFAULT-Werte müssen innerhalb des Wertebereichs sein.

UNIQUE

Erzeugt einen Index, bei dem ein Wert nur einmal vorkommen darf

PRIMARY KEY

Wird in der Regel dazu benutzt, einen eindeutigen Schlüssel (ID) für einen Datensatz zu generieren. Somit kann es nicht zu Verwechslungen kommen.

Beispiel: 
CREATE TABLE test ( abc INT NOT NULL PRIMARY KEY);
CREATE TABLE test ( abc INT NOT NULL, PRIMARY KEY (abc));

5.7 Using the AUTO_INCREMENT Column Attribute

Mit dem Attribut AUTO_INCREMENT kann zu einem numerischen Datentyp hinzugefügt werden. Dieser ermöglicht es, dass Zahlen automatisch hochgezählt werden, um sie beispielsweise als Index zu verwenden. Die Spalte muss in diesem Fall natürlich als NOT NULL definiert werden.

BEISPIEL
mysql> CREATE TABLE fussball
    -> (
    -> id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    -> verein CHAR(100),
    -> platz TINYINT,
    -> PRIMARY KEY (id)
    -> );

Dieses Beispiel zeigt eine Tabelle, welche eine Spalte als Index verwendet (id). Diese wird automatisch hochgezählt. Mit PRIMARY KEY (id) wird festgelegt, welche Spalte der Primärschlüssel ist. Der Wert in id ist also einzigartig und darf nicht doppelt vorkommen.

Möchte man einen Index erstellen, der auch doppelte Werte enthalten darf, so wählt man das Attribut INDEX.

Folgendes sollte man bei AUTO_INCREMENT beachten:

- Die Spalte muss als INT deklariert werden

- Die Spalte muss als UNSIGNED deklariert werden, da logischerweise nur positive Werte Sinn machen.

- AUTO_INCREMENT wird in der Regel als eindeutiger Index verwendet, daher in Verbindung mit PRIMARY KEY oder UNIQUE angelegt.

- Mit der Funktion LAST_INSERT_ID() lässt sich herausfinden, welcher Eintrag zuletzt gespeichert wurde:

SELECT * FROM fussball WHERE id = LAST_INSERT_ID();

- Gelöschte Datensätze wirken sich nicht auf das Hochzählen aus. Wenn Datensätze von 1-10 existieren, Nr. 10 gelöscht wird, so hat der nächste neue Datensatz den Wert 11 und nicht 10.

MySQL unterstützt auch „gemischte“ Indizes. Folgendes Beispiel soll dieses verdeutlichen:

mysql> CREATE TABLE multiindex                                                
    ->  (
    -> name CHAR(30),
    -> name_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    -> PRIMARY KEY (name, name_id)
    -> );

mysql> INSERT INTO multiindex (name)
    -> VALUES ('Peter'),('Anja'),('Anja'),('Anja'),('Peter');

mysql> SELECT * FROM multiindex;
+-------+---------+
| name  | name_id |
+-------+---------+
| Anja  |       1 | 
| Anja  |       2 | 
| Anja  |       3 | 
| Peter |       1 | 
| Peter |       2 | 
+-------+---------+

Seite zuletzt geändert am 22.05.2018 | Impressum | Datenschutzerklärung | Copyright © pc-erfahrung.de