MySQL 5.7 new features | Json Column and Generated Column (medium)-Mysql Tutorial-php.cn

First title

1. Append of JSON_ARRAY_APPEND array class

This usage is mainly to process the value array type data in the JSON field, mainly to append the value, which is not a standard array type. This function will automatically convert to array form, use the previous value as the first element of the array and then append it. There is a batch append method:

mysql> SET @j = &#39;["a", ["b", "c"], "d"]&#39;;

Copy after login

Append to the array of the second value in the field An element 1:

mysql> SELECT JSON_ARRAY_APPEND(@j, &#39;$[1]&#39;, 1);
+----------------------------------+
| JSON_ARRAY_APPEND(@j, &#39;$[1]&#39;, 1) |
+----------------------------------+
| ["a", ["b", "c", 1], "d"]         |
+----------------------------------+

Copy after login

Appends an element 2 to the array of the first value in the field. If it is not a single value in the form of an array, it will be changed to an array and appended:

mysql> SELECT JSON_ARRAY_APPEND(@j, &#39;$[0]&#39;, 2);
+----------------------------------+
| JSON_ARRAY_APPEND(@j, &#39;$[0]&#39;, 2) |
+----------------------------------+
| [["a", 2], ["b", "c"], "d"]      |
+----------------------------------+

Copy after login

Appends an element 3 to the first array of the second value in the field. If it is not a single value in the form of an array, it changes it to an array and appends:

mysql> SELECT JSON_ARRAY_APPEND(@j, &#39;$[1][0]&#39;, 3);
+-------------------------------------+
| JSON_ARRAY_APPEND(@j, &#39;$[1][0]&#39;, 3) |
+-------------------------------------+
| ["a", [["b", 3], "c"], "d"]         |
+-------------------------------------+

Copy after login

In the field Append an element "x" to the value array with key "b":

mysql> SET @j = &#39;{"a": 1, "b": [2, 3], "c": 4}&#39;;
mysql> SELECT JSON_ARRAY_APPEND(@j, &#39;$.b&#39;, &#39;x&#39;);
+------------------------------------+
| JSON_ARRAY_APPEND(@j, &#39;$.b&#39;, &#39;x&#39;)  |
+------------------------------------+
| {"a": 1, "b": [2, 3, "x"], "c": 4} |
+------------------------------------+

Copy after login

In the value array with key "c" in the field Append an element "y":

mysql> SELECT JSON_ARRAY_APPEND(@j, &#39;$.c&#39;, &#39;y&#39;);
+--------------------------------------+
| JSON_ARRAY_APPEND(@j, &#39;$.c&#39;, &#39;y&#39;)    |
+--------------------------------------+
| {"a": 1, "b": [2, 3], "c": [4, "y"]} |
+--------------------------------------+

Copy after login

If the JSON field is a standard JSON type, using this function will change it into an array form and append the value:

mysql> SET @j = &#39;{"a": 1}&#39;;
mysql> SELECT JSON_ARRAY_APPEND(@j, &#39;$&#39;, &#39;z&#39;);
+---------------------------------+
| JSON_ARRAY_APPEND(@j, &#39;$&#39;, &#39;z&#39;) |
+---------------------------------+
| [{"a": 1}, "z"]                    |
+---------------------------------+

Copy after login

If the JSON field is a standard JSON type, batch append:

mysql> SELECT JSON_ARRAY_APPEND(@j, &#39;$&#39;, &#39;z&#39;，&#39;$&#39;, &#39;w&#39;);
+---------------------------------+
| JSON_ARRAY_APPEND(@j, &#39;$&#39;, &#39;z&#39;) |
+---------------------------------+
| [{"a": 1}, "z", "w"]              |
+---------------------------------+

Copy after login

2. Insertion of JSON_ARRAY_INSERT array class

This usage is mainly to process the value array type data in the JSON field. Mainly for insertion, it is not a standard array type. This function will automatically convert to array form, use the previous value as the first element of the array and then append:

mysql> SET @j = &#39;["a", {"b": [1, 2]}, [3, 4]]&#39;;

Copy after login

Insert characters after the first value of the array 'x' element:

mysql> SELECT JSON_ARRAY_INSERT(@j, &#39;$[1]&#39;, &#39;x&#39;);
+------------------------------------+
| JSON_ARRAY_INSERT(@j, &#39;$[1]&#39;, &#39;x&#39;) |
+------------------------------------+
| ["a", "x", {"b": [1, 2]}, [3, 4]]  |

Copy after login

Insert the character 'x' before the first value in the array. Element:

mysql> SELECT JSON_ARRAY_INSERT(@j, &#39;$[0]&#39;, &#39;x&#39;);
+------------------------------------+
| JSON_ARRAY_INSERT(@j, &#39;$[1]&#39;, &#39;x&#39;) |
+------------------------------------+
| ["x","a", {"b": [1, 2]}, [3, 4]]  |
+------------------------------------+

Copy after login

In the array Insert the character 'x' element after the 100th value. If it is too long, insert it after the last value:

mysql> SELECT JSON_ARRAY_INSERT(@j, &#39;$[100]&#39;, &#39;x&#39;);
+--------------------------------------+
| JSON_ARRAY_INSERT(@j, &#39;$[100]&#39;, &#39;x&#39;) |
+--------------------------------------+
| ["a", {"b": [1, 2]}, [3, 4], "x"]    |
+--------------------------------------+

Copy after login

In the first value of the array, the value array with key 'b' Enter the character element 'x' before 1 value:

mysql> SELECT JSON_ARRAY_INSERT(@j, &#39;$[1].b[0]&#39;, &#39;x&#39;);
+-----------------------------------------+
| JSON_ARRAY_INSERT(@j, &#39;$[1].b[0]&#39;, &#39;x&#39;) |
+-----------------------------------------+
| ["a", {"b": ["x", 1, 2]}, [3, 4]]       |
+-----------------------------------------+

Copy after login

Enter the character element 'y' after the second value in the array and after the first value in the array:

mysql> SELECT JSON_ARRAY_INSERT(@j, &#39;$[2][1]&#39;, &#39;y&#39;);
+---------------------------------------+
| JSON_ARRAY_INSERT(@j, &#39;$[2][1]&#39;, &#39;y&#39;) |
+---------------------------------------+
| ["a", {"b": [1, 2]}, [3, "y", 4]]     |
+---------------------------------------+

Copy after login

Batch insertion of array elements:

mysql> SELECT JSON_ARRAY_INSERT(@j, &#39;$[0]&#39;, &#39;x&#39;, &#39;$[2][1]&#39;, &#39;y&#39;);
+----------------------------------------------------+
| JSON_ARRAY_INSERT(@j, &#39;$[0]&#39;, &#39;x&#39;, &#39;$[2][1]&#39;, &#39;y&#39;) |
+----------------------------------------------------+
| ["x", "a", {"b": [1, 2]}, [3, 4]]                      |
+----------------------------------------------------+

Copy after login

3. JSON_INSERT standard JSON value insertion

If the key already exists, the old value will not be overwritten. Insertion of non-existent values:

mysql> SET @j = &#39;{ "a": 1, "b": [2, 3]}&#39;;
mysql> SELECT JSON_INSERT(@j, &#39;$.a&#39;, 10, &#39;$.c&#39;, &#39;[true, false]&#39;);
+----------------------------------------------------+
| JSON_INSERT(@j, &#39;$.a&#39;, 10, &#39;$.c&#39;, &#39;[true, false]&#39;) |
+----------------------------------------------------+
| {"a": 1, "b": [2, 3], "c": "[true, false]"}          |
+----------------------------------------------------+

Copy after login

4. JSON_MERGE merge operation

JSON_MERGE is mainly used for merging: if two are arrays, merge into one array; if two are JSON, merge into one JSON , if two JSONs have the same key, the values are merged into an array; if they are two single values, they are merged into an array; if they are an array and one is JSON, they are merged into an array:

mysql> SELECT JSON_MERGE(&#39;[1, 2]&#39;, &#39;[true, false]&#39;);
+---------------------------------------+
| JSON_MERGE(&#39;[1, 2]&#39;, &#39;[true, false]&#39;) |
+---------------------------------------+
| [1, 2, true, false]                       |
+---------------------------------------+
mysql> SELECT JSON_MERGE(&#39;{"name": "x"}&#39;, &#39;{"id": 47}&#39;);
+-------------------------------------------+
| JSON_MERGE(&#39;{"name": "x"}&#39;, &#39;{"id": 47}&#39;) |
+-------------------------------------------+
| {"id": 47, "name": "x"}                       |
+-------------------------------------------+
mysql> SELECT JSON_MERGE(&#39;{"id": "x"}&#39;, &#39;{"id": 47}&#39;);
+-------------------------------------------+
| JSON_MERGE(&#39;{"id": "x"}&#39;, &#39;{"id": 47}&#39;) |
+-------------------------------------------+
| {"id": ["x",47]}                             |
+-------------------------------------------+
mysql> SELECT JSON_MERGE(&#39;1&#39;, &#39;true&#39;);
+-------------------------+
| JSON_MERGE(&#39;1&#39;, &#39;true&#39;) |
+-------------------------+
| [1, true]                  |
+-------------------------+
mysql> SELECT JSON_MERGE(&#39;[1, 2]&#39;, &#39;{"id": 47}&#39;);
+------------------------------------+
| JSON_MERGE(&#39;[1, 2]&#39;, &#39;{"id": 47}&#39;) |
+------------------------------------+
| [1, 2, {"id": 47}]                    |
+------------------------------------+

Copy after login

5. JSON_REMOVE deletion operation

JSON_REMOVE deletion operation. This is relatively simple. It can delete a certain value in the field, either in array or JSON format:

mysql> SET @j = &#39;["a", ["b", "c"], "d"]&#39;;
mysql> SELECT JSON_REMOVE(@j, '$[1]');
+-------------------------+
| JSON_REMOVE(@j, '$[1]') |
+-------------------------+
| ["a", "d"]                 |
+-------------------------+
 
 
 
 
mysql> SET @j = '{"name": "x","id": 47}';
mysql> SELECT JSON_REMOVE(@j, '$.id');
+-------------------------+
| JSON_REMOVE(@j, '$.id') |
+-------------------------+
| {"name": "x"}              |
+-------------------------+
mysql> SELECT JSON_REMOVE(@j, '$.id', '$.name');
+-----------------------------------+
| JSON_REMOVE(@j, '$.id', '$.name') |
+-----------------------------------+
| {}                                       |
+-----------------------------------+
mysql> SET @j = '{"name": "x","id": [47,48]}';
mysql> SELECT JSON_REMOVE(@j, '$.id[1]');
+-------------------------+
| JSON_REMOVE(@j, '$.id') |
+-------------------------+
| {"id": [47],"name": "x"} |
+-------------------------+

Copy after login

6. JSON_REPLACE operation

JSON_REPLACE replacement operation, if there is any, replace it, if not, ignore it.

mysql> SET @j = &#39;{ "a": 1, "b": [2, 3]}&#39;;
mysql> SELECT JSON_REPLACE(@j, &#39;$.a&#39;, 10, &#39;$.c&#39;, &#39;[true, false]&#39;);
+-----------------------------------------------------+
| JSON_REPLACE(@j, &#39;$.a&#39;, 10, &#39;$.c&#39;, &#39;[true, false]&#39;) |
+-----------------------------------------------------+
| {"a": 10, "b": [2, 3]}                                      
+-----------------------------------------------------+
mysql> SELECT JSON_REPLACE(@j, &#39;$.a&#39;, 10, &#39;$.b[1]&#39;,99);
+-----------------------------------------------------+
| JSON_REPLACE(@j, &#39;$.a&#39;, 10, &#39;$.b[1]&#39;,99&#39;)           |
+-----------------------------------------------------+
| {"a": 10, "b": [2, 99]}                             |
+-----------------------------------------------------+

Copy after login

7. JSON_SET operation

JSON_SET operation is an update and insert operation. If it already exists, update it; if it does not exist, insert it. The following are the differences in the execution results of JSON_SET, JSON_INSERT, and JSON_REPLACE:

MySQL 5.7 new features | Json Column and Generated Column (medium)

The above is the content of MySQL 5.7 new features | Json Column and Generated Column (middle). For more related content, please pay attention to the PHP Chinese website (m.sbmmt.com)!