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)