So verwenden Sie JSON -Datenfelder in MySQL -Datenbanken

Dieser Artikel untersucht die Details der JSON -Implementierung in MySQL 9.1 und setzt das Argument fort Merkmale. Sowohl MySQL 5.7 InnoDB -Datenbank als auch PostgreSQL 9.2 unterstützen direkt das Speichern von JSON -Dokumenttypen in einem einzelnen Feld.

Es ist zu beachten, dass jede Datenbank JSON -Dokumente als einzelne String -Blob speichern kann. MySQL und PostgreSQL unterstützen jedoch das Speichern von validierten JSON-Daten als echte Schlüsselwertpaare anstelle von einfachen Zeichenfolgen.

Schlüsselpunkte

• MySQL 5.7 InnoDB -Datenbank und PostgreSQL 9.2 Unterstützen Sie JSON -Dokumenttypen direkt, sollten jedoch aufgrund der Einschränkung der direkten Indexierung mit Vorsicht verwendet werden.
• json eignet sich am besten zum Speichern von spärlichen Daten, benutzerdefinierten Eigenschaften, Hierarchien und Situationen, in denen Flexibilität erforderlich ist. Es sollte keine normalisierten Spalten ersetzen, die häufig abfragen oder indexieren.
• MySQL bietet verschiedene Funktionen zum Erstellen, Überprüfen, Durchsuchen und Ändern von JSON -Objekten. Diese Funktionen umfassen json_array (), json_object (), json_quote (), json_type (), json_valid (), json_contains (), json_search () und json_set () und json_merge_ zur Aktualisierung von JSON -Dokumenten mit Path Notation Patch () usw. usw. usw. usw. usw. usw. usw. usw. Funktion.
• MySQL 9.1 unterstützt die Funktionsindexierung generierter Spalten, die aus JSON -Daten abgeleitet wurden, und ermöglicht eine effiziente Abfrage bestimmter JSON -Elemente.
• Obwohl MySQL JSON unterstützt, handelt es sich immer noch um eine relationale Datenbank. Überbeanspruchung von JSON kann die Vorteile von SQL ausgleichen.

speichern JSON -Dokumente in der MySQL JSON -Spalte nicht, dass Sie dies

tun sollten

Normalisierung ist eine Technologie zur Optimierung der Datenbankstruktur. Die erste Regel der Normalformel (1NF) sieht vor, dass jede Spalte einen einzelnen Wert enthalten sollte-und das Speichern von JSON-Dokumenten mit mehreren bewerteten JSON-Dokumenten verstößt offensichtlich gegen diese Regel.

Wenn Ihre Daten klare relationale Datenanforderungen haben, verwenden Sie bitte das entsprechende Einzelwertfeld. JSON sollte als letztes Mittel mit Vorsicht verwendet werden. JSON Value Fields können nicht direkt indiziert werden. Vermeiden Sie sie daher, sie in häufig aktualisierten oder durchsuchten Spalten zu verwenden.

Funktionsindexierung generierter Spalten, die aus JSON -Daten abgeleitet wurden, ermöglicht es Ihnen, bestimmte Teile von JSON -Objekten zu indexieren, wodurch die Abfrageleistung verbessert wird.

Das heißt, JSON hat einige gute Anwendungsfälle für spärliche Daten oder benutzerdefinierte Eigenschaften.

Erstellen Sie eine Tabelle mit JSON -Datentypspalte

Betrachten Sie ein Geschäft, das Bücher verkauft. Alle Bücher haben ID, ISBN, Titel, Verlag, Seitenzahl und andere klare relationale Daten.

Wenn Sie jetzt so viele Kategorie -Tags zu jedem Buch hinzufügen möchten. Sie können es in SQL mit der folgenden Methode implementieren:

1. Eine Tag -Tabelle, die jeden Tag -Namen und seine eindeutige ID speichert;
Eine Tag -Mapping -Tabelle mit vielen zu vielen Datensätzen, die die Buch -ID in die Tag -ID zuordnen.

Diese Methode funktioniert, aber für eine sekundäre Funktion ist sie zu umständlich und erfordert erhebliche Anstrengungen. Daher können Sie ein MySQL JSON -Feld für das Tag in der Buchtabelle der MySQL -Datenbank definieren:

CREATE TABLE `book` (
  `id` MEDIUMINT() UNSIGNED NOT NULL AUTO_INCREMENT,
  `title` VARCHAR(200) NOT NULL,
  `tags` JSON DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=INNODB;

Mysql JSON -Spalten können keine Standardwerte haben, können nicht als Primärschlüssel verwendet werden, können nicht als Fremdschlüssel verwendet werden und können keine direkten Indizes haben.

Mit MySQL 9.1 können Sie jedoch Funktionsindizes für generierte Spalten erstellen, die aus JSON -Daten abgeleitet wurden, die die Indizierung spezifischer Elemente in JSON -Dokumenten ermöglicht. Diese erzeugten Spalten können virtuell oder als Hilfsinizes gespeichert und indiziert werden.

ALTER TABLE book
ADD COLUMN first_tag VARCHAR(50) AS (JSON_UNQUOTE(tags->'$[0]')),
ADD INDEX idx_first_tag (first_tag);

add JSON -Daten

Das gesamte JSON -Dokument kann in einer Einfügungs- oder Aktualisierungsanweisung übergeben werden, um JSON für Speicher und Betrieb auf MySQL zu verschieben.

Zum Beispiel können unsere Buch -Tags als Array (innerhalb einer Zeichenfolge) übergeben werden:

INSERT INTO `book` (`title`, `tags`)
VALUES (
  'ECMAScript 2015: A SitePoint Anthology',
  '["JavaScript", "ES2015", "JSON"]'
);

Sie können JSON auch mit den folgenden Funktionen erstellen:

json_array () Funktion, verwendet, um Arrays zu erstellen. Zum Beispiel: - Rückkehr [1, 2, "ABC"]: Wählen Sie JSON_Array (1, 2, 'ABC');
json_object () Funktion, verwendet, um Objekte zu erstellen. Zum Beispiel: - Rückgabe {"A": 1, "B": 2}: Wählen Sie JSON_OBJECT ('A', 1, 'B', 2);
JSON_QUOTE () -Funktion, verwendet, um Zeichenfolgen als JSON -Werte zu verweisen. Zum Beispiel: - return "[1, 2," ABC "]": Wählen Sie JSON_QUOTE ('[1, 2, "ABC"]');
Cast (Anyvalue as JSON) Funktion, um Werte in JSON -Typen umzuwandeln, um die Gültigkeit zu gewährleisten: Wählen Sie Cast ('{"A": 1, "B": 2}' als JSON);
Mit

JSON_TYPE () können Sie den Typ des JSON -Werts überprüfen. Es sollte Objekt, Array, Skalartyp (Integer, Boolean usw.), Null oder Fehler zurückgeben. Zum Beispiel:

-- 返回ARRAY：
SELECT JSON_TYPE('[1, 2, "abc"]');

-- 返回OBJECT：
SELECT JSON_TYPE('{"a": 1, "b": 2}');

-- 返回错误：
SELECT JSON_TYPE('{"a": 1, "b": 2');

JSON_VALID () -Funktion gibt 1 zurück, wenn JSON gültig ist. Andernfalls wird 0 zurückgegeben:

-- 返回1：
SELECT JSON_TYPE('[1, 2, "abc"]');

-- 返回1：
SELECT JSON_TYPE('{"a": 1, "b": 2}');

-- 返回0：
SELECT JSON_TYPE('{"a": 1, "b": 2');

versucht, ein ungültiges JSON -Dokument einzufügen, wirft einen Fehler auf und der gesamte Datensatz wird nicht eingefügt/aktualisiert.

Suche nach JSON -Dokumenten in der MySQL JSON -Spalte

Verwenden von MySQL -JSON -Funktionen wie JSON_Contains () -Funktion können Sie überprüfen, ob das JSON -Dokument einen bestimmten Wert enthält. Gibt 1 zurück, wenn ein Match gefunden wird. Zum Beispiel:

-- 所有带有“JavaScript”标签的书籍：
SELECT * FROM `book` WHERE JSON_CONTAINS(tags, '["JavaScript"]');

JSON_SEARCH () -Funktion gibt den Pfad zum Wert im JSON -Dokument zurück. Gibt NULL zurück, wenn es kein Übereinstimmung gibt.

Sie können auch angeben, ob Sie alle Übereinstimmungen oder eine einzelne Übereinstimmung finden müssen, indem Sie die "One "- und" All "-Flags und -Suchketten übergeben (wobei % eine beliebige Anzahl von Zeichen übereinstimmt, _ mit einem Zeichen wie ähnlich). Zum Beispiel:

-- 所有标签以“Java”开头的书籍：
SELECT * FROM `book` WHERE JSON_SEARCH(tags, 'one', 'Java%') IS NOT NULL;

JSON_TABLE () -Funktion wandelt JSON -Daten in relationale Formate um, um die Abfrage zu erleichtern:

SELECT * 
FROM JSON_TABLE(
    '[{"tag": "SQL"}, {"tag": "JSON"}]', 
    '$[*]' COLUMNS (tag VARCHAR(50) PATH '$.tag')
) AS tags_table;

JSON Path

MySQL JSON -Abfrage mit der Funktion json_extract () kann spezifische Werte aus JSON -Dokumenten basierend auf dem angegebenen Pfad abrufen.

CREATE TABLE `book` (
  `id` MEDIUMINT() UNSIGNED NOT NULL AUTO_INCREMENT,
  `title` VARCHAR(200) NOT NULL,
  `tags` JSON DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=INNODB;

Alle Pfaddefinitionen beginnen mit $, gefolgt von anderen Selektoren:

• Eine Zeit, die ein Name gefolgt ist, z. B. $ .website
• [n], wobei n die Position im Null -Index -Array
ist
• . [*] Alle Mitglieder des Wildcard -Bewertungsobjekts
[*] Wildcard -Bewertungsarray Alle Mitglieder
Präfix ** Suffix Wildcard bewertet alle Pfade, die mit einem benannten Präfix beginnen und mit einem benannten Suffix

enden

Das folgende Beispiel bezieht sich auf das folgende JSON -Dokument:

ALTER TABLE book
ADD COLUMN first_tag VARCHAR(50) AS (JSON_UNQUOTE(tags->'$[0]')),
ADD INDEX idx_first_tag (first_tag);

Pfad Beispiel:

$. A Return 1
$. C Return [3, 4]
$. C [1] return 4
$. D.E RETORT 5
$ **. E Return [5]

Sie können JSON verwenden, um MySQL -Funktionen zu extrahieren, um den Namen und das erste Tag aus Ihrer Buchtabelle effektiv zu extrahieren:

INSERT INTO `book` (`title`, `tags`)
VALUES (
  'ECMAScript 2015: A SitePoint Anthology',
  '["JavaScript", "ES2015", "JSON"]'
);

Für komplexere Beispiele verfügen Sie über eine Benutzertabelle mit JSON -Konfigurationsdateidaten. Zum Beispiel:

id	name	profile
1	Craig	{ "email": ["craig@email1.com", "craig@email2.com"], "twitter": "@craigbuckler" }
2	SitePoint	{ "email": [], "twitter": "@sitepointdotcom" }

<🎜>

Sie können den JSON -Pfad verwenden, um den Twitter -Namen zu extrahieren. Zum Beispiel:

CREATE TABLE `book` (
  `id` MEDIUMINT() UNSIGNED NOT NULL AUTO_INCREMENT,
  `title` VARCHAR(200) NOT NULL,
  `tags` JSON DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=INNODB;

Sie können den JSON -Pfad in der WHERE -Klausel verwenden, um nur Benutzer mit einem Twitter -Konto zurückzugeben:

ALTER TABLE book
ADD COLUMN first_tag VARCHAR(50) AS (JSON_UNQUOTE(tags->'$[0]')),
ADD INDEX idx_first_tag (first_tag);

einen Teil des JSON -Dokuments

ändern

Es gibt mehrere MySQL -Funktionen, die einen Teil eines JSON -Dokuments mithilfe der Pfadnotation ändern können. Diese Funktionen umfassen:

• json_set (doc, path, val [, path, val]…): Daten einfügen oder aktualisieren Sie in das Dokument. Update Book Set Tags = json_set (Tags, '$ [0]', 'aktualisiertes Tag');
• json_insert (doc, path, val [, path, val]…): Daten in das Dokument einfügen, ohne vorhandene Werte zu überschreiben. Update Book Set Tags = json_insert (Tags, '$ [0]', 'New Tag');
• json_replace (doc, path, val [, path, val]…): Daten im Dokument ersetzen. Update Book Set tags = json_replace (Tags, '$ [0]', 'ersetztes Tag');
• JSON_MERGE_PATCH (DOC, DOC [, DOC]…): Führen Sie zwei oder mehr JSON -Dokumente zusammen und ersetzen Sie vorhandene Schlüssel durch die Werte nachfolgender Dokumente. Update Book Set Tags = json_merge_patch (Tags, '["technische"]'), wobei JSON_SEARCH (Tags, 'One', 'JavaScript') nicht null ist;
• json_array_append (doc, path, val [, path, val]…): Gehen Sie den Wert am Ende des Arrays an. Update Book Set Tags = json_array_append (Tags, '$', 'New Tag');
• json_array_insert (doc, path, val [, path, val]…): Fügen Sie Werte an einem bestimmten Ort im JSON -Array ein. Update Book Set Tags = json_array_insert (Tags, '$ [0]', 'eingefügtes Tag');
• JSON_REMOVE (DOC, Pfad [, Pfad]…): Daten aus dem Dokument löschen. Update Book Set Tags = json_remove (Tags, '$ [1]');
• json_pretty (val): verschönern Sie JSON -Dokumente, um sie leichter zu lesen. Wählen Sie json_pretty ('{"name": "sitepoint", "tags": ["mysql", "json"]}');

Wenn Sie beispielsweise einem Buch mit einem "javaScript" -Tag ein "technisches" Tag hinzufügen möchten, können Sie die Funktion json_merge_patch () verwenden:

INSERT INTO `book` (`title`, `tags`)
VALUES (
  'ECMAScript 2015: A SitePoint Anthology',
  '["JavaScript", "ES2015", "JSON"]'
);

Weitere Informationen

MySQL -Dokumentation enthält detaillierte Informationen zu MySQL JSON -Datentypen und zugehörigen JSON -Funktionen.

Erinnern Sie noch einmal, verwenden Sie JSON nicht, es sei denn, dies ist absolut erforderlich. Sie können die gesamte dokumentorientierte NoSQL-Datenbank in MySQL simulieren, dies wird jedoch viele der Vorteile von SQL ausgleichen, und Sie können auch direkt auf ein echtes NoSQL-System wechseln!

dh für die vagen Datenanforderungen in SQL -Anwendungen können JSON -Datentypen einige Anstrengungen sparen.

Häufig gestellte Fragen zur Verwendung von JSON -Daten in MySQL

Können Sie JSON in MySQL verwenden?

MySQL unterstützt JSON durch Bereitstellung eines JSON -Datentyps, mit dem Daten in JSON -Format in Spalten gespeichert werden. Ausgehend von MySQL 5.7.8 können Sie Tabellen mit JSON -Spalten erstellen, mit denen Sie JSON -Daten mit SQL einfügen, aktualisieren und abfragen können. MySQL bietet eine Reihe von JSON -Funktionen, um JSON -Daten in diesen Spalten zu verarbeiten, damit JSON -Daten extrahiert, modifiziert und manipuliert werden können.

Zusätzlich können Sie JSON -Daten in SQL -Abfragen verwenden und sie unter Verwendung von Funktionen wie json_table bei Bedarf in relationale Daten umwandeln. Es ist jedoch wichtig zu verstehen, dass MySQL im Grunde eine relationale Datenbank mit JSON-Datentyp-Unterstützung ist, die die Verarbeitung von JSON-Daten in einem relationalen Kontext erleichtert, anstatt eine vollwertige NOSQL-JSON-Datenbank zu werden.

Wie oben erwähnt, bedeutet dies, dass Sie dies nicht speichern können. Sie sollten dies tun: Normalisierung ist eine Technik zur Optimierung der Datenbankstruktur. In der ersten Regel der Normalformel (1NF) heißt es, dass jede Spalte einen einzelnen Wert enthalten sollte-und das Speichern mehrwertiger JSON-Dokumente verstößt gegen diese Regel.

Ist es in Ordnung, JSON in MySQL zu speichern?

speichern JSON in MySQL ist in Ordnung, wenn:

• semi-strukturierte oder dynamische Daten, nicht für strenge Muster geeignet.
• benutzerdefinierte Eigenschaften, bei denen Beziehungsdesign ineffizient ist.
• Integriert in die JSON -API zum Speichern von Nutzlasten oder Protokollen.

JSON sollte jedoch nicht die normalisierte relationale Speicherung von strukturierten und häufig abfragenden Daten ersetzen. Während MySQL 9.1 die JSON -Funktionalität mit Funktionen wie Funktionsindexierung und JSON_TABLE verbessert, können JSON -Operationen für große Datensätze oder komplexe Abfragen immer noch dazu führen.

Wie kann ich JSON in MySQL -Abfrage verwenden?

Sie können JSON in MySQL -Abfragen verwenden, indem Sie die JSON -Funktion von MySQL verwenden. Diese Funktionen ermöglichen es Ihnen, JSON -Daten zu extrahieren, zu manipulieren und abzufragen, die in JSON -Saiten in JSON -Spalten oder Datenbanken gespeichert sind. Um auf JSON -Daten in einer JSON -Spalte zuzugreifen, verwenden Sie den -& gt;

JSON_EXTRACT, JSON_SET und JSON_OBJECTAGG und andere JSON -Funktionen ermöglichen es Ihnen, JSON -Daten zu filtern, zu ändern, zu aggregieren und zu verarbeiten. Sie können auch die WHERE -Klausel verwenden, um Zeilen basierend auf JSON -Werten zu filtern. Die JSON -Funktionalität von Mysql bietet eine flexible Möglichkeit, JSON -Objekte direkt in Datenbankabfragen zu interagieren und zu manipulieren.

Wann sollte ich JSON in MySQL verwenden?

Sie sollten JSON unter folgenden Umständen in MySQL verwenden:

1. semi-strukturierte Daten: Verwenden Sie JSON, wenn Sie unvorhersehbare oder spärliche Felder wie benutzerdefinierte Eigenschaften behandeln.
2. Dynamischer Modus: JSON bietet Flexibilität, wenn sich die Datenanforderungen häufig ändern.
3. Hydraft oder verschachtelte Daten: JSON unterstützt Daten mit Eltern-Kind-Beziehungen oder Arrays.
4. API -Integration: Nutzlasten, Antworten oder Protokolle als JSON -Dokumente speichern.

Vermeiden Sie jedoch, JSON zu verwenden, wenn:

• häufig abfragte Felder, die eine Indexierung erfordern (Funktionsindexierung kann helfen, das Beziehungsdesign ist jedoch normalerweise schneller).
• strenge Beziehungsdaten, die standardisiert sind, sind erforderlich.
• komplexe Abfragen auf JSON -Pfaden können die Leistung beeinträchtigen.

Wie speichern Sie JSON -Daten in MySQL?

Um JSON -Daten in MySQL zu speichern, haben Sie zwei Hauptoptionen. Zunächst können Sie den in MySQL eingeführten JSON -Datentyp verwenden, um eine Tabelle mit JSON -Spalten zu erstellen. Diese Methode bietet einen strukturierten Speicher und eine bessere Abfrageleistung für JSON -Daten.

Alternativ können Sie JSON -Daten als Text in den regulären Varchar- oder Textspalten speichern. Diese Methode gilt, wenn sie in erster Linie JSON -Daten ohne komplexe Datenbankvorgänge speichern und abrufen müssen.

Wie kann ich JSON -Daten in MySQL indexieren?

Obwohl Sie JSON -Spalten nicht direkt indexieren können, können Sie mit MySQL Funktionsindizes für generierte Spalten erstellen, die von JSON -Werten abgeleitet wurden.

Zum Beispiel, um das erste Element des JSON -Arrays zu indizieren:

CREATE TABLE `book` (
  `id` MEDIUMINT() UNSIGNED NOT NULL AUTO_INCREMENT,
  `title` VARCHAR(200) NOT NULL,
  `tags` JSON DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=INNODB;

Diese Methode verbessert die Abfrageleistung für häufig auf JSON -Pfade.

Sollten Sie für JSON -Daten die MySQL- oder NoSQL -Datenbank verwenden?

Es hängt von Ihren Projektanforderungen ab:

• Wenn Sie einen relationalen Speicher benötigen, wählen Sie gelegentlich semi-strukturierte Daten, benutzerdefinierte Eigenschaften oder hierarchische Daten in relationalen Modellen aus MySQL.
• Wenn Ihr Projekt viel JSON-Speicher, flexible Schemas und dokumentbasierte Vorgänge als Hauptanwendungsfälle umfasst, wählen Sie NoSQL-Datenbanken (wie MongoDB).

Die JSON -Unterstützung von

Mysql ist ideal für Hybrid -Workloads, ist jedoch kein vollständiger Ersatz für NoSQL -Datenbanken speziell für den Dokumentspeicher.

Wie extrahiert man spezifische Werte aus dem Feld MySQL JSON?

Um einen bestimmten Wert aus dem Feld MySQL JSON zu extrahieren, verwenden Sie die Funktion json_extract () oder die Abkürzung -& gt;

ALTER TABLE book
ADD COLUMN first_tag VARCHAR(50) AS (JSON_UNQUOTE(tags->'$[0]')),
ADD INDEX idx_first_tag (first_tag);

Wie kann und filtern Sie Daten in MySQL JSON -Feldern ab?

Um Daten abzufragen und zu filtern, die in MySQL JSON -Feldern gespeichert sind, können Sie Funktionen wie json_contains () und json_search () verwenden. Sie können auch json_extract () verwenden, um bestimmte Werte für die weitere Filterung abzurufen.

INSERT INTO `book` (`title`, `tags`)
VALUES (
  'ECMAScript 2015: A SitePoint Anthology',
  '["JavaScript", "ES2015", "JSON"]'
);

Das obige ist der detaillierte Inhalt vonSo verwenden Sie JSON -Datenfelder in MySQL -Datenbanken. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!