Effiziente Verwaltung von Array-ähnlichen Daten in MySQL mit JSON-Typen

1. Einführung

Der Bedarf, array-ähnliche Daten in MySQL zu verarbeiten

In Datenbanken werden Daten in der Regel nach relationalem Design gespeichert. Je nach Anwendungsanforderungen kann es jedoch sinnvoll sein, mehrere Werte in einer Spalte zu speichern. In solchen Fällen kann eine Datenstruktur wie ein „Array“ hilfreich sein.

• Mehrere Tags, die ein Benutzer auswählt, speichern.
• Mehrere Bild-URLs für ein Produkt speichern.
• Geschichte oder Logs in einem Feld zusammenfassen.

Vorteile der Nutzung des JSON-Typs

MySQL bietet keinen direkten „Array“-Typ, jedoch können Sie mit dem JSON-Typ array-ähnliche Daten verarbeiten. Der JSON-Typ ist sehr flexibel und bietet folgende Vorteile:

• Unterstützung verschachtelter Datenstrukturen.
• Ermöglicht direkte Datenmanipulation innerhalb von Abfragen.
• Verwaltung mehrerer Datenformate innerhalb eines Feldes.

In diesem Artikel zeigen wir, wie man array-ähnliche Daten in MySQL mithilfe des JSON-Typs effizient verwalten kann.

2. Grundlagen der Handhabung von Arrays mit dem JSON-Typ von MySQL

Was ist der JSON-Typ?

JSON (JavaScript Object Notation) ist ein leichtgewichtiges und einfaches Datenformat für den Datenaustausch. MySQL unterstützt ab Version 5.7 einen nativen JSON-Typ, sodass Sie JSON-formatierte Daten direkt in der Datenbank speichern und manipulieren können.

Example: the following is data storable in a JSON type field:

{
  "tags": ["PHP", "MySQL", "JSON"],
  "status": "published"
}

Vorteile und Anwendungsfälle des JSON-Typs

Die wichtigsten Vorteile der Nutzung des JSON-Typs sind:

1. Flexible Datenstruktur: Sie können variablendimensionale Daten verarbeiten, ohne das relationale Schema zu verändern.
2. Effiziente Datenmanipulation: Verwenden Sie MySQLs integrierte Funktionen (z. B. JSON_EXTRACT, JSON_ARRAY), um Daten einfach zu manipulieren.
3. Schema‑loses Design möglich: Sie müssen das Schema nicht häufig ändern, wenn sich die Anforderungsspezifikationen ändern.

Anwendungsfälle:

• Mehrere Kategorien einem Produkt zuweisen.
• Individuelle Benutzereinstellungen speichern.
• In Webanwendungen verwenden, die verschachtelte JSON-Daten verarbeiten.

3. Grundlegende Operationen mit JSON-Arrays

JSON-Arrays erstellen

In MySQL können Sie die Funktion JSON_ARRAY verwenden, um JSON-formatierte Arrays einfach zu erstellen. Arrays sind hilfreich, wenn Sie mehrere Werte in einer Spalte speichern möchten.

Beispielverwendung

In der folgenden Abfrage erstellen wir ein JSON-Array namens tags.

SELECT JSON_ARRAY('PHP', 'MySQL', 'JavaScript') AS tags;

Ergebnis:

["PHP", "MySQL", "JavaScript"]

Angewandtes Beispiel

Hier ein Beispiel, das ein JSON-Array per INSERT in die Datenbank speichert.

CREATE TABLE articles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tags JSON
);

INSERT INTO articles (tags) 
VALUES (JSON_ARRAY('PHP', 'MySQL', 'JavaScript'));

Daten aus JSON-Arrays extrahieren

Um Daten aus einem JSON-Array abzurufen, verwenden Sie die Funktion JSON_EXTRACT. Diese ermöglicht es Ihnen, spezifische Elemente aus dem Array herauszuholen.

Beispielverwendung

Im folgenden Beispiel rufen wir das zweite Element (0‑basierter Index) des Arrays ab.

SELECT JSON_EXTRACT('["PHP", "MySQL", "JavaScript"]', '$[1]') AS second_tag;

Ergebnis:

"MySQL"

Mehrere Elemente abrufen

Sie können auch mehrere Elemente gleichzeitig abrufen.

SELECT JSON_EXTRACT('["PHP", "MySQL", "JavaScript"]', '$[0]', '$[2]') AS extracted_values;

Hinzufügen, Aktualisieren und Löschen von Daten

Daten zu einem Array hinzufügen

Mit der Funktion JSON_ARRAY_APPEND können Sie neue Daten an ein bestehendes Array anhängen.

SET @tags = '["PHP", "MySQL"]';
SELECT JSON_ARRAY_APPEND(@tags, '$', 'JavaScript') AS updated_tags;

Ergebnis:

["PHP", "MySQL", "JavaScript"]

Daten innerhalb eines Arrays aktualisieren

Sie können ein spezifisches Element im Array mit JSON_SET aktualisieren.

SET @tags = '["PHP", "MySQL", "JavaScript"]';
SELECT JSON_SET(@tags, '$[1]', 'Python') AS updated_tags;

Ergebnis:

["PHP", "Python", "JavaScript"]

Daten aus einem Array entfernen

Verwenden Sie JSON_REMOVE, um ein bestimmtes Element innerhalb des Arrays zu löschen.

SET @tags = '["PHP", "MySQL", "JavaScript"]';
SELECT JSON_REMOVE(@tags, '$[1]') AS updated_tags;

Ergebnis:

["PHP", "JavaScript"]

4. Suchen und Filtern von JSON-Arrays

Suchen nach Arrays mit spezifischen Daten

Um festzustellen, ob ein JSON-Array einen bestimmten Wert enthält, verwenden Sie die Funktion JSON_CONTAINS. Diese Funktion gibt zurück, ob das angegebene JSON-Array den gegebenen Wert enthält.

Beispiel

Im folgenden Beispiel prüfen wir, ob das JSON-Array „MySQL“ enthält.

SELECT JSON_CONTAINS('["PHP", "MySQL", "JavaScript"]', '"MySQL"') AS is_present;

Ergebnis:

1  (if present)
0  (if not present)

Angewandtes Beispiel: Bedingte Suche

Wenn Sie Zeilen in der Datenbank abfragen möchten, die ein JSON-Array mit einem bestimmten Wert enthalten, können Sie JSON_CONTAINS im WHERE-Clause verwenden.

SELECT * 
FROM articles
WHERE JSON_CONTAINS(tags, '"MySQL"');

Diese Abfrage ruft Zeilen ab, bei denen die Spalte tags „MySQL“ enthält.

Ermittlung der Länge eines Arrays

Um die Anzahl der Elemente in einem JSON-Array zu ermitteln, verwenden Sie die Funktion JSON_LENGTH. Dies ist nützlich für Datenanalysen oder bedingte Filterung.

Beispiel

Im folgenden Beispiel ermitteln wir die Anzahl der Elemente im Array.

Ergebnis:

SELECT JSON_LENGTH('["PHP", "MySQL", "JavaScript"]') AS array_length;

Ergebnis:

3

Praktisches Beispiel: Zeilen extrahieren, die einer Bedingung entsprechen

Um Zeilen zu extrahieren, bei denen die Anzahl der Elemente gleich oder größer als ein bestimmter Wert ist, verwenden Sie JSON_LENGTH im WHERE-Clause.

SELECT * 
FROM articles
WHERE JSON_LENGTH(tags) >= 2;

Diese Abfrage wählt Zeilen aus, bei denen die Spalte tags zwei oder mehr Elemente enthält.

Praktisches Beispiel mit bedingten Abfragen

Sie können mehrere Bedingungen für komplexere Suchanfragen kombinieren. Die folgende Abfrage findet Zeilen, bei denen das tags-Array „JavaScript“ enthält und drei oder mehr Elemente hat.

SELECT * 
FROM articles
WHERE JSON_CONTAINS(tags, '"JavaScript"') 
  AND JSON_LENGTH(tags) >= 3;

5. Praktische Anwendungsfälle: Lernen durch reale Szenarien für JSON-Arrays

Wie man Produktkategorien als JSON-Array speichert

Auf E-Commerce-Websites können Produkte mehreren Kategorien angehören. In solchen Fällen kann die Verwendung eines JSON-Arrays zur Speicherung von Kategorieninformationen effizient sein.

Beispiel: Speicherung von Kategoriedaten für Produkte

Unten ist ein Beispiel für die Erstellung einer Tabelle mit einer JSON-Spalte namens categories und die Speicherung mehrerer Kategorien.

CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    categories JSON
);

INSERT INTO products (name, categories) 
VALUES ('Laptop', JSON_ARRAY('Electronics', 'Computers')),
       ('Smartphone', JSON_ARRAY('Electronics', 'Mobile Devices'));

Diese Datenstruktur ermöglicht eine kompakte Speicherung, auch wenn ein Produkt mehreren Kategorien angehört.

Produkte abfragen, die einer bestimmten Kategorie angehören

Durch die Nutzung des JSON-Typs können Sie leicht nach Produkten suchen, die bestimmten Kategorien angehören.

Abfragebeispiel

Die folgende Abfrage findet alle Produkte in der Kategorie „Electronics“.

Ergebnis:

SELECT name 
FROM products
WHERE JSON_CONTAINS(categories, '"Electronics"');

Ergebnis:

Laptop
Smartphone

Diese Abfrage ermöglicht es Ihnen, Produktlisten nach Kategorie flexibel abzurufen.

Beispiel: Filtern nach Preisbereich

Schauen wir, wie man Preisinformationen in JSON speichert und Produkte anhand eines Preisbereichs abfragt.

Datenbeispiel

Unten speichern wir die Produktpreisangaben pro Artikel mithilfe des JSON-Typs.

CREATE TABLE products_with_prices (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    details JSON
);

INSERT INTO products_with_prices (name, details)
VALUES ('Laptop', '{"price": 150000, "categories": ["Electronics", "Computers"]}'),
       ('Smartphone', '{"price": 80000, "categories": ["Electronics", "Mobile Devices"]}');

Abfragebeispiel

Um nach Produkten mit einem Preis von 100.000 oder mehr zu suchen, verwenden Sie JSON_EXTRACT.

SELECT name 
FROM products_with_prices
WHERE JSON_EXTRACT(details, '$.price') >= 100000;

Ergebnis:

Laptop

Verwendung von JSON_TABLE für Ausdehnung und Abfragebeispiele

Wenn Sie JSON-Daten in relationaler Form abfragen möchten, können Sie JSON_TABLE verwenden. Diese Funktion ermöglicht es, ein JSON-Array als virtuelle Tabelle zu erweitern.

Beispielverwendung

Unten ist ein Beispiel, bei dem ein JSON-Array aufgeschlüsselt wird und jede Kategorie als separate Zeile angezeigt wird.

SELECT * 
FROM JSON_TABLE(
    '["Electronics", "Computers", "Mobile Devices"]',
    '$[*]' COLUMNS(
        category_name VARCHAR(100) PATH '$'
    )
) AS categories_table;

Ergebnis:

category_name
--------------
Electronics
Computers
Mobile Devices

6. Vorsichtsmaßnahmen bei der Verwendung des JSON-Typs

Punkte zur Leistungsoptimierung

Obwohl der JSON-Typ sehr flexibel ist, kann er ohne geeignetes Design die Datenbankleistung negativ beeinflussen. Nachfolgend sind die wichtigsten Punkte zur Leistungsoptimierung aufgeführt.

1. Verwendung von Indizes

In MySQL können Sie keinen Index direkt auf eine JSON-Spalte setzen, aber Sie können eine virtuelle Spalte erstellen und einen bestimmten Schlüssel indexieren.

Beispiel: Indexerstellung über virtuelle Spalte

In diesem Beispiel indexieren wir den Schlüssel price innerhalb der JSON-Daten.

ALTER TABLE products_with_prices
ADD COLUMN price INT AS (JSON_EXTRACT(details, '$.price')) STORED,
ADD INDEX idx_price (price);

Durch die Verwendung einer virtuellen Spalte können Sie die Suchleistung bei JSON-Daten erheblich verbessern.

2. Vermeiden Sie übermäßig komplexe JSON-Strukturen

Tief verschachtelte JSON-Strukturen beeinträchtigen die Lesbarkeit von Abfragen und die Leistung. Gestalten Sie die Daten möglichst einfach.

Gutes Beispiel:

{
  "categories": ["Electronics", "Computers"],
  "price": 150000
}

Struktur, die zu vermeiden ist:

{
  "product": {
    "details": {
      "price": 150000,
      "categories": ["Electronics", "Computers"]
    }
  }
}

Effektiver Einsatz von Indizes

Bei der Verwendung virtueller Spalten zur Indexerstellung müssen folgende Punkte berücksichtigt werden:

1. Die virtuelle Spalte muss STORED sein.
2. Verwenden Sie JSON_EXTRACT, um spezifische Schlüssel in die virtuelle Spalte zu extrahieren.

Zum Beispiel, um den Wert des Schlüssels categories zu extrahieren und zu indexieren:

ALTER TABLE products
ADD COLUMN main_category VARCHAR(255) AS (JSON_EXTRACT(categories, '$[0]')) STORED,
ADD INDEX idx_main_category (main_category);

Die Bedeutung der Datenvalidierung

Obwohl JSON-Daten flexibel sind, besteht die Gefahr, dass falsche Formate gespeichert werden. Zur Wahrung der Datenintegrität sollten Sie die folgenden Ansätze verwenden.

1. Verwendung von CHECK-Einschränkungen

Ab MySQL 8.0 können Sie CHECK-Einschränkungen verwenden, um die Struktur oder den Inhalt von JSON-Daten zu validieren.

ALTER TABLE products_with_prices
ADD CONSTRAINT check_price CHECK (JSON_EXTRACT(details, '$.price') >= 0);

2. Validierung auf Anwendungsebene

Beim Einfügen von Daten sollten Sie das JSON-Format auf Anwendungsebene validieren. In Programmiersprachen wie PHP oder Python können Sie Standardbibliotheken verwenden, um JSON zu validieren.

7. Häufig gestellte Fragen zur Verwendung von array-ähnlichen Daten in MySQL

Frage 1: Bietet MySQL einen Array-Typ?

Antwort 1: MySQL verfügt nicht über einen direkten „Array-Typ“. Sie können jedoch array-ähnliche Daten mithilfe des JSON-Typs verarbeiten. Durch Verwendung des JSON-Typs können Sie mehrere Werte in einer Spalte speichern und sie über Abfragen manipulieren.

Beispiel:

SELECT JSON_ARRAY('value1', 'value2', 'value3') AS example_array;

Ergebnis:

["value1", "value2", "value3"]

Frage 2: Können Sie einen Index auf JSON-Daten setzen?

Antwort 2: Sie können keinen Index direkt auf einen JSON-Typ setzen. Sie können jedoch spezifische Schlüssel oder Werte als virtuelle Spalte extrahieren und anschließend einen Index auf diese Spalte setzen.

Beispiel:

ALTER TABLE products_with_prices
ADD COLUMN price INT AS (JSON_EXTRACT(details, '$.price')) STORED,
ADD INDEX idx_price (price);

Dadurch wird eine effiziente Suche nach Werten in JSON-Daten ermöglicht.

Frage 3: Gibt es eine Größenbeschränkung für JSON-Daten?

Antwort 3:
Der JSON-Typ von MySQL kann bis zu 4 GB Daten speichern. Allerdings kann die Verwendung sehr großer JSON-Daten die Leistung beeinträchtigen, daher ist ein durchdachtes Daten­design unerlässlich.

Empfehlungen:

• Speichern Sie nur die unbedingt erforderlichen Daten.
• Vermeiden Sie stark verschachtelte JSON‑Strukturen.

Frage 4: Wie aktualisiert man ein bestimmtes Element in einem JSON-Array?

Antwort 4:
Sie aktualisieren bestimmte Elemente innerhalb eines JSON-Arrays mit der Funktion JSON_SET.

Beispiel:

SET @tags = '["PHP", "MySQL", "JavaScript"]';
SELECT JSON_SET(@tags, '$[1]', 'Python') AS updated_tags;

Ergebnis:

["PHP", "Python", "JavaScript"]

Frage 5: Vergleich zwischen dem JSON-Typ und dem traditionellen Tabellendesign

Antwort 5:
Der JSON-Typ bietet hohe Flexibilität, unterscheidet sich jedoch in seiner Natur vom traditionellen relationalen Design.

8. Zusammenfassung

Vorteile der Verarbeitung von array‑ähnlichen Daten mit dem JSON-Typ in MySQL

Dieser Artikel erläutert den JSON-Typ zur Verarbeitung von array‑ähnlichen Daten in MySQL. Die wichtigsten Punkte sind:

1. Warum den JSON-Typ verwenden
   MySQL verfügt nicht über einen direkten Array-Typ, aber durch die Verwendung des JSON‑Typs können Sie mehrere Werte in einer Spalte speichern und flexible Datenoperationen durchführen.

2. Grundlegende JSON‑Operationen

• Lernen, wie man JSON‑Arrays erstellt, Daten extrahiert, Werte aktualisiert und löscht.
• Verwendung von Funktionen wie JSON_ARRAY, JSON_EXTRACT und JSON_SET, um array‑ähnliche Daten effizient zu manipulieren.

1. Suchen und Filtern

• Verwendung von JSON_CONTAINS, um nach bestimmten Werten in JSON‑Arrays zu suchen.
• Verwendung von JSON_LENGTH, um die Array‑Länge zu ermitteln und bedingte Filterungen durchzuführen.

1. Praktische Anwendungsfälle
   Wir haben konkrete Anwendungsszenarien kennengelernt, wie z. B. die Verwaltung von Produktkategorien und die Filterung nach Preis.

2. Vorsichtsmaßnahmen und Optimierung

• Besprochen, wie man JSON‑Daten über virtuelle Spalten indiziert und die Bedeutung der Validierung von JSON‑Daten.

Nächste Schritte zur Nutzung des JSON-Typs

Durch die Verwendung des JSON-Typs in MySQL können Sie eine flexiblere Datenverwaltung ermöglichen als bei traditionellen relationalen Datenbankdesigns. Allerdings sind ein durchdachtes Design und Leistungsüberlegungen unerlässlich.

Themen für das nächste Lernziel:

• Composite-Indexes nutzen – Indizes entwerfen, die JSON‑Typ-Spalten mit regulären Spalten kombinieren.
• Fortgeschrittene JSON‑Funktionen – Verwendung von JSON_MERGE, JSON_OBJECT und anderen Funktionen für komplexere Operationen.
• Manipulation auf Anwendungsebene – MySQL‑JSON‑Daten effizient mit Sprachen wie PHP oder Python manipulieren.

Abschließende Zusammenfassung

In diesem Artikel haben Sie gelernt, wie Sie array‑ähnliche Daten effizient mit dem JSON-Typ in MySQL verarbeiten. Durch die Anwendung dieses Wissens können Sie flexiblere und skalierbarere Datenbanksysteme entwerfen.