How Can You Effectively Create and Insert JSON Data into MySQL Tables?

MySQL Queries: Creating and Inserting JSON Objects

Creating a JSON object and utilizing its values within MySQL queries can present challenges, especially for beginners. This article addresses common issues faced while attempting to establish JSON objects and read their data into MySQL tables.

One common error occurs when attempting to create a table with fields of the JSON datatype. To resolve this, the correct syntax involves setting the field type explicitly as JSON during table creation. For instance:

CREATE TABLE Person (name JSON DEFAULT NULL);

Once the table is created with appropriate datatype, inserting JSON data becomes straightforward. There are two primary methods for inserting JSON data:

1. Inserting JSON Arrays: When inserting JSON arrays, enclose the values within square brackets:

INSERT INTO Person (name) VALUES ('["name1", "name2", "name3"]');

2. Inserting JSON Objects (Key:Value Pairs): Enclose the key-value pairs within curly braces:

INSERT INTO Person VALUES ('{"pid": 101, "name": "name1"}');

To select specific JSON data, utilize the JSON_CONTAINS function:

SELECT * FROM Person WHERE JSON_CONTAINS(name, '["name1"]');

It's crucial to note that JSON support in MySQL is limited to MySQL 5.7 and higher versions, and only works with the InnoDB storage engine.

The above is the detailed content of How Can You Effectively Create and Insert JSON Data into MySQL Tables?. For more information, please follow other related articles on the PHP Chinese website!