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)

来源地址：https://blog.csdn.net/xiaolixi199311/article/details/131977575