JSON-Feld in der MySQL-Datenbank

MyOOS [Dumper]ist ein Sicherungsprogramm für MySQL-Datenbanken. Damit können Sicherungskopien der Daten (Forum, Shop, Blog, usw.) erstellt und bei Bedarf auch wieder hergestellt werden. Besonders bei Web-Space ohne Shell-Zugang bietet sich MyOOS [Dumper] als sinnvolle Alternative an.
Antworten
r23
Beiträge: 2718
Registriert: 18.09.2008, 05:56
Wohnort: Hagen
Kontaktdaten:

JSON-Feld in der MySQL-Datenbank

Beitrag von r23 »

Hallo,

Das JSON-Feld ist eine Spalte, die JSON-Dokumente speichern kann. Um ein JSON-Feld zu aktualisieren, können wir die Funktion JSON_SET() verwenden, die Werte in einem JSON-Dokument einfügt oder aktualisiert.

Die Syntax von JSON_SET() ist wie folgt:

Code: Alles auswählen

JSON_SET(json_document, path, value[, path, value] ...)

- `json_document` ist das JSON-Dokument, das wir aktualisieren möchten.

- `path` ist der Pfad, an dem wir den Wert einfügen oder aktualisieren möchten. Der Pfad beginnt mit einem Dollarzeichen (`$`) und folgt der JSON-Pfadsyntax.

- `value` ist der Wert, den wir einfügen oder aktualisieren möchten. Er kann ein beliebiger gültiger JSON-Wert sein.

wir können mehrere Pfad/Wert-Paare angeben, wenn wir mehrere Werte aktualisieren möchten.

Zum Beispiel, wenn wir das JSON-Feld `dict` in der Tabelle `myjson` aktualisieren möchten, können wir folgenden SQL-Befehl verwenden:

Code: Alles auswählen

UPDATE myjson
SET dict = JSON_SET(dict, '$.variations', 'scheveningen')
WHERE id = 2;
Dieser Befehl fügt den Wert `'scheveningen'` unter dem Schlüssel `'variations'` in das JSON-Dokument `dict` ein, wenn der Schlüssel noch nicht existiert, oder aktualisiert ihn, wenn er bereits existiert. Der Befehl wirkt sich nur auf die Zeile aus, deren `id` gleich `2` ist.

Um diesen Befehl mit PHP auszuführen, können wir die Funktion mysqli_query() verwenden, die eine SQL-Anweisung an eine MySQL-Datenbank sendet. Wir müssen zuerst eine Verbindung zur Datenbank herstellen, indem wir die Funktion mysqli_connect() verwenden. Zum Beispiel:

Code: Alles auswählen

<?php
// Verbindung zur Datenbank herstellen
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myjson";

// Erstellen Sie die Verbindung
$conn = mysqli_connect($servername, $username, $password, $dbname);

// Überprüfen Sie die Verbindung
if (!$conn) {
  die("Connection failed: " . mysqli_connect_error());
}

// SQL-Befehl vorbereiten
$sql = "UPDATE myjson
SET dict = JSON_SET(dict, '$.variations', 'scheveningen')
WHERE id = 2;";

// SQL-Befehl ausführen
if (mysqli_query($conn, $sql)) {
  echo "JSON field updated successfully";
} else {
  echo "Error updating JSON field: " . mysqli_error($conn);
}

// Verbindung schließen
mysqli_close($conn);
Das MOD Projekt soll JSON-Dokumente zuverlässig in einer MySQL sichern und verwalten können. Vor diesem Hintergrund suche ich Anwender, die in ihrer Datenbank JSON-Dokument verwalten.

Für die, die beim MOD helfen möchten

Wie kann ich ein JSON-Feld in MySQL-Datenbank erstellen?

Um ein JSON-Feld in einer MySQL-Datenbank zu erstellen, müssen wir eine Spalte mit dem Datentyp JSON definieren. Zum Beispiel:

Code: Alles auswählen

CREATE TABLE person (
  name JSON DEFAULT NULL
);
Dieser Befehl erstellt eine Tabelle namens `person` mit einer Spalte namens `name`, die JSON-Dokumente speichern kann. Wir können dann JSON-Daten in diese Spalte einfügen, indem wir entweder einen JSON-String oder eine JSON-Funktion verwenden. Zum Beispiel:

Code: Alles auswählen

INSERT INTO person (name) VALUES (' [\"Alice\", \"Bob\", \"Charlie\"]');
oder

Code: Alles auswählen

INSERT INTO person (name) VALUES (JSON_ARRAY('Alice', 'Bob', 'Charlie'));

Beide Befehle fügen ein JSON-Array mit drei Namen in die Spalte `name` ein.

Wie kann ich ein JSON-Feld in PHP auslesen?

Um ein JSON-Feld in PHP auszulesen, müssen wir zuerst das JSON-Dokument aus der MySQL-Datenbank abfragen, indem wir die Funktion mysqli_query() verwenden. Zum Beispiel:

Code: Alles auswählen

<?php
// Verbindung zur Datenbank herstellen
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myjson";

// Erstellen Sie die Verbindung
$conn = mysqli_connect($servername, $username, $password, $dbname);

// Überprüfen Sie die Verbindung
if (!$conn) {
  die("Connection failed: " . mysqli_connect_error());
}

// SQL-Befehl vorbereiten
$sql = "SELECT dict FROM myjson WHERE id = 2;";

// SQL-Befehl ausführen
$result = mysqli_query($conn, $sql);

// Verbindung schließen
mysqli_close($conn);
?>

Dieser Code fragt das JSON-Dokument in der Spalte `dict` aus der Tabelle `myjson` ab, wo die `id` gleich `2` ist. Das Ergebnis wird in der Variablen `$result` gespeichert.

Um das JSON-Dokument in ein PHP-Array oder -Objekt umzuwandeln, können wir die Funktion json_decode() verwenden. Zum Beispiel:

Code: Alles auswählen

<?php
// JSON-Dokument in ein PHP-Array umwandeln
$array = json_decode($result, true);

// JSON-Dokument in ein PHP-Objekt umwandeln
$object = json_decode($result);
Der zweite Parameter von json_decode() bestimmt, ob das Ergebnis ein assoziatives Array (true) oder ein Objekt (false oder weggelassen) sein soll.

Um auf die Werte im PHP-Array oder -Objekt zuzugreifen, können wir die üblichen Syntaxregeln verwenden. Zum Beispiel:

Code: Alles auswählen

<?php
// Auf den Wert unter dem Schlüssel 'variations' im Array zugreifen
echo $array['variations']; //scheveningen

// Auf den Wert unter dem Schlüssel 'variations' im Objekt zugreifen
echo $object->variations; //scheveningen
Wir können auch Schleifen oder andere Funktionen verwenden, um das PHP-Array oder -Objekt zu manipulieren.

Kann ich auch verschachtelte JSON-Dokumente speichern?
Ja, wir können auch verschachtelte JSON-Dokumente in einer MySQL-Datenbank speichern. Ein verschachteltes JSON-Dokument ist ein JSON-Dokument, das andere JSON-Dokumente als Werte enthält. Zum Beispiel:

Code: Alles auswählen

{
  "name": "Alice",
  "address": {
    "street": "Main Street",
    "city": "Berlin",
    "country": "Germany"
  },
  "hobbies": [
    {
      "name": "reading",
      "books": ["Harry Potter", "The Lord of the Rings"]
    },
    {
      "name": "cooking",
      "recipes": ["pizza", "pasta"]
    }
  ]
}
Dieses JSON-Dokument hat zwei verschachtelte JSON-Dokumente als Werte: `address` und `hobbies`. Um ein solches JSON-Dokument in einer MySQL-Datenbank zu speichern, müssen wir eine Spalte mit dem Datentyp JSON definieren, wie ich bereits erklärt habe. Zum Beispiel:

Code: Alles auswählen

CREATE TABLE person (
  name JSON DEFAULT NULL,
  address JSON DEFAULT NULL,
  hobbies JSON DEFAULT NULL
);
Dieser Befehl erstellt eine Tabelle namens `person` mit drei Spalten, die JSON-Dokumente speichern können: `name`, `address` und `hobbies`. Wir können dann das JSON-Dokument in diese Tabelle einfügen, indem wir entweder einen JSON-String oder eine JSON-Funktion verwenden. Zum Beispiel:

Code: Alles auswählen

INSERT INTO person (name, address, hobbies) VALUES (
  '"Alice"',
  '{"street": "Main Street", "city": "Berlin", "country": "Germany"}',
  '[{"name": "reading", "books": ["Harry Potter", "The Lord of the Rings"]}, {"name": "cooking", "recipes": ["pizza", "pasta"]}]'
);
Beide Befehle fügen das JSON-Dokument in die Tabelle `person` ein. Um ein verschachteltes JSON-Dokument zu aktualisieren, können wir die Funktion JSON_SET() verwenden, wie ich bereits gezeigt habe. Zum Beispiel:

Code: Alles auswählen

UPDATE person
SET address = JSON_SET(address, '$.city', 'Munich')
WHERE name = '"Alice"';
Dieser Befehl aktualisiert den Wert von `city` in dem JSON-Dokument `address` für die Zeile, deren `name` gleich `"Alice"` ist. Um ein verschachteltes JSON-Dokument abzufragen, können wir die Funktion JSON_EXTRACT() verwenden, die einen Wert aus einem JSON-Dokument anhand eines Pfades extrahiert. Zum Beispiel:

Code: Alles auswählen

SELECT JSON_EXTRACT(hobbies, '$[0].books[1]') AS favorite_book
FROM person
WHERE name = '"Alice"';
Dieser Befehl gibt den Wert von `books[1]` in dem JSON-Dokument `hobbies` für die Zeile, deren `name` gleich `"Alice"` ist. Das Ergebnis ist `"The Lord of the Rings"`. Wir können auch Wildcards verwenden, um mehrere Werte aus einem JSON-Dokument zu extrahieren. Zum Beispiel:

Code: Alles auswählen

SELECT JSON_EXTRACT(hobbies, '$[*].name') AS hobby_names
FROM person
WHERE name = '"Alice"';
Dieser Befehl gibt die Werte von `name` in allen Elementen des JSON-Dokuments `hobbies` für die Zeile, deren `name` gleich `"Alice"` ist. Das Ergebnis ist `["reading", "cooking"]`.

Das MyOOS Projekt sucht Entwickler, die im TEAM an dem Projekt arbeiten. Der Weg zur Mitarbeit führt über die Lerngemeinschaft. viewtopic.php?t=4706

Beste Grüße

Ralf
r23
Beiträge: 2718
Registriert: 18.09.2008, 05:56
Wohnort: Hagen
Kontaktdaten:

Re: JSON-Feld in der MySQL-Datenbank

Beitrag von r23 »

Hallo,

der erste Test sieht überraschend gut aus.

Die Datensicherung:

Code: Alles auswählen

-- Status:1:1:MP_0:myoos:php:5.0.23::10.4.32-MariaDB:1:::utf8mb4:EXTINFO
--
-- TABLE-INFO
-- TABLE|person|1|16384|2023-12-19 11:30:26|InnoDB
-- EOF TABLE-INFO
--
-- Dump by MyOOS [Dumper] 5.0.23 (http://foren.myoos.de/viewforum.php?f=40)
/*!40101 SET NAMES 'utf8mb4' */;
SET FOREIGN_KEY_CHECKS=0;
-- Dump created: 2023-12-19 11:35

--
-- Create Table `person`
--

DROP TABLE IF EXISTS `person`;
CREATE TABLE `person` (
  `name` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`name`))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;


--
-- Data for Table `person`
--

/*!40000 ALTER TABLE `person` DISABLE KEYS */;
INSERT INTO `person` (`name`) VALUES (' [\"Alice\", \"Bob\", \"Charlie\"]');
/*!40000 ALTER TABLE `person` ENABLE KEYS */;

SET FOREIGN_KEY_CHECKS=1;
-- EOB
Antworten