Oftmals hat man bei Webseiten mit dem Anlegen von größeren Datenmengen zu tun, welche man in folgende Kategorien einteilen kann:
- Längere Zeichenketten
- Sehr lange Zeichenketten
- Binärdaten (klein bis mittelgroß)
- Binärdaten (sehr groß)
MySQL bietet für jeden diesen Typen (und mehr) einen geeigneten Datentyp. Ich möchte in diesem Artikel ein wenig auf die Unterschiede und Besonderheiten bei der Verwendung dieser Typen eingehen.
1 | Längere Zeichenketten
Normalerweise verwendet man in MySQL den Typ varchar für Zeichen. Dieser Typ wird aber so gespeichert, dass man eine maximale Größe für ihn angeben muss. Der Vorteil dieses Typs liegt darin, dass er sehr effektiv ist und man über SQL Befehle in Datensätzen von diesem Typ direkt und sehr schnell suchen kann.
Will man etwas längeren Text speichern so empfiehlt es sich den Binären Textstring Datentyp text zu verwenden. Das Indizieren von diesem Datentyp läuft etwas anders ab als beim normalen varchar Typen, weshalb man diesen Typen wirklich nur für größere Textfelder verwenden sollte und keinesfalls wenn man ihn nicht wirklich braucht. Die maximale Größe dieses Datentyps ist auf 65535 bytes begrenzt - damit ist er sehr ähnlich zum string Datentyp von .NET.
2 | Sehr lange Zeichenketten
Wem das noch nicht reicht, der kann mit dem Datentyp longtext sehr lange Zeichenketten speichern. Dieser Datentyp verhält sich sehr ähnlich zu text aber kann sehr viel mehr Daten speichern. Insgesamt sind ca. 4 GB (4294977295 bytes) an Daten möglich.
Da dieser Typ größerere Attribute benötigt ist es effektiver für kleinere Strings text als Datentyp zu verwenden und nur in Ausnahmefällen longtext als Datentyp zu deklarieren.
Z | Auslesen von text und longtext in .NET
Diese beiden Datentypen (und ebenso tinytext, mediumtext) müssen anders ausgelesen werden als die bekannten varchar Typen. Das Problem ist, dass diese Typen binär gespeichert werden - also nicht direkt als Strings bei einer Abfrage durch .NET zurückkommen.
Die beste Strategie ist sich vorher zu überlegen mit welcher Kodierung man die Strings speichern will und dann diese Kodierung für alle SQL Befehle zu verwenden (UPDATE, INSERT und bei SELECT zum Konvertieren des Strings in Binärdaten und umgekehrt). Der folgende Code demonstriert diese Möglichkeit.
row["LT_Example"].ToString();//System.Byte[]
UTF7Encoding enc = new UTF7Encoding();
enc.GetString((byte[])row["LT_Example"]);//Beispieltext
Ist die Datenbank auf die selbe Kodierung eingestellt wie die Kodierung, die man in .NET zum Auslesen verwendet, so kann man sich das Konvertieren von Strings in Binärdaten zum Speichern von Strings sparen. MySQL vollzieht dann die Konvertierung automatisch. Somit bleibt nur gezeigte Konvertierung von Bytes in einen String über die Kodierung übrig.
Dies ist auch der große Unterschied zwischen den speziellen Textdatentypen und den reinen Datentypen.
3 | Binärdaten (klein bis mittelgroß)
Hier muss man immer die Daten in Binärdaten umwandeln. Der Datentyp heißt mediumblob (medium binary large object). Ansonsten hat man hier ähnliche Restriktionen wie bei text, d.h. man hat insgesamt 16777215 bytes (ca. 16 MB) zur Verfügung. Dieser Datentyp sollte für 99% der in MySQL Datenbanken gespeicherten Dateien genügen.
4 | Binärdaten (sehr groß)
Für Leute die einfach nicht genug kriegen können wurde ein gigantischer Datentyp geschaffen, welcher fast komplett analog zu longtext ist. Mit longblob ist es möglich 4294967295 bytes an Daten zu speichern. Der Overhead ist allerdings auch hier größer.
Insgesamt kann man die Overheads folgendermaßen klassifizieren (L = Datenlänge, # = Overhead):
- blob = L + 2 bytes (Maximalgröße ist 2^16 - 1 or 65535 bytes, 65 KB)
- mediumblob = L + 3 bytes (Maximalgröße ist 2^24 - 1 or 16777215 bytes, 16 MB)
- longblob = L + 4 bytes (Maximalgröße ist 2^32 - 1 or 4294967295 bytes, 4 GB)
Das Auslesen von diesen Binärdaten geht in .NET sehr einfach (trivialer Cast der Zelle in ein Byte-Array, (byte[])DataTable.Rows[ZEILE][SPALTE]). Was man dann mit dem byte[] Array macht hängt vom Typ ab, z.B. könnte man das Array in einen MemoryStream holen und dann in Bild draus machen (geht natürlich nur wenn es sich um ein Bild handelt).
Um ein Byte-Array in die Datenbank zu bringen kann man ein sog. Parameter-Query verwenden. Solche Queries eigenen sich perfekt für DataQueries. Ein Beispiel stellt folgender Code dar:
// data is a byte[], ?file specifies the parameter
RunDataQuery("UPDATE EX_TAB FILECONTENT=?file WHERE EX_ID=2", "?file", data);
// function RunDataQuery(string q, string p, byte[] d)
MySQLCommand ODBCmd = new MySQLCommand();
ODBC.Open();
ODBCmd.Connection = ODBC;
ODBCmd.CommandText = q;
MySQLParameter param = new MySQLParameter(p, DbType.Object);
param.Value = d;
param.Size = MAX_ALLOWED_PACKET;
ODBCmd.Parameters.Add(param);
ODBCmd.ExecuteNonQuery();
ODBC.Close();
Die Konstante MAX_ALLOWED_PACKET ist dabei von besonderem Interesse für uns, da dieser Parameter über Erfolg oder Misserfolg entscheiden kann.
Erhält man eine Exception "MySQL server has gone away", so ist im Normalfall dieser Parameter falsch eingestellt (Serverseitig). Nachdem wir den Parameter nur zur Kontrolle verwenden, muss unsere Konstante den selben Wert aufweisen wie die Servervariable. Wie also können wir die Servervariable entsprechend einstellen (z.B. auf 16 MB - genügend für alle mediumtext und mediumblob Typen).
Zunächst verbinden wir uns über die Kommandozeile mit dem MySQL Server und loggen uns mit Benutzernamen und Passwort ein (z.B. mysql -u user -p). Anschließend fragen wir die aktuellen Settings ab:
SHOW GLOBAL VARIABLES;
Wir müssen nur den Wert für MAX_ALLOWED_PACKET raussuchen. Im Normalfall sollte dieser Wert bei etwa 1024*1024 liegen. Wir setzen ihn nun auf 16 MB mit dem Befehl:
SHOW GLOBAL VARIABLES;
SET GLOBAL MAX_ALLOWED_PACKET = 16 * 1024 * 1024;
Das wars auch schon gewesen. Nun sollten das Speichern von Dateien in der MySQL Datenbank bis 16 MB kein Problem mehr sein. Manchmal wird dieser Wert beim Neustart des Servers überschrieben. Sollte dies der Fall sein, so ist diese Variable in der Datei my.cnf oder (neuere Versionen) my.ini gesetzt. MySQL übernimmt dann den Wert von der Datei beim Neustart. Man sollte einfach den Wert in der Datei ändern um das gewünschte Resultat zu erhalten.