Die MySQL-Speicherengine arbeitet bytesemantisch, d.h., sie speichert auch nur Oktetten (Bytes)
Anzahl der Zeichen oder Anzahl der Bytes? Das ist auch beim DB Design die Frage wenn es darum geht, Speicherplätze zu optimieren. Wie später noch anhand konkreter Codebeispiele gezeigt wird, arbeitet die MyQL Speicherengine unabhängig vom für die Tabelle oder das Feld deklarierten Charset, beispielsweise Charset=Lain1. Das heißt, daß sie die zu speichernden Oktetten nicht verändert. Die Kodierung kommt nämlich nur dann ins Spiel, wenn eine bestimmte Collation gebraucht wird!
Wenn die Collation jedoch nicht benötigt wird sondern MySQL nur zum Speichern benutzt, ist die Deklaration einer bestimmten Zeichenkodierung zur Tabelle und deren Felder uninteressant. Ist jedoch ein Feld für ein bestimmtes Charset z.B. text varchar(255) charset latin1 not null default '' deklariert, ist der für die Bytes verfügbare Speicherplatz als Anzahl der Zeichen angegeben. So kann es passieren, daß der Platz für UTF-8 nicht ausreicht, weil in dieser Kodierung ein Zeichen ggf. mehrere Bytes belegt.
Beachte: Mit varchar(1) CHARACTER SET latin1 reserviert MySQL 1 Byte, mit UTF8 hingegen 3 Byte. Es gibt in MySQL weitere Charsets wie z.B. utf8mb4 was 4 Byte reserviert. Siehe also MySQL.
Wie mit der Deklaration einer zweckmäßigen Zeichenkodierung das eigene DB Design verbessert und optimiert werden kann, das ist das eigentliche Anliegen dieses Artikels. Das Verständnis der hier gezeigten Zusammenhänge ist unerläßlich für diejenigen die ihre Programme mit DB Anbindung optimal gestalten wollen.
Wurde für eine ganze Tabelle ein bestimmtes Charset, zum Beispiel CHARSET=UTF8 deklariert, gilt das für alle Felder sofern diese im Einzelnen nicht anders deklariert sind. Wenn eine solche Tabelle indiziert wird, wächst natürlich auch die Datenmenge des Index auf ein Mehrfaches, was u.U. nicht erwünscht ist. Einem solchen Verhalten lässt sich abhelfen, indem die Schlüsselfelder für Latin1 deklariert werden:
CREATE TABLE `images` ( `id` int(32) NOT NULL AUTO_INCREMENT, `image` text, `text` varchar(255) CHARACTER SET latin1 NOT NULL DEFAULT '', `xkey` varchar(128) CHARACTER SET latin1 NOT NULL DEFAULT '', PRIMARY KEY (`id`), KEY `xkey` (`xkey`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8
Wobei es überhaupt zu überlegen gilt, für Schlüssel gleich welcher Verwendung (z.B. CODE intern), nicht-ASCII kodierte Strings zu verwenden: Eine Sache die einmal mehr dafür spricht, Schlüsselnamen von Inhalten zu trennen. Wenn ein Schlüssel verwendet werden soll, muß er in der gesamten Länge passen. Siehe also MySQL diesbezüglich.
Bytesemantic heißt, daß die zu speichernden Bytes nicht verändert werden.
# Untenstehender Code zeigt die Byteorientierung in MySQL
# D.h., daß das Speichern von Binaries oder UTF-8 Oktetten
# auch in Tabellen möglich ist die mit einem Charset=Latin1
# deklariert sind
use strict;
use warnings;
use dbh;
use IO::File;
$, = "\n";
# Erstelle ein DB Handle
my $dbh = (bless{})->dbh('myweb');
$dbh->do(q(
CREATE TABLE if not exists images(
id int(32) auto_increment primary key,
image text
)CHARSET=Latin1
));
# Zur Kontrolle
print $dbh->selectrow_array('SHOW CREATE TABLE images'), "\n";
# eine Grafik einlesen
my $fh = IO::File->new;
$fh->open('768.jpg', O_BINARY|O_RDONLY) or die $!;
read($fh, my $image, -s $fh);
$fh->close;
# Grafik in MySQL Textfeld einfügen
$dbh->do("INSERT INTO images(image)values(?)", {}, $image);
# Grafik aus MySQL auslesen
my $binary = $dbh->selectrow_array('SELECT image FROM images WHERE id = (SELECT LAST_INSERT_ID())');
# Grafik zur Kontrolle ausgeben
$fh->open('new.jpg', O_CREAT|O_TRUNC|O_BINARY|O_RDWR) or die $!;
$fh->print($binary);
$fh->close;
# Derselbe Test mit den Bytes eines UTF-8-kodierten Zeichen
# Oktetten des Eurozeichen einfügen
$dbh->do("INSERT INTO images(image)values(?)", {}, "€");
# Oktetten auslesen
print $dbh->selectrow_array('SELECT image FROM images WHERE id = (SELECT LAST_INSERT_ID())');
# Ausgabe OK
# Cleanup
$dbh->do('drop table images');
Während die Collation einer DB für DB-interne Operationen mit Zeichenketten zuständig ist, kann das Core-Modul Encode für die Perlinterne Kodierung verhaftet werden. Das heißt, das der Programmierer selbst entscheiden kann, ob er Stringoperationen dem RDBMS überlässt oder ob er sich mit seinem Code selbst darum kümmert.
Dieses Attribut kann beim Erstellen eines DBH (Data Base Handle) gesetzt werden. Es bewirkt, daß die Perlinterne Kodierung beim Transport über den DBH ausgeschaltet wird. Dieses Attribut macht nicht nur den Transport Layer transparent sondern bewirkt auch daß bei einem SELECT die Textstrings als kodierte Zeichenketten geliefert werden.
In untenstehendem Beispiel werden UTF-8-kodierte Kleinbuchstaben eingefügt in ein Feld was wie folgt deklariert wurde:
zeichen varchar(255) collate utf8_general_ci not null default '', Charset=UTF8 für die ganze Tabelle
Mit dem SELECT Statement bekommt Perl eine in das interne Format utf-8-kodierte Zeichenkette. Auf diese Zeichenketten sind, weil der Interpreter die Kodierung kennt, Stringoperationen wie substr(), uc() und lc() erfolgreich anwendbar. Hiermit würde die Perlfunktion length() auch die Anzahl der Zeichen liefern und nicht die Anzahl der Oktetten.
Zur Ausgabe einer kodierten Zeichenkette auf STDOUT ist die interne Kodeirung jedoch wieder auszuschalten, was use bytes; in diesem Fall erledigt.
# Enable UTF-8
# schaltet die Perlinterne Kodierung
# für INSERT aus
$dbh->{mysql_enable_utf8} = 1;
# eine utf-8-kodierte Zeichenkette in Kleinbuchstaben einfügen
$dbh->do("INSERT INTO images(zeichen)values(?)", {}, "äöü€");
# Stringoperationen wie upper() für UTF8
# in der Abfrage ermöglichen
$dbh->do('SET NAMES UTF8'); # NUR bei Abfragen setzen!!!
# Ausgabe auf STDOUT
use bytes; # Schaltet die interne Kodierung ab
# benutze MySQL für uppercase im Select Statement
print
$dbh->selectrow_array('SELECT upper(zeichen) FROM images WHERE id = (SELECT LAST_INSERT_ID())'),
'ÄÖÜ€';
# Übereinstimmung!
Eine Änderung der Zeichenkodierung in MySQL ändert nichts an den Inhalten, es sei denn daß sie infolge der Änderungen zwangsläufig gekürzt werden (siehe Feldtypen und ~Längen)! Abschließend noch ein Blick auf mein MySQL-Backend:
Datenschutzerklärung: Diese Seite dient rein privaten Zwecken. Auf den für diese Domäne installierten Seiten werden grundsätzlich keine personenbezogenen Daten erhoben. Das Loggen der Zugriffe mit Ihrer Remote Adresse erfolgt beim Provider soweit das technisch erforderlich ist. sos@rolfrost.de.