Beispieltest MySQL-Enumerationstyp

Bei der Entwicklung eines Projekts stößt man häufig auf einige Statusfelder, z. B. den Bestellstatus „Zu zahlen“, „Bezahlt“, „Abgeschlossen“, „Erstattet“ usw. In meinen vorherigen Projekten wurden diese Status als Zahlen in der Datenbank gespeichert und dann wurde im PHP-Code mithilfe von Konstanten eine Zuordnungstabelle gepflegt, z. B.:

const STATUS_PENDING = 0;
const STATUS_PAID = 1;
const STATUS_CLOSED = 2;
const STATUS_REFUNDED = 3;

Im tatsächlichen Einsatz stellten wir jedoch fest, dass die Verwendung nicht so einfach ist. Aus verschiedenen Gründen (Tracking-Bugs, vorübergehender statistischer Bedarf usw.) müssen wir uns häufig beim MySQL-Server anmelden, um einige SQL-Abfragen manuell auszuführen. Da viele Tabellen Statusfelder haben, müssen wir beim Schreiben von SQL auf die Zuordnungsbeziehung im PHP-Code verweisen. Wenn wir nicht vorsichtig sind, können wir die Statusnummern verschiedener Tabellen verwechseln und große Probleme verursachen.

Daher werde ich den Enumerationstyp von MySQL verwenden, um verschiedene Zustände in meinem neuen Projekt zu speichern. Während der Verwendung habe ich festgestellt, dass ein Fehler gemeldet wird, wenn ich mit dem Enumerationstyp in der Laravel-Migrationsdatei Änderungen an der Tabelle vornehme (selbst wenn ich ein Feld ändere, das kein Enumerationstyp ist).

[Doctrine\DBAL\DBALException]
Unbekannter Datenbanktyp, Aufzählung angefordert, Doctrine\DBAL\Platforms\MySQL57Platform unterstützt ihn möglicherweise nicht.

Nach einiger Suche stellte ich fest, dass Doctrine MySQL Enum nicht unterstützt. Der Artikel listet drei Nachteile von Enum auf:

Beim Hinzufügen eines neuen Enumerationswertes muss die gesamte Tabelle neu aufgebaut werden, was bei großen Datenmengen mehrere Stunden dauern kann.

Die Sortierregel der Enumerationswerte richtet sich nach der beim Anlegen der Tabellenstruktur festgelegten Reihenfolge und nicht nach der Größe des Literalwertes.

Es ist nicht notwendig, sich auf MySQL zu verlassen, um Enumerationswerte zu validieren. In der Standardkonfiguration wird das Einfügen eines ungültigen Wertes schließlich zu einem Nullwert.

Nach der tatsächlichen Situation des neuen Projekts ist es unwahrscheinlich, dass das Statusfeld sortiert werden muss. Selbst wenn dies der Fall ist, können wir die Reihenfolge beim Entwurf der Tabellenstruktur festlegen, sodass Nachteil 2 ignoriert werden kann. Nachteil 3 kann durch Codestandards, Überprüfung vor dem Einfügen/Aktualisieren usw. vermieden werden. Was Nachteil 1 betrifft, müssen wir einige Tests durchführen.

Prüfungsvorbereitung

Erstellen Sie zunächst eine Tabelle:

TABELLE `enum_tests` ERSTELLEN (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `Status` enum('ausstehend', 'Erfolgreich', 'geschlossen') COLLATE utf8mb4_unicode_ci NICHT NULL,
 PRIMÄRSCHLÜSSEL (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Geben Sie dann 1 Million Daten ein:

$Anzahl = 1000000;
$bulk = 1000;
$daten = [];
foreach (['ausstehend', 'erfolgreich', 'geschlossen'] als $status) {
  $daten[$status] = [];
  für ($i = 0; $i < $bulk; $i++) {
    $data[$status][] = ['status' => $status];
  }
}

für ($i = 0; $i < $count; $i += $bulk) {
  $status = array_random(['ausstehend', 'erfolgreich', 'geschlossen']);
  EnumTest::insert($data[$status]);
}

Testprozess

Prüfung 1#

Fügen Sie am Ende der Enumerationswertliste einen Rückerstattungswert hinzu

ALTER TABLE `enum_tests` CHANGE `status` `status` ENUM('ausstehend', 'erfolgreich', 'geschlossen', 'rückerstattet') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL;

Ausgabe:

Abfrage OK, 0 Zeilen betroffen (0,04 Sek.)
Datensätze: 0 Duplikate: 0 Warnungen: 0

Fazit: Das Anhängen eines Enumerationswerts am Ende verursacht fast keine Kosten.

Prüfung 2:#

Löschen Sie die Rückerstattung für den gerade hinzugefügten Wert

ALTER TABLE `enum_tests` CHANGE `status` `status` ENUM('ausstehend', 'erfolgreich', 'geschlossen') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL;

Ausgabe:

Abfrage OK, 1.000.000 Zeilen betroffen (5,93 Sek.)
Datensätze: 1000000 Duplikate: 0 Warnungen: 0

Fazit: Das Löschen eines nicht verwendeten Enumerationswerts erfordert immer noch einen vollständigen Tabellenscan, der zwar kostspielig, aber immer noch in einem akzeptablen Rahmen liegt.

Prüfung 3:#

Rückerstattung in der Mitte der Werteliste einfügen statt am Ende

ALTER TABLE `enum_tests` CHANGE `status` `status` ENUM('ausstehend', 'erfolgreich', 'rückerstattet', 'geschlossen') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL;

Ausgabe:

Abfrage OK, 1000000 Zeilen betroffen (6,00 Sek.)
Datensätze: 1000000 Duplikate: 0 Warnungen: 0

Fazit: Das Hinzufügen eines neuen Werts in der Mitte der ursprünglichen Enumerationswerteliste erfordert einen vollständigen Tabellenscan und eine Aktualisierung, was kostspielig ist.

Prüfung 4:#

Entfernen Sie den Wert in der Mitte einer Werteliste

ALTER TABLE `enum_tests` CHANGE `status` `status` ENUM('ausstehend', 'erfolgreich', 'geschlossen') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL;

Ausgabe:

Abfrage OK, 1.000.000 Zeilen betroffen (4,23 Sek.)
Datensätze: 1000000 Duplikate: 0 Warnungen: 0

Fazit: Es muss die komplette Tabelle gescannt werden, was sehr aufwändig ist.

Prüfung 5:

Fügen Sie dem Statusfeld einen Index hinzu und führen Sie dann den obigen Test aus

ALTER TABLE `enum_tests` ADD INDEX(`status`);

Dabei zeigte sich, dass der Zeitaufwand bei den Tests 2-4 tatsächlich zunahm, was auf die gleichzeitig notwendige Aktualisierung des Index zurückzuführen sein dürfte.

Abschluss

Für mein neues Projekt werden nur neue Enumerationswerte angezeigt. Selbst wenn einige Zustände in Zukunft aufgegeben werden, muss die Enumerationswerteliste nicht angepasst werden. Daher habe ich beschlossen, den Enumerationstyp als Datentyp zum Speichern von Zuständen im Projekt einzuführen.