How does MySQL store JSON data generated by PHP?

How does MySQL store JSON data generated by PHP?

Overview:
In web development, we often encounter situations where we need to store JSON data generated by PHP into a MySQL database. This article will introduce how to use PHP and MySQL to store and retrieve JSON data, and provide corresponding code examples.

1. Create a data table
First, we need to create a data table to store JSON data. Assuming that the JSON data we want to store contains an id field and a json_data field, you can use the following SQL statement to create a data table named json_data_table:

CREATE TABLE `json_data_table` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `json_data` JSON NOT NULL,
  PRIMARY KEY (`id`)
)

2. Connect to the database
In PHP, we use the mysqli extension library to connect to the MySQL database. The following code demonstrates how to connect to a MySQL database:

<?php
$host = "localhost";
$user = "root";
$password = "";
$database = "test";

$conn = new mysqli($host, $user, $password, $database);
if ($conn->connect_error) {
    die("连接失败：" . $conn->connect_error);
}
?>

3. Store JSON data into the database
Next, we will use PHP and MySQL to store the generated JSON data into the database. Suppose we have an associative array $data with two fields and want to store it as JSON data. The following code shows how to achieve this:

<?php
$data = array(
    'name' => 'John',
    'age' => 25
);

$jsonData = json_encode($data);

$sql = "INSERT INTO json_data_table (json_data) VALUES ('$jsonData')";

if ($conn->query($sql) === TRUE) {
    echo "JSON数据添加成功";
} else {
    echo "Error: " . $sql . "<br>" . $conn->error;
}

$conn->close();
?>

4. Retrieve JSON data from the database
We can also retrieve the stored JSON data from the database. The code below shows how to retrieve JSON data from a database and decode it into an associative array:

<?php
$sql = "SELECT json_data FROM json_data_table WHERE id = 1";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    while ($row = $result->fetch_assoc()) {
        $jsonData = $row['json_data'];

        $data = json_decode($jsonData, true);

        echo "姓名：" . $data['name'] . "<br>";
        echo "年龄：" . $data['age'] . "<br>";
    }
} else {
    echo "没有数据";
}

$conn->close();
?>

Summary:
This article describes how to store and retrieve JSON data using PHP and MySQL. We can easily store and retrieve data by creating data tables, connecting to the database, storing JSON data to the database, and retrieving JSON data from the database. I hope the content of this article is helpful to you, thank you for reading!

The above is the detailed content of How does MySQL store JSON data generated by PHP?. For more information, please follow other related articles on the PHP Chinese website!