How to Efficiently Store and Retrieve JSON Data in MySQL Tables?

Creating and Inserting JSON Objects in MySQL Tables

When working with JSON data in MySQL, you may encounter challenges if you're unfamiliar with the process. Here's a step-by-step guide to assist you.

Creating a Table with JSON Data Type

To store JSON data effectively, you'll need to define your table columns with the JSON datatype. For instance:

<code class="sql">CREATE TABLE `person` (
  `name` JSON DEFAULT NULL
);</code>

Inserting JSON Data

There are multiple ways to insert JSON data into your MySQL table:

1. Inserting JSON Arrays

To insert an array of values, enclose them in square brackets and use the JSON_CONTAINS function in your query:

<code class="sql">INSERT INTO `person` (`name`)
VALUES ('["name1", "name2", "name3"]');</code>

2. Inserting JSON Objects (Key: Value Pairs)

To insert individual JSON objects, use curly braces to enclose your key-value pairs:

<code class="sql">INSERT INTO person VALUES ('{"pid": 101, "name": "name1"}');
INSERT INTO person VALUES ('{"pid": 102, "name": "name2"}');</code>

Selecting JSON Data

Once you've inserted your JSON data, you can retrieve specific records using the JSON_CONTAINS function:

<code class="sql">SELECT * FROM `person` WHERE JSON_CONTAINS(name, '["name1"]');</code>

Note: These features require MySQL version 5.7 or higher and InnoDB storage engine.

The above is the detailed content of How to Efficiently Store and Retrieve JSON Data in MySQL Tables?. For more information, please follow other related articles on the PHP Chinese website!