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.

Jan hat 152 BeitrÀge geschrieben

30 Kommentare zu “Boolesche Werte in MySQL

  1. GhostGambler sagt:

    Typos ohne Ende, Shift-Taste scheint dauernd ohne Funktion geblieben zu sein, ich hab den Eintrag gar nicht ganz gelesen… als öffentlicher Text echt katastrophal.

    Übrigens rate ich (und bestimmt auch andere) von der Benutzung von Zahlen als Enum-Werte ab.
    Bei einer ENUM-Spalte kann man nämlich auch mit den internen int-Repräsentationen arbeiten, d.h. man kann bei einer Enum-Spalte mit „Bla“ und „Blubb“ als mögliche Werte, auch durchaus ein „UPDATE … SET spalte = 0“ machen.
    Wenn man jetzt als Werte z.B. „1“, „0“ angibt, und dann ein „UPDATE … SET spalte = 0“ macht, steht in der Spalte eine 1. Das ist extrem unschön und eine beliebte Quelle für Fehler.

  2. IcyT sagt:

    Außerdem wird hier suggeriert, dass (1) hinter TINYINT die Größe in Bytes angibt, was aber so nicht stimmt. TINYINT ist IMMER 1 Byte groß, egal was in der Klammer steht. Die Klammer bedeutet bei den Zahlen lediglich die Anzeigebreite (display width).

  3. Chris sagt:

    Schöner Artikel, war mir so noch garnicht bewußt, aber macht Sinn. Finds immer wieder toll die Performance-Vergleiche zu sehen, gibt nicht viele Blogger/Webmaster die sich soviel Mühe bei ihren Artikeln geben!

  4. Stefan sagt:

    Nach der Kritik muss ich mich auch mal zu Wort melden:

    Super Artikel, klasse Blog!
    Vielen Dank und weiter so!

  5. admin sagt:

    Vielen Dank. So etwas höre ich auch ab und an gern. Sicherlich haben die beiden ersten Kommentatoren recht (hab dann auch noch 4-5 Rechtschreibfehler ausgebessert, fand das jetzt nicht „katastrophal“, aber ich bin auch kein Deutsch-Lehrer).

    @icyt: Was macht denn die Anzeigebreite? Also wofür ist das gut? In Oracle ist diese Zahl meines Wissens nach die Länge in Byte. Deshalb nahm ich hier das gleiche an.

  6. IcyT sagt:

    Wieso? Nur weil ich es nicht nochmal per Copy & Paste hier reinschreibe? Sei froh, dass ich wenigstens deine falschen Aussagen ausbügle 😛

  7. Adrian sagt:

    Vielen Dank für diesen Blog. Dieser Blog ist einer der wenigen bei dem ich jeden Beitrag komplett lese. Die Artikel sind meistens sehr aufschlussreich.

    Also noch mal vielen Dank und weiter so. Mich stören die Rechtschreibfehler übrigens gar nicht.

    Grüße

    Adrian

  8. Ralf sagt:

    Die Zahl in den Klammern (Anzeigenbreite) ist optional und gibt an mit wie vielen Leerzeichen der ausgegebene Wert aufgefüllt werden soll.
    TINYINT kann Werte von 0-255 speichern, dass sind maximal 3 Zeichen.
    Würde man nur TINYINT() angeben, würden immer so viele Leerzeichen vor dem Wert eingefügt, bis die Zeichenkette die von MySQL zurück gegeben wird 3 Stellen hat. Bei einstelligen Werten also zwei Leerzeichen, bei zweistelligen Werten ein Leerzeichen und bei dreistelligen Werten halt gar kein Leerzeichen.
    Bei Verwendung von TINYINT(1) würde immer nur ein Leerzeichen eingefügt werden. Also egal ob ein- oder zweistellige Werte zurück gegeben werden, es wird immer nur ein Leerzeichen eingefügt.

    Am besten macht man sich das mit ZEROFILL deutlich. TINIYINT(1) ZEROFILL würde bei einstelligen Werten 0x zurück geben, bei zweistelligen eben 0xx. TINYINT(2) ZEROFILl würde bei einstelligen Werten 00x zurück geben, bei zweistelligen Werten 0xx.
    Demnach würde TINYINT(3) keinen Sinn ergeben. Denn es können bei einem Wertebereich mit maximal 3 Stellen auch nur maximal zwei Stellen aufgefüllt werden.

    Die Anzeigenbreite ist also eine Formatierungsanweisung. In PHP wäre es vergleichbar mit printf(„%01d“,$foo).

    BTW: Wer sich über Rechtschreibung oder mangelnde Großschreibung aufregt, hat sich nicht für den Artikel interessiert und hat auch ansonsten keine Hobbys.
    Wer Erklärungen anhand von Links gibt, hat es (wahrscheinlich) selber nicht verstanden.

  9. Jo sagt:

    Interessanter Beitrag. Arbeite zwar schon länger mit Tinyint, aber schön zu sehen wie das im Vergleich zu anderen Datentypen ist.

    Zudem denke ich auch, dass in einem „technischen“ Blog die Rechtschreibung zweitrangig ist.

  10. Mich hätte eher interessiert, ob man nicht besser einen normalen Integer nehmen sollte, trotz des höheren Platzbedarfs. Daß der Kampf zwischen diesen Varianten so ausgeht, überrascht mich nicht.

  11. Leif sagt:

    Ein Integer ist die Ganzzahl, die der logischen Adressbreite des Prozessors entspricht. Im Umgang mit diesen Zahlen kann ein Prozessor in den Burst-Mode schalten und damit schneller arbeiten. Aus diesem Grund sind in hochoptimierten Programmen einerseits die Befehle an dieser Adressbreite ausgerichtet (wenn möglich) und Berechnungen mit Integern schneller als mit Bytes.

  12. admin sagt:

    Ah ja. Na wenn das so ist, versuche ich das mal noch zu testen. Allerdings habe ich derzeit wenig Zeit. Ich habe ja die SQL-Abfragen oben beschrieben. Falls also jemand etwas Zeit hat, könnte er / sie das auch nachprüfen. Aber ich werds auch versuchen…

  13. Dominik sagt:

    erstmal vielen Dank für den tollen Beitrag. Aber hin und wieder versteh ich manche Menschen nicht. Da macht Sie jemand die Arbeit und schreibt einen ordentlichen Artikel, aber zu würdigen wissen sie dies nicht und nörgeln an der Rechtschreibfehlern rum!

  14. admin sagt:

    Ich denke aber, dass in einem technischen Blog es Wichtigeres gibt als die Rechtschreibung, denkst du nicht? Bei einem Beitrag vom Umfang wie diesem werden wohl 3 – 4 Rechtschreibfehler nicht so wichtig sein, zumal mir allein der Tonfall Deines Kommentars (der erste ganz oben) sehr unangenehm auffällt. Das hat nichts mit Netiqutte zu tun, wie Du dort schreibst.
    Und falls es Dich doch zu sehr stört, bitte ich Dich diesen Blog nicht mehr zu lesen oder zumindest in Zukunft das Kommentieren zu unterlassen.

  15. GhostGambler sagt:

    Ja, mein Ton ist manchmal etwas schroff.

    Auch bei technischen und/oder wissenschaftlichen Texten sollte eine einwandfreie Rechtschreibung dazu gehören.
    Und mal ganz im ernst, das waren Fehler, die unser liebster aller Browser mit entsprechender Erweiterung hübsch rot unterschlängelt.
    Wenn es jetzt irgendwelche komplizierten grammatikalischen Konstrukte gewesen wären, über die ich mich echauffiert hätte, aber nein, es waren simple Dinge, über die jedes 0815-Duden-Abgleich-Programm gezetert hätte.
    Und jeder von dir verfasste Text sollte, vor allem wenn du ihn öffentlich stellst, dir die Mühe wert sein ihn einmal auf rote Linien zu überprüfen – wenn schon nicht noch einmal Korrektur lesen.

    Ich hab den Feed von dem Blog hier ja nicht abonniert, weil zu wenig Einträge pro Tag bei mir rein spülen, sondern weil ich die Beiträge für produktiv halte.
    Dass allerdings der Glanz dieses Blogs von derartigen Kleinigkeiten geschmälert wird, finde ich sehr schade.
    Das muss einfach nicht sein.

  16. Alex sagt:

    Mir war vorher schon bewusst, wie booleansche Werte in mySQL gehandlet werden. Der Artikel ist gut, bis auf einige Fehler, aber man muss sagen, dass diese Kommentatoren, die hier alles schlecht reden und auf der Rechtschreibung rumhacken, selbst ihren Ausdruck mal unter die Lupe nehmen sollten.

    Dieses Blog ist, außer einigen Fehlern in diesem Blog, wirklich sehr informativ, mit gut ausgearbeiteten Beiträgen und die Wortwahl der Kommentatoren keines Falles berechtigt.

    Gruß Alex

  17. Die ganzen Kommentare sind zwar alt, aber mich würde interessieren, ob jemand mal die Geschwindigkeit von CHAR(0) getestet hat. Man könnte damit NULL für False speichern und „“ für TRUE. Die Prüfung würde man dann mit IS NULL machen.

  18. wolfgang sagt:

    super beitrag, super erklaerung, schade dass es immer leute gibt die die rechtschraibung anmekkern anstelle sich ueber super-anleitungen zu freuen.

    die rechtschreibfanatiker sollen doch bitte in rechtschreibforen gehen, so wuerden fachliche beitraege uebersichtlicher bleiben !

Eine Antwort schreiben

Ihre E-Mail-Adresse wird nicht veröffentlicht. Benötigte Felder sind markiert mit *

You may use these HTML tags and attributes: <a href=""> <blockquote cite=""> <pre lang=""> <b> <strong> <i> <em>