Wie man Array‑Daten in MySQL mithilfe von JSON verarbeitet (Umfassender Leitfaden mit Beispielen)

1. Einführung

Der Bedarf, Array-Daten in MySQL zu verarbeiten

Datenbanken speichern typischerweise Daten basierend auf relationalen Designprinzipien. Je nach Anwendungsanforderungen gibt es jedoch Fälle, in denen Sie mehrere Werte in einer einzigen Spalte speichern möchten. In solchen Situationen wird eine Datenstruktur, die einem „Array“ ähnelt, nützlich.

Zum Beispiel, betrachten Sie die folgenden Szenarien:

• Speichern mehrerer vom Benutzer ausgewählter Tags.
• Speichern mehrerer Bild-URLs für ein Produkt.
• Kombinieren von Verlauf oder Protokollen in einem einzigen Feld.

Vorteile der Verwendung des JSON-Datentyps

MySQL bietet keinen direkten „Array‑Typ“, aber durch die Verwendung des JSON‑Datentyps können Sie array‑ähnliche Datenstrukturen handhaben. Der JSON‑Typ ist hochflexibel und bietet die folgenden Vorteile:

• Unterstützt verschachtelte Datenstrukturen.
• Ermöglicht einfache Datenmanipulation innerhalb von Abfragen.
• Ermöglicht die Verwaltung mehrerer Datenformate in einem einzigen Feld.

In diesem Artikel stellen wir vor, wie Sie Array‑Daten in MySQL effizient mithilfe des JSON‑Datentyps verarbeiten können.

2. Grundlegendes Wissen über die Handhabung von Arrays mit MySQL JSON

Was ist der JSON-Datentyp?

JSON (JavaScript Object Notation) ist ein leichtgewichtiges und einfaches Datenaustauschformat. In MySQL wurde die native JSON‑Unterstützung ab Version 5.7 eingeführt, sodass Sie JSON‑formatierte Daten direkt in der Datenbank speichern und manipulieren können.

Beispiel: Nachfolgend ein Beispiel für Daten, die in einer JSON‑Spalte gespeichert werden können.

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

Vorteile und Anwendungsfälle des JSON-Datentyps

Die wichtigsten Vorteile der Verwendung des JSON‑Typs sind wie folgt:

1. Flexible Datenstruktur: Sie können Daten variabler Länge verarbeiten, ohne das relationale Schema zu ändern.
2. Effiziente Datenmanipulation: Sie können Daten einfach mit MySQL‑spezifischen Funktionen manipulieren (z. B. JSON_EXTRACT, JSON_ARRAY).
3. Schema‑loses Design: Es ist nicht nötig, das Schema häufig zu ändern, wenn sich die Anwendungsanforderungen ändern.

Beispiel‑Anwendungsfälle:

• Zuweisen mehrerer Kategorien zu Produktinformationen.
• Speichern benutzerdefinierter Einstellungen.
• Verwendung verschachtelter JSON‑Daten in Webanwendungen.

3. Grundlegende JSON-Array-Operationen

Erstellen eines JSON-Arrays

In MySQL können Sie mithilfe der Funktion JSON_ARRAY ganz einfach ein JSON‑Array erstellen. Arrays sind nützlich, wenn mehrere Werte in einer einzigen Spalte gespeichert werden sollen.

Beispiel

Die folgende Abfrage erstellt ein JSON‑Array mit dem Namen tags.

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

Ergebnis:

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

Praktisches Beispiel

Das folgende Beispiel zeigt, wie Sie ein JSON‑Array in der Datenbank mit einer INSERT‑Anweisung speichern können.

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

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

Extrahieren von Daten aus einem JSON-Array

Um Daten, die in einem JSON‑Array gespeichert sind, abzurufen, verwenden Sie die Funktion JSON_EXTRACT. Diese Funktion ermöglicht das einfache Extrahieren bestimmter Elemente aus dem Array.

Beispiel

Das folgende Beispiel ruft das zweite Element im Array ab (Index beginnt bei 0).

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

Ergebnis:

"MySQL"

Abrufen mehrerer Elemente

Sie können auch mehrere Elemente auf einmal abrufen.

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

Hinzufügen, Aktualisieren und Entfernen von Daten

Hinzufügen von Daten zu einem Array

Sie können die Funktion JSON_ARRAY_APPEND verwenden, um neue Daten zu einem bestehenden Array hinzuzufügen.

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

Ergebnis:

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

Aktualisieren von Daten in einem Array

Sie können ein bestimmtes Element im Array mithilfe der Funktion JSON_SET aktualisieren.

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

Ergebnis:

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

Entfernen von Daten aus einem Array

Sie können ein bestimmtes Element aus dem Array mit der Funktion JSON_REMOVE entfernen.

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, die bestimmte Daten enthalten

Um zu prüfen, ob ein JSON-Array bestimmte Daten enthält, verwenden Sie die Funktion JSON_CONTAINS. Diese Funktion bestimmt, ob ein angegebener Wert im JSON-Array vorhanden ist.

Beispiel

Das folgende Beispiel prüft, 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)

Praktisches Beispiel: Bedingte Suche

Um Zeilen zu finden, die einen bestimmten Wert in einem JSON-Array einer Datenbanktabelle enthalten, verwenden Sie JSON_CONTAINS in der WHERE-Klausel.

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

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

Ermitteln der Länge eines Arrays

Um die Anzahl der Elemente in einem JSON-Array zu erhalten, verwenden Sie die Funktion JSON_LENGTH. Diese Funktion gibt die Anzahl der Elemente im Array zurück und ist nützlich für Datenanalyse und bedingte Logik.

Beispiel

Das folgende Beispiel ermittelt die Anzahl der Elemente im Array.

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

Ergebnis:

3

Praktisches Beispiel: Zeilen extrahieren, die eine bestimmte Bedingung erfüllen

Um Zeilen zu extrahieren, bei denen die Anzahl der Elemente größer oder gleich einem bestimmten Wert ist, verwenden Sie JSON_LENGTH in der WHERE-Klausel.

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

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

Fortgeschrittenes Beispiel für bedingte Abfragen

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

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

5. Praktische Beispiele: Verwendung von JSON-Arrays in realen Anwendungsfällen

Wie man Produktkategorien als JSON-Array speichert

In E‑Commerce-Websites und ähnlichen Systemen kann ein Produkt zu mehreren Kategorien gehören. In solchen Fällen können Sie Kategoriedaten effizient mit einem JSON-Array speichern.

Beispiel: Speichern von Produktkategoriedaten

Im Folgenden ein Beispiel für das Erstellen einer JSON-Spalte namens categories in einer Produkttabelle und das Speichern 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 Struktur hält die Daten kompakt, selbst wenn ein Produkt mehreren Kategorien zugeordnet ist.

Abfrage zum Extrahieren von Produkten einer bestimmten Kategorie

Durch die Nutzung des JSON-Datentyps können Sie leicht nach Produkten suchen, die einer bestimmten Kategorie zugeordnet sind.

Abfragebeispiel

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

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

Ergebnis:

Laptop
Smartphone

Diese Abfrage erleichtert das flexible Abrufen von Produktlisten nach Kategorie.

Beispiel: Filtern nach Preisbereich

Schauen wir uns an, wie man JSON-Daten speichert, die Preisinformationen enthalten, und dann Produkte nach einem Preisbereich sucht.

Beispieldaten

Das folgende Beispiel speichert Preisinformationen pro Produkt unter Verwendung 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"]}');

Query Example

Um nach Produkten zu suchen, deren Preis 100.000 oder höher ist, verwenden Sie die Funktion JSON_EXTRACT.

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

Result:

Laptop

JSON mit JSON_TABLE erweitern und Abfragebeispiel

Wenn Sie JSON‑Daten in einem relationalen Format abfragen möchten, ist die Funktion JSON_TABLE sehr nützlich. Diese Funktion ermöglicht es, ein JSON‑Array in eine virtuelle Tabelle zu expandieren.

Example

Das folgende Beispiel expandiert ein JSON‑Array und zeigt jede Kategorie als separate Zeile an.

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

Result:

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

6. Wichtige Überlegungen bei der Verwendung des JSON-Datentyps

Tipps zur Leistungsoptimierung

Obwohl der JSON‑Typ flexibel ist, kann ein schlechtes Design die Datenbankleistung negativ beeinflussen. Im Folgenden finden Sie wichtige Optimierungspunkte.

1. Verwendung von Indizes

In MySQL können Sie keinen Index direkt auf einer JSON‑Spalte erstellen, aber Sie können eine generierte Spalte anlegen und einen bestimmten Schlüssel indexieren.

Beispiel: Erstellen eines Indexes mit einer generierten Spalte

Im folgenden Beispiel wird der Schlüssel price innerhalb der JSON‑Daten als Indexziel verwendet.

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

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

2. Vermeiden Sie zu komplexe JSON‑Strukturen

Tief verschachtelte JSON‑Strukturen können die Lesbarkeit von Abfragen und die Leistung verringern. Wählen Sie beim Entwerfen von Daten die einfachste mögliche JSON‑Struktur.

Gutes Beispiel:

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

Beispiel, das vermieden werden sollte:

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

Wie man Indizes nutzt

Beim Indexieren mit generierten Spalten sollten Sie die folgenden Punkte beachten:

1. Die generierte Spalte muss STORED sein.
2. Verwenden Sie die Funktion JSON_EXTRACT, um einen bestimmten Schlüssel als generierte Spalte zu extrahieren.

Zum Beispiel, um das erste Element des Schlüssels categories zu extrahieren und einen Index zu erstellen, gehen Sie wie folgt vor.

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

Da JSON‑Daten flexibel sind, ist es auch einfacher, Daten im falschen Format zu speichern. Um die Datenintegrität zu wahren, verwenden Sie die folgenden Ansätze.

1. Verwendung von CHECK-Constraints

In MySQL 8.0 und später können Sie die JSON‑Struktur und den Inhalt mit CHECK‑Constraints 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 wird empfohlen, das JSON‑Format auf der Anwendungsseite zu validieren. Programmiersprachen wie PHP und Python können JSON mit ihren Standardbibliotheken validieren.

7. Häufig gestellte Fragen zur Verwendung von Arrays in MySQL

F1: Hat MySQL einen Array-Datentyp?

A1:MySQL hat keinen direkten „Array-Datentyp“. Durch die Verwendung des JSON-Typs können Sie jedoch array‑ähnliche Datenstrukturen handhaben. Mit dem JSON‑Typ können Sie mehrere Werte in einer Spalte speichern und sie über Abfragen manipulieren.

Beispiel:

SELECT JSON_ARRAY('Value 1', 'Value 2', 'Value 3') AS array_example;

Ergebnis:

["Value 1", "Value 2", "Value 3"]

Q2: Können Sie einen Index auf JSON‑Daten erstellen?

A2:Sie können keinen Index direkt auf dem JSON‑Typ selbst erstellen. Sie können jedoch einen bestimmten Schlüssel oder Wert in eine generierte Spalte extrahieren und einen Index auf dieser generierten Spalte erstellen.

Beispiel:

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

Damit können Sie Werte innerhalb von JSON‑Daten effizient durchsuchen.

Q3: Gibt es ein Größenlimit für JSON‑Daten?

A3:Der JSON‑Typ von MySQL kann bis zu 4 GB Daten speichern. Das Verwenden extrem großer JSON‑Dokumente kann jedoch die Leistung verringern, daher sollten Sie Ihre Daten sorgfältig entwerfen.

Empfehlungen:

• Speichern Sie nur die minimal erforderlichen Daten.
• Vermeiden Sie tief verschachtelte JSON‑Strukturen.

Q4: Wie kann ich ein bestimmtes Element in einem JSON‑Array aktualisieren?

A4:Sie können ein bestimmtes Element in einem Array mit der Funktion JSON_SET aktualisieren.

Beispiel:

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

Ergebnis:

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

Q5: Vergleich des JSON‑Typs mit normalem Tabellendesign

A5:Obwohl der JSON‑Typ sehr flexibel ist, hat er andere Eigenschaften im Vergleich zum traditionellen relationalen Datenbankdesign.

8. Zusammenfassung

Vorteile der Verwendung des JSON‑Datentyps für Array‑Operationen in MySQL

In diesem Artikel haben wir den JSON‑Datentyp erklärt, der beim Arbeiten mit array‑ähnlichen Daten in MySQL hilfreich ist. Nachfolgend finden Sie eine Zusammenfassung der wichtigsten behandelten Punkte:

1. Warum den JSON‑Typ verwenden MySQL hat keinen direkten Array‑Typ, aber durch die Verwendung des JSON‑Typs können Sie mehrere Werte in einer Spalte speichern und flexible Datenmanipulation erreichen.
2. Grundlegende JSON‑Operationen

• Wir haben behandelt, wie man JSON‑Arrays erstellt, Daten extrahiert, Daten aktualisiert und Daten entfernt.
• Durch die Verwendung praktischer Funktionen wie JSON_ARRAY, JSON_EXTRACT und JSON_SET können Sie Array‑Daten effizient manipulieren.

1. Suche und Filterung

• Wie man Daten, die bestimmte Werte enthalten, mit JSON_CONTAINS sucht.
• Wie man die Anzahl der Elemente mit JSON_LENGTH abruft und bedingte Filterungen durchführt.

1. Praktische Beispiele Durch reale Anwendungsfälle wie die Verwaltung von Produktkategorien und das Filtern nach Preis haben wir konkrete Methoden gelernt, JSON‑Arrays in Anwendungen anzuwenden.
2. Überlegungen und Optimierung

• Wir haben erklärt, wie man Indexierung mit generierten Spalten einrichtet und die Bedeutung der Validierung von JSON‑Daten betont.

Nächste Schritte bei der Verwendung des JSON‑Datentyps

Durch die Verwendung des JSON‑Typs in MySQL können Sie Daten flexibler verwalten als bei traditionellen relationalen Datenbankdesigns. Dennoch sind gutes Design und Leistungsüberlegungen unerlässlich.

Themen zum nächsten Lernen:

• Verwendung zusammengesetzter Indizes Indexdesign, das JSON‑Daten mit normalen Spalten kombiniert.
• Verwendung fortgeschrittener JSON‑Funktionen Führen Sie komplexere Operationen mit Funktionen wie JSON_MERGE und JSON_OBJECT durch.
• Datenverarbeitung auf Anwendungsebene Wie man MySQL‑JSON‑Daten effizient mit PHP oder Python manipuliert.

Zusammenfassung

Durch diesen Artikel sollten Sie nun verstehen, wie man array‑ähnliche Daten mit MySQLs JSON‑Datentyp effizient handhabt. Durch die Anwendung dieses Wissens können Sie flexiblere und skalierbare Datenbanken entwerfen.