Sie sind hier : sebastian1012.bplaced.net/ homepage-neu / kreuz-und-quer / tutorials-info-neuigkeiten-datenbank / boolesche-werte-in-mysql.php

Boolesche Werte in MySQL

Ein häufiger Anwendungsfall für MySQL-Tabellen ist die Speicherung eines Wahrheitswertes. In den meisten Programmiersprachen ist eine Variable mit dem Wertebreich [0;1] vom Typ Boolean. In MySQL gibt es keinen solchen Datentyp. Aus diesem Grund soll dieser Beitrag Klarheit darüber schaffen, welcher Datentyp für einen Wahrheitswert wann am sinnvollsten ist.

MySQL-Kenner und PHPMyAdmin-Anwender werden im ersten Moment jetzt natürlich sagen, dass es doch sehr wohl einen Datentyp namens BOOL bzw. BOOLEAN gibt. Allerdings stimmt das nicht ganz, denn beides sind lediglich Synonyme für UNSIGNED TINYINT(1). Sie haben also nicht nur einen Wertebereich von [0;1] sondern von [0;255].

In Foren habe ich über die Verwunderung gelesen, weshalb man Werte bis 255 eingeben kann und nicht nur von 0 bis 9. Dies ist so, da die Länge stets in Byte angegeben wird. Dass dies bei Textdatentypen äquivalent ist, ist eine andere Geschichte. TINYINT(1) beduetet also eine Zahl mit 1 Byte. 1 Byte sind 8 Bit und die höchste Zahl mit 8 Bit ist die 255 (alle Bits auf 1).

Was soll hier nun eigentlich untersucht werden?
Jeder DB-Designer und Programmierer stand sicherlich schon häufiger vor der Frage, welchen Datentyp er/sie für eine solche BOOLEAN-Spalte verwenden sollte. Zur Auswahl stehen in diesem Benchmark: das oben erwähnte UNSIGNED TINYINT(1), ENUM(‚0′,’1‘) sowie CHAR(1).
Außerdem wurden alle Spalten auf NOT NULL gesetzt. Als Testdaten habe ich einfach 10000 etwa gleich verteilte Tupel benutzt und diese dann mehrfach repliziert, sodass am Ende ca. 21 Mio. Datensätze in der Tabelle vorhanden waren. Indizes wurden nicht benutzt, um die Performance allein auf Grund der Optimalität der Datensätze zu überprüfen.

Gut, nun sollten die Bedingungen klar sein 😉
Der erste Test ging auf die Performance einer einfachen SELECT-Anweisung mit WHERE-Bedingung:

SELECT * FROM boolean_test WHERE spalte='1'

Hierbei gab es keine Performance-Unterschiede – alle Datentypen erledigten die Aufgabe innerhalb weniger Millisekunden.

Dann wollte ich etwas härtere Geschütze auffahren. Ich habe einfach mal folgende Abfrage benutzt:

SELECT * FROM boolean_test GROUP BY spalte;

Hier zeigt sich schon ein deutlicherer Unterschied:

Datentyp Laufzeit Verhältnis zur schnellsten Variante
UNSIGNED TINYINT(1) 6,9 s 100%
ENUM(‚0′,’1‘) 7,3 s 106 % (+ 6%)
CHAR(1) 8,8 s 128 % (+ 28%)

Eindeutiger Sieger dieses Tests ist also Tinyint. ENUM wird intern ebenfalls numerisch behandelt, allein das Mappen der Werte auf die numerischen Äquivalente kostet etwas Zeit. Aus diesem Grund verhält sich übrigens ENUM(‚0′,’1‘) genauso wie ENUM(‚true‘,’false‘) oder jeder andere Wertebreich aus 2 Werten.

Als nächsten Test habe ich nach dieser Wahrheitsspalte sortiert. Ein Anwendungsgebiet könnte beispielsweise in einer Administrationsoberfläche eine Übersicht sein, in der noch nicht überprüfte Einträge ganz oben stehen sollen.

SELECT * FROM boolean_test ORDER BY spalte

Hier sind die Unterschiede knapper, die Reihenfolge aber bleibt gleich:

Datentyp Laufzeit Verhältnis zur schnellsten Variante
UNSIGNED TINYINT(1) 4,8 s 100%
ENUM(‚0′,’1‘) 4,9 s 102 % (+ 2%)
CHAR(1) 5,1 s 106 % (+ 6%)

Joins und andere Operationen habe ich an dieser Stelle mal weggelassen, weil es bereits jetzt eine eindeutige, logische Schlussfolgerung gibt: TINYINT(1) ist für einen Wahrheitswert ist die beste Variante.
Der Grund dafür ist, dass EDV-Systeme vom Aufbau her sehr viel besser mit Zahlen arbeiten können als mit Wörtern. Deshalb fällt CHAR(1) bei diesem Test eindeutig zurück und aus dem gleichen Grund wird ENUM auf numerische Werte gemappt.

Trotzdem ist der Unterschied zwischen TINYINT(1) und ENUM nicht so groß. Deshalb kann es durchaus Sinn machen, auf ENUM zu setzen, wenn sich dadurch an anderer Stelle Vorteile ergeben (z.B. wenn in einer externen Datenquelle mit „true“ und „false“ gearbeitet wird) und somit auf eine if-else-Konstruktion beim Import verzichtet werden kann.
Auch wenn andere Werte als ‚0‘ und ‚1‘ schädlich sind, dann sollte ENUM eingesetzt werden, denn wie gesagt nimmt UNSIGNED TINYINT einen Wertebereich von 0 bis 255 auf.

Übrigens gibt es auch einen Datentyp BIT. Dieser ist aber derzeit ebenfalls nur ein Synonym für Tinyint(1). Vermutlich wird sich dies auch in Zukunft nicht ändern, da Speicherarchitekturen in modernen Rechnern so designt sind, dass in einem Takt gleich ganze Wörter ausgelesen werden und nicht nur einzelne Bits. Deshalb hätte performancetechnisch ein solcher „wirklicher“ 1-Bit-Datentyp keinen Vorteil.

Es gibt also 2 Sichtweisen bei der Wahl des optimalen Datentyps für eine Wahrheitswert-Spalte: Für die Performance ist UNSIGNED TINYINT(1) die erste Wahl. Soll der Wertebereich aber bewusst auf ‚0‘ und ‚1‘ (und nur auf diese Werte, keine zusätzlichen) beschränkt werden, ist ENUM vorzuziehen. Auch wenn sich aus „sprechenden“ Werten an anderer Stelle Vorteile ergeben, kann der Einsatz von ENUM sinnvoll sein.