[Original] Introduction to the use of MySQL5.7 JSON type_PHP tutorial

[Original] Introduction to the use of MySQL5.7 JSON type

JSON is a lightweight data exchange format that uses a language-independent text format, similar to XML, but simpler than XML , easy to read and easy to write. It is easy for machines to parse and generate, and will reduce network bandwidth transmission.
The format of JSON is very simple: name/key value. In previous versions of MySQL, to implement such storage, either VARCHAR or TEXT large text was used. After the release of MySQL 5.7, the JSON data type and the retrieval and other function parsing of this type were specially designed. Let’s first look at JSON access in older versions of MySQL.


Example table structure:

<ol style="margin:0 1px 0 0px;padding-left:40px;" start="1" class="dp-css"><li>CREATE TABLE json_test(<br /></li><li>id INT,<br /></li><li>person_desc TEXT<br /></li><li>)ENGINE INNODB;</li></ol>

Let’s insert a record:

<ol style="margin:0 1px 0 0px;padding-left:40px;" start="1" class="dp-css"><li>INSERT INTO json_test VALUES (1,'{<br /></li><li>"programmers": [{<br /></li><li>"firstName": "Brett",<br /></li><li>"lastName": "McLaughlin",<br /></li><li>"email": "aaaa"<br /></li><li>}, {<br /></li><li>"firstName": "Jason",<br /></li><li>"lastName": "Hunter",<br /></li><li>"email": "bbbb"<br /></li><li>}, {<br /></li><li>"firstName": "Elliotte",<br /></li><li>"lastName": "Harold",<br /></li><li>"email": "cccc"<br /></li><li>}],<br /></li><li>"authors": [{<br /></li><li>"firstName": "Isaac",<br /></li><li>"lastName": "Asimov",<br /></li><li>"genre": "sciencefiction"<br /></li><li>}, {<br /></li><li>"firstName": "Tad",<br /></li><li>"lastName": "Williams",<br /></li><li>"genre": "fantasy"<br /></li><li>}, {<br /></li><li>"firstName": "Frank",<br /></li><li>"lastName": "Peretti",<br /></li><li>"genre": "christianfiction"<br /></li><li>}],<br /></li><li>"musicians": [{<br /></li><li>"firstName": "Eric",<br /></li><li>"lastName": "Clapton",<br /></li><li>"instrument": "guitar"<br /></li><li>}, {<br /></li><li>"firstName": "Sergei",<br /></li><li>"lastName": "Rachmaninoff",<br /></li><li>"instrument": "piano"<br /></li><li>}]<br /></li><li>}');</li></ol>

Generally, if we encounter the JSON format stored in this way, we can only take out this record and hand it over to an application, and the application will parse it.


Now that we are in MySQL 5.7, we re-modify the following table structure:

<ol style="margin:0 1px 0 0px;padding-left:40px;" start="1" class="dp-css"><li>ALTER TABLE json_test MODIFY person_desc json;</li></ol>

First look at the inserted row What KEYs are there in JSON data:

<ol style="margin:0 1px 0 0px;padding-left:40px;" start="1" class="dp-css"><li>mysql> SELECT id,json_keys(person_desc) as "keys" FROM json_test\G<br /></li><li>*************************** 1. row ***************************<br /></li><li>id: 1<br /></li><li>keys: ["authors", "musicians", "programmers"]<br /></li><li>1 row in set (0.00 sec)</li></ol>

We can see that there are three KEYs in it, namely authors, musicians, and programmers. . Now find a KEY and take out the corresponding value:

<ol style="margin:0 1px 0 0px;padding-left:40px;" start="1" class="dp-css"><li>mysql> SELECT json_extract(AUTHORS,'$.lastName[0]') AS 'name', AUTHORS FROM<br /></li><li>-> (<br /></li><li>-> SELECT id,json_extract(person_desc,'$.authors[0][0]') AS "authors" FROM json_test<br /></li><li>-> UNION ALL<br /></li><li>-> SELECT id,json_extract(person_desc,'$.authors[1][0]') AS "authors" FROM json_test<br /></li><li>-> UNION ALL<br /></li><li>-> SELECT id,json_extract(person_desc,'$.authors[2][0]') AS "authors" FROM json_test<br /></li><li>-> ) AS T1<br /></li><li>-> ORDER BY NAME DESC\G<br /></li><li>*************************** 1. row ***************************<br /></li><li>name: "Williams"<br /></li><li>AUTHORS: {"genre": "fantasy", "lastName": "Williams", "firstName": "Tad"}<br /></li><li>*************************** 2. row ***************************<br /></li><li>name: "Peretti"<br /></li><li>AUTHORS: {"genre": "christianfiction", "lastName": "Peretti", "firstName": "Frank"}<br /></li><li>*************************** 3. row ***************************<br /></li><li>name: "Asimov"<br /></li><li>AUTHORS: {"genre": "sciencefiction", "lastName": "Asimov", "firstName": "Isaac"}<br /></li><li><br /></li><li><br /></li><li>3 rows in set (0.00 sec)</li></ol>

Now let’s list the detailed values:

<ol style="margin:0 1px 0 0px;padding-left:40px;" start="1" class="dp-css"><li>mysql> SELECT<br /></li><li>-> json_extract(AUTHORS,'$.firstName[0]') AS "firstname",<br /></li><li>-> json_extract(AUTHORS,'$.lastName[0]') AS "lastname",<br /></li><li>-> json_extract(AUTHORS,'$.genre[0]') AS "genre"<br /></li><li>-> FROM<br /></li><li>-> (<br /></li><li>-> SELECT id,json_extract(person_desc,'$.authors[0]') AS "authors" FROM json<br /></li><li>_test<br /></li><li>-> ) AS T\G<br /></li><li>*************************** 1. row ***************************<br /></li><li>firstname: "Isaac"<br /></li><li>lastname: "Asimov"<br /></li><li>genre: "sciencefiction"<br /></li><li>1 row in set (0.00 sec)</li></ol>

Let's further demonstrate deleting all objects corresponding to the authors KEY.

<ol style="margin:0 1px 0 0px;padding-left:40px;" start="1" class="dp-css"><li>mysql> UPDATE json_test<br /></li><li>-> SET person_desc = json_remove(person_desc,'$.authors')\G<br /></li><li>Query OK, 1 row affected (0.01 sec)<br /></li><li>Rows matched: 1  Changed: 1  Warnings: 0</li></ol>

Look for the corresponding KEY and find that it has been deleted.

<ol style="margin:0 1px 0 0px;padding-left:40px;" start="1" class="dp-css"><li>mysql> SELECT json_contains_path(person_desc,'all','$.authors') as authors_exist<br /></li><li>s FROM json_test\G<br /></li><li>*************************** 1. row ***************************<br /></li><li>authors_exists: 0<br /></li><li>1 row in set (0.00 sec)</li></ol>

To summarize, although MySQL 5.7 begins to support the JSON data type, I suggest that if you want to use it, it is best to take out such a value and then use it in the application Segments are calculated. After all, the database is used to process simple data.

http://www.bkjia.com/PHPjc/1069967.htmlwww.bkjia.comtruehttp: //www.bkjia.com/PHPjc/1069967.htmlTechArticle[Original] Introduction to the use of MySQL5.7 JSON type JSON is a lightweight data exchange format that adopts Language-independent text format, similar to XML, but simpler, easier to read and easier to program than XML...