MySQL的JSON操作

官网地址

1. MySQL json介绍

As of MySQL 5.7.8, MySQL supports a native JSON data type defined by RFC 7159 that enables efficient access to data in JSON (JavaScript Object Notation) documents.
Automatic validation of JSON documents stored in JSON columns. Invalid documents produce an error.
Optimized storage format. JSON documents stored in JSON columns are converted to an internal format that permits quick read access to document elements.
It is important to keep in mind that the size of any JSON document stored in a JSON column is limited to the value of the max_allowed_packet system variable.
A JSON column cannot have a non-NULL default value.
In MySQL, JSON values are written as strings. MySQL parses any string used in a context that requires a JSON value, and produces an error if it is not valid as JSON. These contexts include inserting a value into a column that has the JSON data type and passing an argument to a function that expects a JSON value (usually shown as json_doc or json_val in the documentation for MySQL JSON functions)
MySQL handles strings used in JSON context using the utf8mb4 character set and utf8mb4_bin collation.
Case sensitivity also applies to the JSON null, true, and false literals, which always must be written in lowercase.
In MySQL 5.7.9 and later, you can use column->path with a JSON column identifier and JSON path expression as a synonym for JSON_EXTRACT(column, path).

在这里插入图片描述

2. JSON Function Reference

MySQL 5.7.22 and later supports two aggregate JSON functions JSON_ARRAYAGG() and JSON_OBJECTAGG().
Also beginning with MySQL 5.7.22:

“pretty-printing” of JSON values in an easy-to-read format can be obtained using the JSON_PRETTY() function.
You can see how much storage space a given JSON value takes up using JSON_STORAGE_SIZE().

3. Functions That Create JSON Values

mysql> SELECT JSON_ARRAY(1, "abc", NULL, TRUE, CURTIME());
+---------------------------------------------+
| JSON_ARRAY(1, "abc", NULL, TRUE, CURTIME()) |
+---------------------------------------------+
| [1, "abc", null, true, "11:30:24.000000"]   |
+---------------------------------------------+mysql> SELECT JSON_OBJECT('id', 87, 'name', 'carrot');
+-----------------------------------------+
| JSON_OBJECT('id', 87, 'name', 'carrot') |
+-----------------------------------------+
| {"id": 87, "name": "carrot"}            |
+-----------------------------------------+mysql> SELECT JSON_QUOTE('null'), JSON_QUOTE('"null"');
+--------------------+----------------------+
| JSON_QUOTE('null') | JSON_QUOTE('"null"') |
+--------------------+----------------------+
| "null"             | "\"null\""           |
+--------------------+----------------------+
mysql> SELECT JSON_QUOTE('[1, 2, 3]');
+-------------------------+
| JSON_QUOTE('[1, 2, 3]') |
+-------------------------+
| "[1, 2, 3]"             |
+-------------------------+

Converting between JSON and non-JSON values

在这里插入图片描述

on duplicate key

mysql> CREATE TABLE `t_json` (->   `id` int NOT NULL AUTO_INCREMENT,->   `json_val` json DEFAULT NULL,->   PRIMARY KEY (`id`)-> ) ENGINE=InnoDB AUTO_INCREMENT=9;
mysql> select * from t_json;
Empty set (0.00 sec)mysql> insert into t_json values(1, '[123]') on duplicate key update json_val=JSON_ARRAY_APPEND(json_val, '$', 1);
Query OK, 1 row affected (0.00 sec)mysql> 
mysql> 
mysql> 
mysql> select * from t_json;
+----+----------+
| id | json_val |
+----+----------+
|  1 | [123]    |
+----+----------+
1 row in set (0.00 sec)mysql> 
mysql> insert into t_json values(1, '[123]') on duplicate key update json_val=JSON_ARRAY_APPEND(json_val, '$', 1);
Query OK, 2 rows affected (0.00 sec)mysql> select * from t_json;
+----+----------+
| id | json_val |
+----+----------+
|  1 | [123, 1] |
+----+----------+
1 row in set (0.00 sec)mysql> insert into t_json values(1, '[123]') on duplicate key update json_val=JSON_ARRAY_APPEND(json_val, '$', 1);
Query OK, 2 rows affected (0.00 sec)mysql> select * from t_json;
+----+-------------+
| id | json_val    |
+----+-------------+
|  1 | [123, 1, 1] |
+----+-------------+
1 row in set (0.01 sec)