5 Minuten Lesezeit (1058 Worte)

Schubladendenken für Fortgeschrittene: Der MySQL/MariaDB Datentyp ENUM?

Donnerstag, 08. Februar 2024

Der MySQL/MariaDB Datentyp ENUM ist vielen Anwendern unbekannt. Manchmal kann dieser Typ aber hilfreich sein. Wir möchten an dieser Stelle klären, wann sich ein Einsatz von ENUM lohnt und was die Vor- und Nachteile dieses Datentyps sind.

Der String, das unbekannte Wesen!

Bei der Arbeit mit String-Daten ist es manchmal wichtig sicherzustellen, dass in den Spalten Ihrer Tabellen nur fachlich gültige, also bekannte und bereits definierte Daten gespeichert werden. Eine Möglichkeit, dies zu erreichen, ist die Verwendung von ENUMs, mit der Sie eine vordefinierte Liste zulässiger Werte für eine Spalte festlegen können. So ist in einem Anwendungsfall ggf. fachlich klar, dass in der Spalte „Anrede“ in einer Tabelle „person“ nur die Strings „Frau“, „Herr“ und „divers“ zulässig sein sollten. Natürlich könnte man dies auch über eine Fremdschlüsselbeziehung, eine weitere Normalisierung, lösen (Anlegen einer Tabelle „Anreden“ mit einem Primärschlüssel und den Strings für die gewünschten Werte). Auch die Nutzung eines „Check-Constraints“ wäre sicherlich denkbar. In diesem Blog soll aber nun einmal der Datentyp ENUM vorgestellt werden.

Ein „stringentes" Beispiel

Um das Thema besser erklären zu können, brauchen wir ein Beispiel. Nehmen wir einmal an, dass wir in unserem Datenmodell (natürlich historisch gewachsen) eine (hier verkürzte) Tabelle mit Kundeninformationen haben. Hier besteht diese nur aus den folgenden Spalten:

p_id (Primärschlüssel)
anrede
nachname

mysql> desc person; 
+----------+-------------+------+-----+---------+----------------+ 
| Field    | Type        | Null | Key | Default | Extra          | 
+----------+-------------+------+-----+---------+----------------+ 
| p_id     | int         | NO   | PRI | NULL    | auto_increment | 
| anrede   | varchar(10) | YES  |     | NULL    |                | 
| nachname | varchar(20) | YES  |     | NULL    |                | 
+----------+-------------+------+-----+---------+----------------+ 
3 rows in set (0.00 sec) 

mysql> select * from person limit 5; 
+------+--------+-----------+ 
| p_id | anrede | nachname  | 
+------+--------+-----------+ 
|   12 | Herr   | BERRY     | 
|   14 | divers | BERGEN    | 
|   47 | divers | BARRYMORE | 
|   58 | Frau   | AKROYD    | 
|   60 | Herr   | BERRY     | 
+------+--------+-----------+ 
5 rows in set (0.01 sec)

Ein paar weitere Eckdaten zu der Tabelle, die ggf. im Laufe des Beitrages noch interessant werden könnten:

Die Tabelle ist ca. 8,5 MB groß, hat eine durchschnittliche Satzlänge von 89 Bytes, besitzt ca. 100.000 Datensätze und wird von der Engine InnoDB verwaltet.

mysql> select  
round((data_length + index_length)/1024/1024,2) 'size mb', AVG_ROW_LENGTH 'row length bytes', TABLE_ROWS '# rows', ENGINE 'engine'  from information_schema.tables where table_name = 'person'; 
+---------+------------------+--------+--------+ 
| size mb | row length bytes | # rows | engine | 
+---------+------------------+--------+--------+ 
|    8.52 |               89 |  99334 | InnoDB | 
+---------+------------------+--------+--------+ 
1 row in set (0.00 sec)

(E)nummerier das doch einfach.

In unserem Beispiel werden wir nun das VARCHAR-Feld „anrede“ in ein ENUM-Attribut konvertieren:

mysql> alter table person modify anrede enum('Frau', 'Herr', 'divers'); 
Query OK, 100000 rows affected (0.31 sec) 
Records: 100000  Duplicates: 0  Warnings: 0

Was hat dies nun direkt für Folgen für die oben erhobenen „Eckdaten“:

mysql> select round((data_length + index_length)/1024/1024,2) 'size mb', AVG_ROW_LENGTH 'row length bytes', TABLE_ROWS '# rows', ENGINE 'engine'  from information_schema.tables where table_name = ‘person’ 
+---------+------------------+--------+--------+ 
| size mb | row length bytes | # rows | engine | 
+---------+------------------+--------+--------+ 
|    3.52 |               36 | 100121 | InnoDB | 
+---------+------------------+--------+--------+ 
1 row in set (0.00 sec)

Wie wir sehen, ist die Größe der Tabelle entscheidend kleiner geworden. Ohne in die Details zu gehen, sei hier angemerkt, dass dies mehrere Vorteile hat:

Speicherplatz (auf Platte) wird gespart.

Die Satzlänge ist deutlich kürzer geworden. Damit passen mehr Informationen in eine MySQL-Page (Block). Pro I/O-Vorgang können nun deutlich mehr Datensätze bewegt werden.
Auch im Speicher (InnoDB Buffer) können jetzt mehr Daten (da diese ja pro Satz kleiner geworden sind) gehalten werden.
Daten werden validiert (siehe nächstes Code-Beispiel).

mysql> insert into person values (null, 'Firma', 'ORDIX AG'); 
ERROR 1265 (01000): Data truncated for column 'anrede' at row 1

Unbekannte Werte (z. B. neue Anreden wie „Firma“) können nun nicht mehr ad hoc gespeichert werden. Sollte in unserem Fall auch die „Firma“ als Anrede zulässig sein, so muss nun erst eine Schema-Änderung mittels ALTER TABLE Kommando vorgenommen werden.

Eine Frage der Organisation?

Auch hinter den Kulissen hat sich etwas (technisch) verändert. Springen wir noch einmal in der Zeit zurück, zu dem Moment als die Spalte „anrede“ noch den Datentyp VARCHAR verwendet hatte.

Das folgende Kommando hätte das untenstehende Ergebnis geliefert:

mysql> select distinct(anrede) from person order by anrede; 
+--------+ 
| anrede | 
+--------+ 
| divers | 
| Frau   | 
| Herr   | 
+--------+ 
3 rows in set (0.08 sec)

Nach der Änderung auf den Datentypen ENUM sieht das Ergebnis wie folgt aus:

mysql> select distinct(anrede) from person order by anrede; 
+--------+ 
| anrede | 
+--------+ 
| Frau   | 
| Herr   | 
| divers | 
+--------+ 
3 rows in set (0.07 sec)

Sehen sie den Unterschied? Die Sortierreihenfolge hat sich geändert:

Die VARCHAR Spalte wird alphanumerisch sortiert.
Die ENUM-Spalte legt für jeden String einen numerisch aufsteigenden Wert (beginnend mit 1) fest und sortiert die Strings gemäß der entsprechenden Zuweisung. Diese entspricht der Reihenfolge bei der Deklaration („Frau“ => 1, „Herr“ => 2, „divers“ =>3)

Dies kann man auch per SELECT beweisen:

mysql> select distinct(anrede), anrede + 0 from person order by anrede; 
+--------+------------+ 
| anrede | anrede + 0 | 
+--------+------------+ 
| Frau   |          1 | 
| Herr   |          2 | 
| divers |          3 | 
+--------+------------+ 
3 rows in set (0.08 sec)

Fazit: Alles nur String-Theorie?

ENUMs in MySQL sind eine leistungsstarke Funktion, die das Datenbankdesign vereinfacht, die Sicherheit erhöht und die Lesbarkeit verbessern kann. Mit einer vordefinierten Liste zulässiger Werte für eine Spalte und einer manchmal deutlichen Reduzierung des Platzbedarfes sind die Vorteile von ENUMs klar. Es ist jedoch auch wichtig, die Einschränkungen und potenziellen Nachteile der Verwendung von ENUMs zu beachten, z. B. die Notwendigkeit, das Schema zu ändern, wenn sich die zulässigen Werte ändern. Insgesamt können ENUMs, wenn sie richtig verwendet werden, ein wertvolles Werkzeug bei der Arbeit mit String-Daten sein. Ich selbst konnte bei Kunden bereits mehrfach Performanceprobleme durch den Einsatz von ENUM-Datentypen minimieren und oder lösen.

Sie haben Fragen zu MySQL und/oder MariaDB? Sprechen Sie mit uns. Wir beraten Sie gerne!