Seit einigen Wochen arbeite ich mit geringer Priorität an einem MaxDB-Plugin für das MySQL MigrationToolkit. Heute habe ich mir Gedanken über das Mapping von Datentypen gemacht und habe die bisherigen Entscheidungen hinterfragt, die ich einst getroffen hatte, um schnell etwas zu erstellen. Dabei kamen ein paar Details zu Tage, die ich noch nicht kannte.

Inhalt

• Mapping von Strings
  • CHAR
  • VARCHAR
• Mapping von LONG
• Mapping von BOOLEAN
• Mapping von Zahlen
  • FIXED
  • FLOAT
  • INTEGER, SMALLINT
• Fazit: Aufgepasst!

Stand der Informationen und Warnung

Alle hier angeführten Mappings entsprechen dem was ich heute (27.10.2005) implementiert habe. Diese Mapping ist in keinem Release des MigrationToolkits enthalten und wird es vielleicht auch nie sein. Spätere Versionen des MigrationToolkits können ganz andere Mappings verwenden! Deshalb sollte dieser Blogeintrag nicht als Dokumentation verstanden werden. Es ist mehr ein Blick auf den aktuellen Entwicklungsstand.

Mit MySQL 5.0.3 gab es einige Neuerungen, die das Mapping von Strings beeinflussen. Deshalb ist vor einer Migration zu prüfen, welche MySQL-Version das Zielsystem bildet. Seit MySQL 5.0.3 können VARCHAR Spalten bis zu 65.535 Zeichen aufnehmen anstatt nur 255 Zeichen.

Weiterhin wurden zwei neue Datentypen VARBINARY und BINARY eingeführt, die [VAR]CHAR Spalten ähneln jedoch Binärdaten aufnehmen können. [VAR]BINARY speichert im Gegensatz zu [VAR]CHAR “binary byte strings”. [VAR]CHAR kann zwar mit der Option[BYTE|BINARY] definitert werden, also [VAR]CHAR(n) BINARY, aber das führt nur zu einer binary Collation, etwa CHARACTER SET latin1 COLLATE latin1_bin. Die Collation ändert nichts daran, daß [VAR]CHAR(n) BINARY nur “non-binary character strings” aufnehmen kann.

Letztlich hat sich auch noch das Handling von Leerzeichen am Ende eines [VAR]CHAR Feldes in 5.0.3 verändert. Vor der Version 5.0.3 wurden Leerzeichen abgeschnitten. Das widerspricht dem SQL-Standard. Leider verletzt auch MaxDB 7.6.00 an dieser Stelle den Standard und schneidet ab.

Eine Randbemerkung zu einem kleinen Stolperstein für mich. CHAR ist unter MySQL ein Synonym für CHAR(1). BINARY ein Synonym für BINARY(1). Diese Synonyme finden jedoch keine Entsprechung in VARCHAR und VARCHAR(1) bzw. VARBINARY und VARBINARY(1). VARCHAR und VARBINARY benötigten beide zwingend eine Längenangabe.

Mapping ab MySQL 5.0.3.

Der Datentyp BINARY steht erst ab MySQL 5.0.3 zur Verfügung. Deshalb wird bei allen Versionen vor 5.0.3 der MaxDB Datentyp CHAR(n) BYTE auf TINYBLOB gemappt, sofern die Länge weniger als 256 Zeichen beträgt.

Mapping ab MySQL 5.0.3.

Wird eine MySQL Version kleiner als 5.0.3 verwendet, ergeben sich einige Änderungen, da VARCHAR(n) bis zu dieser Version maximal 255 Zeichen speichern kann. Bei MaxDB Spalten, die mehr als 255 Zeichen enthalten, muß auf die passenden BLOB-Typen ausgewichen werden.
Wie auch oben werden nur die Unterschiede zu 5.0.3+ in der folgenden Tabelle dargestellt.

LONG Spalten von MaxDB können maximal 4G Bytes aufnehmen. Das macht für ASCII/BYTE 4G Zeichen, bei UNICODE können noch 2G Zeichen gespeichert (2 Byte pro Zeichen). Die MySQL Datentypen LONGTEXT und LONGTYPE können die benötigten 4G Byte Daten aufnehmen.

MySQL hat kein Gegenstück zum MaxDB Datentyp BOOLEAN. BOOL und BOOLEAN sind unter MySQL Synonyme für TINYINT(1). In der MT Version von heute ist kein Mapping implementiert.

Das Mapping von numerischen Datentypen ist mit Detailproblemen versehen. FIXED/DECIMAL hat das Problem, daß MySQL maximal 30 Nachkommastellen verwendet, währen MaxDB mit 37 Nachkommastellen aufwartet. FLOAT hat unter MaxDB eine frei wählbare Präzision von bis zu 38 Stellen. Eine derartige Präzision bietet MySQL erst seit der Version 5.0.3 unter dem Schlagwort “Precision Math”. “Precision Math” jedoch ersteckt sich nur auf Fixkommazahlen (DECIMAL, Ganzzahlen – INTEGER, …) nicht jedoch auf Fließkommazahlen.

FIXED ist unter MySQL ein Synonym für DECIMAL. Der Datentyp FIXED ist ein Fixed Point Datentyp. Es gibt also eine festgelegte Anzahl von Nachkommastellen. Unter MaxDB können maximal 38 signifikante Stellen verwendet werden. Von den 38 Stellen dürfen maximal 37 Nachkommastellen sein. Ab MySQL 5.0.3 kann DECIMAL 65 (zweitweilig 64) Stellen speichern von denen maximal 30 Nachkommastellen sein dürfen. Das bedeutet, daß ein MaxDB FIXED(38, 37) nur unzureichend in MySQL DECIMAL(38,30) gemappt werden kann.

Wer das unten angegebene SQL im SQL Studio ausführt wird sehen, daß 0.33 und 0.99 ausgegeben werden. Verwendet man unter MySQL den Datentyp FLOAT(2), dann erhält man etwas ähnliches wie 0.333333 und 1.0000000298023.

CREATE TABLE t1 (col_float FLOAT(2))
//
INSERT INTO t1 (col_float) VALUES ( 1 /3 )
//
SELECT col_float, col_float * 3 FROM t1
  

MySQL garantiert nicht, daß bei Verwendung von FLOAT oder DOUBLE präzise Rechnungen durchgeführt werden. Die Berechnungen können sogar von Rechner zu Rechner und Betriebssystem zu Betriebssystem unterschiedlich ausfallen. Der Grund ist, daß MySQL sich auf die Routinen verläßt, die das Betriebssystem bereitstellt. Beim Datentyp FLOAT spricht MySQL deshalb nur von einer Genauigkeit von etwa 7 Stellen und für DOUBLE von etwa 15 Stellen. Erst ab 5.0.3 wird eine eigene Bibliothek für DECIMAL verwendet, was die Genauigkeit auf mindestens 64 Stellen anhebt.

Das MigrationToolkit wertet die Rechengenauigkei höher als die Anzahl der verfügbaren (Nachkomma-)Stellen und mappt deshalb FLOAT auf DECIMAL. Wie bei FIXED tritt jedoch das Problem auf, daß DECIMAL maximal 30 Nachkommastellen speichern kann. Wenn man DECIMAL jedoch 30 Nachkommastellen spendiert, dann bleiben nur noch 65 – 30 = 35 Vorkommastellen übrig. Das reicht bei weitem nicht, um den größten FLOAT-Wert von MaxDB zu speichern. 9. 9999999999999999999999999999999999999E+62 passt einfach nicht in ein DECIMAL(65, 30).

mysql> create table t1(col_decimal decimal(65,30), col_double double);
Query OK, 0 rows affected (0.02 sec)

mysql> insert into t1(col_decimal, col_double) values (9.9E+62, 9.9E+62);
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+---------------------------------------------------------------+
| Level   | Code | Message                                                       |
+---------+------+---------------------------------------------------------------+
| Warning | 1264 | Out of range value adjusted for column 'col_decimal' at row 1 |
+---------+------+---------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select col_decimal, col_double, col_double - col_decimal from t1;
+--------------------------------------------------------------------+------------+--------------------------+
| col_decimal                                                        | col_double | col_double - col_decimal |
+--------------------------------------------------------------------+------------+--------------------------+
| 99999999999999999999999999999999999.999999999999999999999999999999 |    9.9e+62 |                  9.9e+62 |
+--------------------------------------------------------------------+------------+--------------------------+
1 row in set (0.00 sec)

Das MigrationToolkit versucht derzeit gar nicht erst mit Automatismen und Wertebereichsuntersuchungen herauszufinden, welcher Wertebereich und welche Präzision benötigt wird. Hier sollte der Anwender unbedingt manuell prüfen, ob MaxDB in einer FLOAT Spalte Werte enthält die nicht in einem MySQL DECIMAL abgebildet werden können. Ich nehme auch keine Fallunterscheidung vor welche MySQL Version vorliegt. Entweder man hat kleine FLOAT-Werte unter MaxDB und es passt oder man muß die Automatik überstimmen.

Die Entscheidung für DECIMAL(65, p) ist eine der Enscheidungen, die noch zu überprüfen ist.

Das Mapping von Ganzzahlen ist denkbar einfach. Alles bleibt so wie es ist.

Es existieren einige Tücken im Mapping von numerischen Werten. Direkte Entsprechungen gibt es nicht immer. Man muß genau auf die Wertebereiche und die gewünschte Rechengenauigkeit schauen, um einen passenden Datentyp auszuwählen. Also Achtung beim Versuch MaxDB-Tabellen allzu direkt auf MySQL-Tabellen zu mappen. Morgen geht’s weiter mit den verbleibenden Datentypen: DATE, TIME und TIMESTAMP.