Binary und UTF-8 in MySQL Tabellen speichern

Die MySQL-Speicherengine arbeitet bytesemantisch, d.h., sie speichert auch nur Oktetten (Bytes)

Optimierung und Design

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.

Noch ein Fakt und ein Tipp

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.

Demo zur Bytesemantic

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');

use Encode

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.

$dbh->{mysql_enable_utf8} = 1

Dieses Attribut kann beim Erstellen eines DBH (Data Base Handle) gesetzt werden. Es bewirkt, daß die Collation durchgereicht wird, somit sind die Ergebnisse von SELECT-Statements UTF-8-kodierte Zeichenketten und keine Oktetten. Damit wird also die Perlinterne Kodierung eingeschaltet. Gleichermaßen wird die Perlintene Kodierung wieder ausgeschaltet, wenn Perlinterne Zeichenketten an MySQL übergeben werden (INSERT). Dieses Attribut macht nur den Transport Layer transparent1

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
$dbh->{mysql_enable_utf8} = 1;

# Stringoperationen wie upper() für UTF8 ermöglichen
$dbh->do('SET NAMES UTF8');

# eine utf-8-kodierte Zeichenkette in Kleinbuchstaben einfügen
$dbh->do("INSERT INTO images(zeichen)values(?)", {}, "äöü€");

# 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!

Und das Wichtigste

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)!


Die rein persönlichen Zwecken dienende Seite verwendet funktionsbedingt einen Session-Cookie. Datenschutzerklärung: 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. @: nmq​rstx-18­@yahoo.de